TSQL之JOIN（连接）彻底理解
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 行受影响)
*/
分类: SQL SERVER T-TQL
作者： Leo_wl
出处： http://HdhCmsTestcnblogs测试数据/Leo_wl/
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。
版权信息查看更多关于TSQL之JOIN（连接）彻底理解的详细内容...