SQL Server 2005 EXCEPT 與 INTERSECT 集合運算子完整指南

SQL Server 2005 引入 EXCEPT (差集) 與 INTERSECT (交集) 集合運算子，提供比 NOT IN/NOT EXISTS 更直觀、正確處理 NULL 值的集合運算方式。本文詳細說明語法規則、NULL 處理差異、優先順序、SELECT INTO 限制、ORDER BY 規則，並附 SQL 2000 替代寫法對比。

核心差異

語法規則

1. 結構相容：兩邊查詢欄位數、順序、資料型別必須相容
2. 自動去重：結果集自動 DISTINCT (類似 UNION)
3. NULL 視為相等：EXCEPT/INTERSECT 視 NULL = NULL，不同於 =、IN、JOIN (NULL ≠ NULL)
4. 優先順序：INTERSECT > EXCEPT > UNION (左至右同級)
5. SELECT INTO：必須出現在第一個查詢 (最左邊)
6. ORDER BY：只能在最後一個查詢後，欄位名稱必須參考最左邊查詢的別名/欄位名

測試資料建立

CREATE TABLE TableA (col1 INT);
INSERT INTO TableA VALUES (1),(1),(2),(3),(4),(4),(5),(NULL),(NULL);

CREATE TABLE TableB (col2 INT);
INSERT INTO TableB VALUES (NULL),(1),(2),(3);

CREATE TABLE TableC (col3 INT);
INSERT INTO TableC VALUES (1),(5),(6);

範例 1：EXCEPT - TableA 中不在 TableC 的非重複值

SELECT col1 FROM TableA
EXCEPT
SELECT col3 FROM TableC;

結果

col1
----
NULL
2
3
4

關鍵：NULL 出現在結果中，因 EXCEPT 視 NULL 相等，TableA 有 NULL、TableC 無 NULL → NULL 被保留。

範例 2：SQL 2000 用 NOT EXISTS 實現 EXCEPT

SELECT col1 FROM TableA a
WHERE NOT EXISTS (SELECT col3 FROM TableC WHERE a.col1 = col3)
GROUP BY col1;

結果相同 (含 NULL)。

範例 3：NOT IN 陷阱 (NULL 導致無結果)

SELECT col1 FROM TableA
WHERE col1 NOT IN (SELECT col3 FROM TableC)
GROUP BY col1;

結果

col1
----
2
3
4

原因：NOT IN 內部轉為 NOT (col1 = val1 OR col1 = val2 ...)，若子查詢含 NULL，col1 = NULL 為 UNKNOWN，整個 NOT (TRUE OR UNKNOWN) 為 UNKNOWN，該列被過濾。含 NULL 子查詢絕對不可用 NOT IN。

範例 4：優先順序 - INTERSECT 先於 EXCEPT

SELECT col1 FROM TableA
EXCEPT
SELECT col2 FROM TableB
INTERSECT
SELECT col3 FROM TableC;

等價於：TableA EXCEPT (TableB INTERSECT TableC)

結果

col1
----
NULL
2
3
4
5

TableB ∩ TableC = {1}，TableA \ {1} = {NULL,2,3,4,5}

範例 5：SELECT INTO 應用

SELECT col1 INTO #tem FROM TableA
EXCEPT
SELECT col3 FROM TableC;

SELECT * FROM #tem;
DROP TABLE #tem;

範例 6：ORDER BY 規則

SELECT col1 FROM TableA
INTERSECT
SELECT col3 FROM TableC
ORDER BY col1;  -- 必須用左邊查詢的欄位名/別名

結果

col1
----
1
5

最佳實務建議

• 集合運算優先使用 EXCEPT/INTERSECT，語意清晰、NULL 語意正確
• 複雜邏輯加括號明確優先順序：(A EXCEPT B) INTERSECT C
• 大量資料集合運算確保參與欄位有索引支援
• 需保留重複值時改用 EXCEPT ALL/INTERSECT ALL (SQL 2005 不支援，需 ROW_NUMBER() 模擬)
• 遷移舊碼時檢查所有 NOT IN 子查詢是否含 NULL，改用 NOT EXISTS 或 EXCEPT

適用版本

• SQL Server 2005+
• Azure SQL Database / Managed Instance
• PostgreSQL、Oracle、DB2、SQLite 等主流資料庫均支援 (標準 SQL)