下图展示了 LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相关的 7 种用法

例子

create table #a (af1 int) 
insert into #a select 1
insert into #a select 2
create table #b (bf1 int) 
insert into #b select 2
insert into #b select 3
select 'lwft join'  x,a.*,b.* from #a a left join #b b on a.af1=b.bf1
select 'right join' x,a.*,b.* from #a a right join #b b on a.af1=b.bf1
select 'inner join'  x,a.*,b.* from #a a inner join #b b on a.af1=b.bf1
select 'lwft join b is null'  x,a.*,b.* from #a a left join #b b on a.af1=b.bf1 where b.bf1 is null
select 'right join a is null'  x,a.*,b.* from #a a right join #b b on a.af1=b.bf1 where a.af1 is null
select 'full outer join' x,a.*,b.* from #a a full outer join #b b on a.af1=b.bf1 
select 'full outer join a or b null' x,a.*,b.* from #a a full outer join #b b on a.af1=b.bf1 where a.af1 is null or b.bf1 is null
drop table #a
drop table #b