1、创建一个临时表，用于演示sqlserver语法中的LEFT JOIN、LEFT OUTER JOIN使用IF OBJECT_ID('tempdb..#tmp1媪青怍牙') IS NOT NULL DROP TABLE #tmp1;CREATE TABLE #tmp1( Col1 varchar(50), Col2 varchar(200) );
2、创建另外一个临时表，用于演示sqlserver语法中的LEFT JOIN、LEFT OUTER JOIN使用IF OBJECT_ID('tempdb..#tmp2') IS NOT NULL DROP TABLE #tmp2;CREATE TABLE #tmp2( Col1 varchar(50), Col3 varchar(200) );
3、往临时表中插入几行测试数据，其中包辖艮鲔鸣括插入Null值insert into #tmp1(Col1, Col2) values('Code1'荑樊综鲶, '第一列');insert into #tmp1(Col1, Col2) values('Code10', '第二列');insert into #tmp1(Col1, Col2) values('Code100', '第三列');insert into #tmp2(Col1, Col3) values('Code1', NULL);insert into #tmp2(Col1, Col3) values('Code10', '职员');insert into #tmp2(Col1, Col3) values('Code99', '经理');
4、查询临时表中的测试数据select * from #tmp1;select * from #tmp2;
5、使用LEFT JOIN关联两张表，以第一个临时表为准，查询Col3不为空的结果select *from #tmp1 aleft join #tmp2 b on b.Col1 = a.Col1 and b.Col3 IS NOT NULL
6、使用LEFT OUTER JOIN关联两张表，以第一个临时表为准，查询Col3不为空的结果select *from #tmp1 aleft outer join #tmp2 b on b.Col1 = a.Col1 and b.Col3 IS NOT NULL
7、从运行结果可以看出，对于left join和left outer join两种写法在sqlserver中其实是一样的效果。平时写sql语句的时候不必纠结两种写法，就使用left join即可
