on，where上的条件区别

 DECLARE @T TABLE ( ID int ) DECLARE @B TABLE ( ID INT ,USEFLG INT ) INSERT INTO @T SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 INSERT INTO @B SELECT 0,0 UNION SELECT 1,1 UNION SELECT 2,1 UNION SELECT 3,1 UNION SELECT 4,1 UNION SELECT 5,2 UNION SELECT 6,NULL -- -------LEFT---------------------------------------------------------------- SELECT * FROM @T AS A LEFT JOIN @B AS B ON B.USEFLG=1---A表数据全显示，B表中USEFLG=1的所有数据和A表中的数据做了全连接 SELECT * FROM @T AS A LEFT JOIN @B AS B ON A.ID=4---A表数据都显示，只有 ID=4的数据才和B表的数据做了全连结，其他全部为NULL SELECT * FROM @T AS A LEFT JOIN @B AS B ON B.USEFLG=1 AND A.ID=4---A表数据都显示，只有 ID=4的数据才和B表USEFLG=1的数据做了全连结，其他全部为NULL SELECT * FROM @T AS A LEFT JOIN @B AS B ON B.USEFLG=1 WHERE A.ID=4---A表只和USEFLG=1的数据做了全连结，其他全部为NULL，然后从结果中选取ID=4的数据 SELECT * FROM @T AS A LEFT JOIN @B AS B ON B.USEFLG=2---A表数据全显示，B表中USEFLG=2的所有数据和A表中的数据做了全连接 SELECT * FROM @T AS A LEFT JOIN @B AS B ON A.ID=3---A表数据都显示，只有 ID=3的数据才和B表的数据做了全连结，其他全部为NULL SELECT * FROM @T AS A LEFT JOIN @B AS B ON B.USEFLG=2 AND A.ID=3---A表数据都显示，只有 ID=3的数据才和B表USEFLG=2的数据做了全连结，其他全部为NULL SELECT * FROM @T AS A LEFT JOIN @B AS B ON B.USEFLG=2 WHERE A.ID=3--A表只和USEFLG=2的数据做了全连结，其他全部为NULL，然后从结果中选取ID=3的数据 SELECT * FROM @T AS A LEFT JOIN @B AS B ON A.ID=B.ID---A表数据全显示，按照ID连接B表的数据，有则连，无则NULL SELECT * FROM @T AS A LEFT JOIN @B AS B ON A.ID=B.ID AND A.ID=2---A表数据全显示，按照ID连接B表的数据，只连接ID=2的数据，其他的全部为NULL SELECT * FROM @T AS A LEFT JOIN @B AS B ON A.ID=B.ID AND USEFLG=1---A表数据全显示，按照ID连接B表的数据，只连接USEFLG=1的数据，其他的全部为NULL SELECT * FROM @T AS A LEFT JOIN @B AS B ON A.ID=B.ID AND USEFLG=1 AND A.ID=4---A表数据全显示，按照ID连接B表的数据，只连接ID=4和USEFLG=1的数据，其他的全部为NULL SELECT * FROM @T AS A LEFT JOIN @B AS B ON A.ID=B.ID AND USEFLG=1 WHERE A.ID=4---A表按照ID连接B表的数据，只连接USEFLG=1的数据，其他的全部为NULL,，然后从结果中选取ID=4的数据 SELECT * FROM @T AS A LEFT JOIN @B AS B ON A.ID=B.ID AND A.ID=3 SELECT * FROM @T AS A LEFT JOIN @B AS B ON A.ID=B.ID AND USEFLG<>1 SELECT * FROM @T AS A LEFT JOIN @B AS B ON A.ID=B.ID AND USEFLG<>1 AND A.ID=3 SELECT * FROM @T AS A LEFT JOIN @B AS B ON A.ID=B.ID AND A.ID=3 SELECT * FROM @T AS A LEFT JOIN @B AS B ON A.ID=B.ID AND USEFLG=2 SELECT * FROM @T AS A LEFT JOIN @B AS B ON A.ID=B.ID AND USEFLG=2 AND A.ID=5 SELECT * FROM @T AS A LEFT JOIN @B AS B ON A.ID=B.ID AND USEFLG=2 AND A.ID=3 SELECT * FROM @T AS A LEFT JOIN @B AS B ON A.ID=B.ID AND A.ID=3 SELECT * FROM @T AS A LEFT JOIN @B AS B ON A.ID=B.ID AND USEFLG IS NULL SELECT * FROM @T AS A LEFT JOIN @B AS B ON A.ID=B.ID AND USEFLG IS NULL AND A.ID=2 -----INNER---------------------------------------------------------------- SELECT * FROM @T AS A INNER JOIN @B AS B ON B.USEFLG=1 SELECT * FROM @T AS A INNER JOIN @B AS B ON A.ID=4 SELECT * FROM @T AS A INNER JOIN @B AS B ON B.USEFLG=1 AND A.ID=4 SELECT * FROM @T AS A INNER JOIN @B AS B ON B.USEFLG=1 WHERE A.ID=4 SELECT * FROM @T AS A INNER JOIN @B AS B ON B.USEFLG=2 SELECT * FROM @T AS A INNER JOIN @B AS B ON A.ID=3 SELECT * FROM @T AS A INNER JOIN @B AS B ON B.USEFLG=2 AND A.ID=3 SELECT * FROM @T AS A INNER JOIN @B AS B ON B.USEFLG=2 WHERE A.ID=3 SELECT * FROM @T AS A INNER JOIN @B AS B ON A.ID=B.ID SELECT * FROM @T AS A INNER JOIN @B AS B ON A.ID=B.ID AND A.ID=2 SELECT * FROM @T AS A INNER JOIN @B AS B ON A.ID=B.ID AND USEFLG=1 SELECT * FROM @T AS A INNER JOIN @B AS B ON A.ID=B.ID AND USEFLG=1 AND A.ID=4 SELECT * FROM @T AS A INNER JOIN @B AS B ON A.ID=B.ID AND A.ID=3 SELECT * FROM @T AS A INNER JOIN @B AS B ON A.ID=B.ID AND USEFLG<>1 SELECT * FROM @T AS A INNER JOIN @B AS B ON A.ID=B.ID AND USEFLG<>1 AND A.ID=3 SELECT * FROM @T AS A INNER JOIN @B AS B ON A.ID=B.ID AND A.ID=3 SELECT * FROM @T AS A INNER JOIN @B AS B ON A.ID=B.ID AND USEFLG=2 SELECT * FROM @T AS A INNER JOIN @B AS B ON A.ID=B.ID AND USEFLG=2 AND A.ID=5 SELECT * FROM @T AS A INNER JOIN @B AS B ON A.ID=B.ID AND USEFLG=2 AND A.ID=3 SELECT * FROM @T AS A INNER JOIN @B AS B ON A.ID=B.ID AND A.ID=3 SELECT * FROM @T AS A INNER JOIN @B AS B ON A.ID=B.ID AND USEFLG IS NULL SELECT * FROM @T AS A INNER JOIN @B AS B ON A.ID=B.ID AND USEFLG IS NULL AND A.ID=2

对于left  join而言，on 上的常量条件，

只是做为允许哪些数据连结的条件，

常量条件之外的其他数据仍然显示。

即使在其他表中有你匹配的数据，连接也只能竹篮打水一场空。

where上的常量条件，才是最终数据结果的筛选条件。

 

对于inner join 而言，

on上的常量条件和where上的常量条件效果是一样的。