left join , 左边表为基础, 扫描右边表匹配的记录.将返回左表的所有行。如果左表的某行在右表中没有匹配行,则将为左表返回空值
right join , 右边表为基础, 扫描左边表匹配的记录。将返回右表的所有行。如果右表的某行在左表中没有匹配行,则将为左表返回空值
有两表a和b,前两字段完全相同:(id int,name varchar(10)...)
declare @a table(id int,name varchar(10))
insert @a Select '1','a'
union select '2','b'
union select '33','d'
union select '44','e'
declare @b table(id int,name varchar(10))
insert @b select '1','a'
union select '2','b'
union select '55','d'
union select '66','e'
select * from @a as a inner join @b as b on a.id = b.id --内連接
1 a 1 a
2 b 2 b
select * from @a as a full join @b as b on a.id = b.id --全連接
1 a 1 a
2 b 2 b
NULL NULL 55 d
NULL NULL 66 e
44 e NULL NULL
33 d NULL NULL
select * from @a as a left join @b as b on a.id = b.id --右連接
1 a 1 a
2 b 2 b
44 e NULL NULL
33 d NULL NULL
select * from @a as a right join @b as b on a.id = b.id --右連接
1 a 1 a
2 b 2 b
NULL NULL 55 d
NULL NULL 66 e