SQL中常见的连接方式

USE master
GO


IF OBJECT_ID(N'tempdb..##test01') IS NOT NULL
DROP TABLE ##test01

IF OBJECT_ID(N'tempdb..##test02') IS NOT NULL
DROP TABLE ##test02


SELECT * INTO ##test01 
FROM (
SELECT 1 ID ,'A' NAME,'0001' CLASS 
UNION 
SELECT 2 ID ,'B' NAME,'0002' CLASS
UNION 
SELECT 3 ID ,'C' NAME,'0003' CLASS
) M1;

SELECT * INTO ##test02
FROM (
SELECT 1 ID ,'A班' CLASS_NAME 
UNION 
SELECT 2 ID ,'B班' CLASS_NAME
UNION 
SELECT 4 ID ,'D班' CLASS_NAME
) M2;

--SQL server 中只有join ：
-- 相当于inner join 的方式，表示两张表的共有部分
--JOIN ON
SELECT * FROM  ##test01 T1 JOIN ##test02 T2 ON T1.ID = T2.ID
--INNER JOIN ON
SELECT * FROM  ##test01 T1 INNER JOIN ##test02 T2 ON T1.ID = T2.ID
--LEFT JOIN ON
SELECT * FROM  ##test01 T1 LEFT JOIN ##test02 T2 ON T1.ID = T2.ID
--RIGHT JOIN ON 
SELECT * FROM  ##test01 T1 RIGHT JOIN ##test02 T2 ON T1.ID = T2.ID
--CROSS JOIN ;表交叉连接
SELECT * FROM  ##test01 T1 CROSS JOIN ##test02 T2 WHERE T1.ID = T2.ID
--FULL JOIN ON 
SELECT * FROM  ##test01 T1 FULL JOIN ##test02 T2 ON T1.ID = T2.ID