T-SQL之JOIN彻底理解
3072 点击·0 回帖
灯火互联
楼主
 |  | |
 | T-SQL之JOIN彻底理解 本文不解释，欢迎提问解答，谢谢。直接上代码： --测试数据建立 Create TABLE JOIN_TEST( COLA INT, COLB INT ) GO INSERT INTO JOIN_TEST SELECT 1,1 www.atcpu.com 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 行受影响) */ www.atcpu.com 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 /* www.atcpu.com 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 行受影响) */ | |
 |  |