今天研究了两个表中外连接条件位置不同,导致的查询结果不同,
---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
两种查询结果对比