T-SQL之JOIN彻底理解
本文不解释，欢迎提问解答，谢谢。直接上代码：
--测试数据建立
Create TABLE JOIN_TEST(
COLA INT,
COLB INT
)
GO
INSERT INTO JOIN_TEST
SELECT 1,1
www.2cto.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.2cto.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.2cto.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 行受影响)
*/
作者 小材小用