sql中外连接条件位置不同导致的查询结果不过

  今天研究了两个表中外连接条件位置不同，导致的查询结果不同，

 

---DROP TABLE #t1,#t2

---创建表1
CREATE TABLE #t1(areaid NVARCHAR(20),housecode NVARCHAR(20),expressid INT)

INSERT INTO	#t1(areaid, housecode, expressid )VALUES  ('101','01', 1)
INSERT INTO	#t1(areaid, housecode, expressid )VALUES  ('101','02', 1)
INSERT INTO	#t1(areaid, housecode, expressid )VALUES  ('101','03', 1)
INSERT INTO	#t1(areaid, housecode, expressid )VALUES  ('101','04', 1)

--创建表2
CREATE TABLE #t2(areaid NVARCHAR(20),housecode NVARCHAR(20),expressid INT)

INSERT INTO	#t2(areaid, housecode, expressid )VALUES  ('101','01', 0)
INSERT INTO	#t2(areaid, housecode, expressid )VALUES  ('101','02', 1)
INSERT INTO	#t2(areaid, housecode, expressid )VALUES  ('101','03', 0)
INSERT INTO	#t2(areaid, housecode, expressid )VALUES  ('101','04', 1)

---#t2.expressid=0跟在where后面
SELECT #t1.*,#t2.* FROM #t1 LEFT JOIN #t2 
ON #t1.areaid = #t2.areaid AND #t1.housecode = #t2.housecode  
WHERE  #t2.expressid=0

---#t2.expressid=0跟在on后面
SELECT #t1.*,#t2.* FROM #t1 LEFT JOIN #t2 
ON #t1.areaid = #t2.areaid AND #t1.housecode = #t2.housecode  AND #t2.expressid=0

 

 

两种查询结果对比