[SQL] Except 和 Intersect

Except 和 Intersect 可以用來比較兩個資料集的差異。

從下方文氏圖可以看出 Except 找出差集、Intersect 找出交集

利用下述範例實作

1. Except：找出資料集 1 中有，但資料集 2 沒有的，並把資料集 1 重覆資料刪除，只留一筆
2. Intersect：找出資料集 1 和資料集 2 都有的，並把重覆資料刪除，只留一筆

DECLARE @T1 TABLE (EmpNO int , EmpName nchar(10))
INSERT INTO @T1 VALUES
 (1,N'蔡一') ,
 (2,N'陳二') ,
 (3,N'張三') ,
 (4,N'李四') ,
 (5,N'王五') ,
 (1,N'蔡一') , -- 故意重覆
 (3,N'張三') , -- 故意重覆
 (4,N'李四')   -- 故意重覆

DECLARE @T2 TABLE (EmpNO int , EmpName nchar(10))
INSERT INTO @T2 VALUES
 (1,N'蔡一') ,
 (4,N'李四')

SELECT EmpNO , EmpName
FROM @T1 -- 資料集 1
EXCEPT
SELECT EmpNO , EmpName
FROM @T2 -- 資料集 2

SELECT EmpNO , EmpName
FROM @T1 -- 資料集 1
INTERSECT
SELECT EmpNO , EmpName
FROM @T2 -- 資料集 2

運算子優先順序：Intersect 優先順序高於 Except，因此下述語法會先進行資料集 2 和 資料集 3 的 INTERSECT，其結果再和資料集 1 進行 EXCEPT，結果會是上圖 Except 結果

SELECT EmpNO , EmpName
FROM @T1 -- 資料集 1
EXCEPT
SELECT EmpNO , EmpName
FROM @T1 -- 資料集 2
INTERSECT
SELECT EmpNO , EmpName
FROM @T2 -- 資料集 3

備註

1. 兩個資料集的資料行數目與順序都要相同
2. 兩個資料集的資料類型必須相容（隱含式轉換）

• 2013 DBA 天團

• 20150606

Querying with Transact-SQL Module4 測驗時，發現到 Except 會移除重複的資料，原來已經筆記過，Orz

延伸閱讀
• UNION

參考資料
• 論壇問題出處
• EXCEPT 和 INTERSECT (Transact-SQL)
• 使用 UNION、EXCEPT 和 INTERSECT 搭配其他 Transact-SQL 陳述式
• 使用 EXCEPT 和 INTERSECT 執行半聯結
• The EXCEPT and INTERSECT Operators in SQL Server
• Except只會回傳經過distinct的資料列