inner join, left join, right join,all join区别

注:本文来自优快云 爱新觉罗.毓华 的回帖

 

/*
标题：联接的各种写法
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2010-04-21
地点：重庆大学科苑宾馆

create table t1(id int, val int)
insert into t1 values(1,1)
insert into t1 values(2,2)
create table t2(id int, val int)
insert into t2 values(1,1)
insert into t2 values(3,3)
go

--1、内联接：内联接是用比较运算符比较要联接列的值的联接。
--inner join逻辑运算符返回满足第一个(顶端)输入与第二个(底端)输入联接的每一行。
--inner join
--相等联接
select t1.* , t2.* from t1 inner join t2 on t1.id = t2.id
--等同于
select t1.* , t2.* from t1 , t2 where  t1.id = t2.id
/*
id          val         id          val        
----------- ----------- ----------- -----------
1           1           1           1
（所影响的行数为 1 行）
*/
--不等联接
select t1.* , t2.* from t1 inner join t2 on t1.id <> t2.id
--等同于
select t1.* , t2.* from t1 , t2 where t1.id <> t2.id
/*
id          val         id          val        
----------- ----------- ----------- -----------
2           2           1           1
1           1           3           3
2           2           3           3
（所影响的行数为 3 行）
*/

--2、左(外)联接：检索通过左向外联接引用的左表的所有行。
--Left Outer Join 逻辑运算符返回每个满足第一个(顶端)输入与第二个(底端)输入的联接的行。
--它还返回任何在第二个输入中没有匹配行的第一个输入中的行。第二个输入中的非匹配行作为空值返回。
--如果 Argument 列内不存在任何联接谓词，则每行都是一个匹配行。
--left join (Left Outer Join)
select t1.* , t2.* from t1 left join t2 on t1.id = t2.id
/*
id          val         id          val        
----------- ----------- ----------- -----------
1           1           1           1
2           2           NULL        NULL

（所影响的行数为 2 行）
*/

--3、右(外)联接：检索通过右向外联接引用的右表的所有行。
--Right Outer Join 逻辑运算符返回满足第二个(底端)输入与第一个(顶端)输入中的每个匹配行联接的每一行。
--它还返回第二个输入中在第一输入中没有匹配行的任何行，即与 NULL 联接。
--如果 Argument 列内不存在任何联接谓词，则每行都是一个匹配行。
--right join(right Outer join)
select t1.* , t2.* from t1 right join t2 on t1.id = t2.id
/*
id          val         id          val        
----------- ----------- ----------- -----------
1           1           1           1
NULL        NULL        3           3

（所影响的行数为 2 行）
*/

--4、全联接(完整外部联接)：通过在联接结果中包括不匹配的行保留不匹配信息。
--full join(full Outer join)
select isnull(t1.id , t2.id) id , t1.val , t2.val from t1 full join t2 on t2.id = t1.id order by id
/*
id          val         val        
----------- ----------- -----------
1           1           1
2           2           NULL
3           NULL        3

（所影响的行数为 3 行）
*/

--5、交叉连接(迪卡尔积)：没有 WHERE 子句的交叉联接将产生联接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。
--Cross Join 逻辑运算符将第一个(顶端)输入中的每行与第二个(底端)输入中的每行联接在一起。
--cross join(cross Outer join)
select t1.* , t2.* from t1 cross join t2
/*
id          val         id          val        
----------- ----------- ----------- -----------
1           1           1           1
2           2           1           1
1           1           3           3
2           2           3           3

（所影响的行数为 4 行）
*/

drop table t1 , t2