连接分为:内连接、外连接、交叉连接
一、内连接——最常用
定义:仅将两个表中满足连接条件的行组合起来作为结果集。
在内连接中,只有在两个表中匹配的行才能在结果集中出现
关键词:INNER JOIN (可以省略)
格式:SELECT 列名表 FROM 表名1 [INNER] JOIN 表名2 ON或WHERE 条件表达式
说明:
(1)列名表中的列名可以出自后面的两个表,但如果两个表中有同名列,应在列名前标明出处,格式为:表名.列名
(2)若连接的两个表名字太长,可以为它们起个别名。 格式为:表名 AS 别名
(3)INNER是默认方式,可以省略
eg:
select *
from t_institution i
inner join t_teller t
on i.inst_no = t.inst_no
where i.inst_no = "5801"
其中inner可以省略。
等价于早期的连接语法
select *
from t_institution i, t_teller t
where i.inst_no = t.inst_no
and i.inst_no = "5801"
二、外连接
在左外连接和右外连接时都会以一张表为基表,另一张表为外表,基表的内容会全部显示,然后加上两张表匹配的内容。 如果基表的数据在外表没有记录。 那么在相关联的结果集行中列显示为空值(NULL)。
对于外连接, 也可以使用“(+) ”来表示。 关于使用(+)的一些注意事项:
1.(+)操作符只能出现在where子句中,并且不能与outer join语法同时使用。
2. 当使用(+)操作符执行外连接时,如果在where子句中包含有多个条件,则必须在所有条件中都包含(+)操作符(即外表的条件都要加),否则连接失败
3.(+)操作符只适用于列,而不能用在表达式上。
4.(+)操作符不能与or和in操作符一起使用。
5.(+)操作符只能用于实现左外连接和右外连接,而不能用于实现完全外连接。
1、左(外)连接
定义:在内连接的基础上,还包含左表中所有不符合条件的数据行,并在其中的右表列填写NULL
关键字:LEFT JOIN
eg:
select *
from t_institution i
left outer join t_teller t
on i.inst_no = t.inst_no
其中outer可以省略。
注意:
当在内连接查询中加入条件是,无论是将它加入到join子句,还是加入到where子句,其效果是完全一样的,但对于外连接情况就不同了。当把条件加入到 join子句时,SQL Server、Informix会返回外连接表的全部行,然后使用指定的条件返回第二个表的行。如果将条件放到where子句 中,SQL Server将会首先进行连接操作,然后使用where子句对连接后的行进行筛选。下面的两个查询展示了条件放置位子对执行结果的影响:
条件在join子句
select *
from t_institution i
left outer join t_teller t
on i.inst_no = t.inst_no
and i.inst_no = “5801”
结果是:
inst_no inst_name inst_no teller_no teller_name
5801 天河区 5801 0001 tom
5801 天河区 5801 0002 david
5802 越秀区
5803 白云区
条件在where子句
select *
from t_institution i
left outer join t_teller t
on i.inst_no = t.inst_no
where i.inst_no = “5801”
结果是:
inst_no inst_name inst_no teller_no teller_name
5801 天河区 5801 0001 tom
5801 天河区 5801 0002 david
2、右(外)连接
定义:在内连接的基础上,还包含右表中所有不符合条件的数据行,并在其中的左表列填写NULL
关键字:RIGHT JOIN
3、完全连接
定义:在内连接的基础上,还包含两个表中所有不符合条件的数据行,并在其中的左表、和右表列填写NULL
关键字:FULL JOIN
三、交叉连接
定义:将两个表的所有行进行组合,连接后的行数为两个表的乘积数。(笛卡尔积)
关键词:CROSS JOIN
格式:FROM 表名1 CROSS JOIN 表名2
四, 自身连接
自身连接是指同一个表自己与自己进行连接。这种一元连接通常用于从自反关系(也称作递归关系)中抽取数据。例如人力资源数据库中雇员与老板的关系。
下面例子是在机构表中查找本机构和上级机构的信息。
select s.inst_no superior_inst, s.inst_name sup_inst_name, i.inst_no, i.inst_name
from t_institution i
join t_institution s
on i.superior_inst = s.inst_no
结果是:
superior_inst sup_inst_name inst_no inst_name
800 广州市 5801 天河区
800 广州市 5802 越秀区
800 广州市 5803 白云区
关于使用(+)的一些注意事项2举例(网上的例子):
注意事项2:当使用(+)操作符执行外连接时,如果在where子句中包含有多个条件,则必须在所有条件中都包含(+)操作符(即外表的条件都要加),否则连接失败
测试用的例子:xtest2是基表,xtest1是外表表结构及数据如下
xtest1(外表) company_code item_code item_name cost_class
00 item1 name1 f
00 item2 name2 m
01 item1 name1 m
01 item2 name2 f考试大-全国最大教育类网站(www.Examda。com)
xtest2(基表) company_code item_code t1 t2
00 item1 aa bb
00 item2 cc dd
00 item3 ee ff
null null null null
要求:将xtest2中的数据全部显出,根据xtest2中的公司号及物料代码去xtest1表里找对应的物料名,但是有一个附加条件,就是xtest1表里必须是cost_class='f'的才能写到最后的结果集中。最后实验成功的sql如下:
select a.item_code, b.item_name
from xtest2 a, xtest1 b
where a.item_code = b.item_code(+)
and b.cost_class(+) = 'f'
and a.company_code = b.company_code(+)
以b表是外表,b的条件都必须加+,否则连接失败(查出结果有问题),另外也可以用SQL的表中来写:
select a.item_code, b.item_name
from xtest2 a
left join xtest1 b on a.item_code = b.item_code
and b.cost_class = 'f'
and a.company_code = b.company_code
关于使用(+)的一些注意事项3举例(网上的例子):
注意事项3:(+)操作符只适用于列,而不能用在表达式上。
select distinct a.city_id, a.city_name, count(b.msisdn)
from region_info a, receive_user b
where a.city_id = substr(b.area_id, 1, 5) (+)
and a.province_id = '10'
and b.end_date < to_char(sysdate, 'yyyymmdd')
group by a.city_id, a.city_name
因为oracle左右连接不允许表达式,先生成列,再嵌套
SELECT DISTINCT a.city_id, a.city_name, COUNT(b.msisdn)
FROM region_info a,
(SELECT msisdn, end_date, substr(area_id, 1, 5) area_id receive_user) b
WHERE a.city_id = area_id(+)
AND a.province_id = '10'
AND b.end_date < to_char(SYSDATE, 'yyyymmdd')
GROUP BY a.city_id, a.city_name