T-SQL之JOIN彻底理解
本文不解释，欢迎提问解答，谢谢。直接上代码：
--测试数据建立Create TABLE JOIN_TEST(COLA INT,COLB INT)GOINSERT INTO JOIN_TESTSELECT 1,1INSERT INTO JOIN_TESTSELECT 1,1GO--测试数据建立完毕SELECT * FROM JOIN_TEST/*COLA COLB----------- -----------1 11 1(2 行受影响)*/SELECT * FROM JOIN_TEST A CROSS JOIN JOIN_TEST B/*COLA COLB COLA COLB----------- ----------- ----------- -----------1 1 1 11 1 1 11 1 1 11 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 11 1 1 11 1 1 11 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 11 1 1 11 1 1 11 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 NULL1 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 NULL1 1 NULL NULLNULL NULL 1 1NULL 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 行受影响)*/ |
分类: SQL SERVER T-TQL