T-SQL之JOIN彻底理解
本文不解释，欢迎提问解答，谢谢。直接上代码：
--测试数据建立 Create TABLE JOIN_TEST( COLA INT, COLB INT ) GO INSERT INTO JOIN_TEST SELECT 1,1 INSERT INTO JOIN_TEST SELECT 1,1 GO --测试数据建立完毕 SELECT * FROM JOIN_TEST /* COLA COLB ----------- ----------- 1 1 1 1 (2 行受影响) */ SELECT * FROM JOIN_TEST A CROSS JOIN JOIN_TEST B /* COLA COLB COLA COLB ----------- ----------- ----------- ----------- 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 (4 行受影响) */ SELECT * FROM JOIN_TEST A INNER JOIN JOIN_TEST B ON A.COLA=B.COLA /* COLA COLB COLA COLB ----------- ----------- ----------- ----------- 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 (4 行受影响) */ SELECT * FROM JOIN_TEST A LEFT JOIN JOIN_TEST B ON A.COLA=B.COLA /* COLA COLB COLA COLB ----------- ----------- ----------- ----------- 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 (4 行受影响) */ SELECT * FROM JOIN_TEST A LEFT JOIN JOIN_TEST B ON A.COLA=B.COLA AND A.COLA<>1 /* COLA COLB COLA COLB ----------- ----------- ----------- ----------- 1 1 NULL NULL 1 1 NULL NULL (2 行受影响) */ SELECT * FROM JOIN_TEST A LEFT JOIN JOIN_TEST B ON A.COLA=B.COLA WHERE A.COLA<>1 /* COLA COLB COLA COLB ----------- ----------- ----------- ----------- (0 行受影响) */ SELECT * FROM JOIN_TEST A FULL JOIN JOIN_TEST B ON A.COLA=B.COLA AND A.COLA<>1 /* COLA COLB COLA COLB ----------- ----------- ----------- ----------- 1 1 NULL NULL 1 1 NULL NULL NULL NULL 1 1 NULL NULL 1 1 */ SELECT * FROM JOIN_TEST A FULL JOIN JOIN_TEST B ON A.COLA=B.COLA where A.COLA<>1 /* COLA COLB COLA COLB ----------- ----------- ----------- ----------- (0 行受影响) */