create table #t(id int, a char)
insert #t values (1, 'a')
insert #t values (2, 'b')
create table #t1(id int, a char)
insert #t1 values (1, 'A')
insert #t1 values (3, 'C')select * from #t inner join #t1 on #t.id = #t1.id
id a id a
----------- ---- ----------- ----
1 a 1 A(1 row(s) affected)
select * from #t left join #t1 on #t.id = #t1.id
id a id a
----------- ---- ----------- ----
1 a 1 A
2 b NULL NULL(2 row(s) affected)
select * from #t right join #t1 on #t.id = #t1.id
id a id a
----------- ---- ----------- ----
1 a 1 A
NULL NULL 3 C(2 row(s) affected)
select * from #t full join #t1 on #t.id = #t1.id
id a id a
----------- ---- ----------- ----
1 a 1 A
NULL NULL 3 C
2 b NULL NULL(3 row(s) affected)
insert #t values (1, 'a')
insert #t values (2, 'b')
create table #t1(id int, a char)
insert #t1 values (1, 'A')
insert #t1 values (3, 'C')select * from #t inner join #t1 on #t.id = #t1.id
id a id a
----------- ---- ----------- ----
1 a 1 A(1 row(s) affected)
select * from #t left join #t1 on #t.id = #t1.id
id a id a
----------- ---- ----------- ----
1 a 1 A
2 b NULL NULL(2 row(s) affected)
select * from #t right join #t1 on #t.id = #t1.id
id a id a
----------- ---- ----------- ----
1 a 1 A
NULL NULL 3 C(2 row(s) affected)
select * from #t full join #t1 on #t.id = #t1.id
id a id a
----------- ---- ----------- ----
1 a 1 A
NULL NULL 3 C
2 b NULL NULL(3 row(s) affected)
right join 第二张表的连接列在第一张表中没有对应的,第一张表中的值返回null
full join 返回两张表中的 left join+right join中不相同的记录
inner join 只返回两张表连接列的匹配项