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

[sql] view plaincopy
---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

两种查询结果对比