SQL Server 提供三大集合運算子 UNION、EXCEPT、INTERSECT，對應關係代數的並集、差集、交集。這些運算子作用於結果集層級，自動處理重複值去除 (對應 DISTINCT 語意)，是資料整合、比對、稽核的核心工具。
語法與視覺化對照
| 運算子 | 關係代數 | 語意 | Venn 圖 | 保留重複 |
|---|
UNION | A ∪ B | A 和 B 所有不重複列 | ⭕⭕ 完整合併 | ❌ 自動 DISTINCT |
UNION ALL | A ⊎ B | A 和 B 所有列 (含重複) | ⭕⭕ 疊加 | ✅ 保留所有 |
EXCEPT | A \ B | 在 A 中但不在 B 中的列 | ⭕➖ A 獨有 | ❌ 自動 DISTINCT |
INTERSECT | A ∩ B | 同時在 A 和 B 中的列 | ⭕⭕ 重疊區域 | ❌ 自動 DISTINCT |
前置條件 (強制要求)
- 欄位數量相同：每個查詢
SELECT 清單欄位數必須一致
- 資料型別相容：對應位置欄位型別隱含轉換優先順序相容 (如
INT 與 BIGINT、VARCHAR 與 NVARCHAR)
- 順序對應：依
SELECT 清單順序一一對應，非依欄位名稱
實戰範例
-- 建立測試資料
CREATE TABLE DeptA (EmpID INT, EmpName NVARCHAR(20), Dept NVARCHAR(10));
CREATE TABLE DeptB (EmpID INT, EmpName NVARCHAR(20), Dept NVARCHAR(10));
INSERT INTO DeptA VALUES (1, N'張三', N'銷售'), (2, N'李四', N'銷售'), (3, N'王五', N'技術');
INSERT INTO DeptB VALUES (2, N'李四', N'銷售'), (3, N'王五', N'技術'), (4, N'趙六', N'人資');
-- 1. UNION - 所有員工不重複
SELECT EmpID, EmpName, Dept FROM DeptA
UNION
SELECT EmpID, EmpName, Dept FROM DeptB;
-- 結果: 1張三, 2李四, 3王五, 4趙六 (4筆)
-- 2. UNION ALL - 保留重複 (李四、王五各出現兩次)
SELECT EmpID, EmpName, Dept FROM DeptA
UNION ALL
SELECT EmpID, EmpName, Dept FROM DeptB;
-- 結果: 6筆
-- 3. EXCEPT - 僅在 A 不在 B (張三)
SELECT EmpID, EmpName, Dept FROM DeptA
EXCEPT
SELECT EmpID, EmpName, Dept FROM DeptB;
-- 結果: 1張三銷售 (1筆)
-- 4. INTERSECT - 同時在 A 和 B (李四、王五)
SELECT EmpID, EmpName, Dept FROM DeptA
INTERSECT
SELECT EmpID, EmpName, Dept FROM DeptB;
-- 結果: 2李四銷售, 3王五技術 (2筆)
進階應用場景
資料比對稽核：找出異動
-- 今日 vs 昨日 價格異動商品
SELECT ProductID, Price FROM TodayPrices
EXCEPT
SELECT ProductID, Price FROM YesterdayPrices;
-- 價格有變動的商品 (含新增/刪除/價格異動)
-- 結合 UNION ALL 區分新增/刪除/異動
SELECT 'NEW' AS ChangeType, ProductID, Price FROM TodayPrices
EXCEPT SELECT 'NEW', ProductID, Price FROM YesterdayPrices
UNION ALL
SELECT 'DEL', ProductID, Price FROM YesterdayPrices
EXCEPT SELECT 'DEL', ProductID, Price FROM TodayPrices
UNION ALL
SELECT 'CHG', ProductID, Price FROM TodayPrices
INTERSECT SELECT 'CHG', ProductID, Price FROM YesterdayPrices; -- 邏輯需調整
權限矩陣運算
-- 使用者實際權限 = 角色權限 UNION 個人授權 EXCEPT 明確拒絕
SELECT Permission FROM RolePermissions WHERE RoleID IN (SELECT RoleID FROM UserRoles WHERE UserID = @uid)
UNION
SELECT Permission FROM UserPermissions WHERE UserID = @uid
EXCEPT
SELECT Permission FROM UserDenials WHERE UserID = @uid;
分頁去重 (舊版替代方案)
-- 取得第 2 頁不重複資料 (假設 PK 為 ID)
SELECT * FROM (
SELECT DISTINCT * FROM LargeTable
) AS T
ORDER BY ID
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
-- 等價於 (舊版)
SELECT TOP 10 * FROM (
SELECT TOP 30 * FROM (SELECT DISTINCT * FROM LargeTable) T1 ORDER BY ID
) T2 ORDER BY ID DESC; -- 複雜且易錯
效能特性與執行計畫
| 運算子 | 典型執行計畫 | 效能特性 | 優化建議 |
|---|
UNION ALL | Concatenation | 最快，無排序/去重，流式處理 | 優先使用，應用層去重或確知無重複 |
UNION | Merge Join (Distinct) / Hash Match (Aggregate) | 需排序或雜湊去重，記憶體/TempDB 壓力大 | 確保輸入已排序 (索引)，或用 UNION ALL + 應用層去重 |
EXCEPT/INTERSECT | Left Anti Semi Join / Left Semi Join + Distinct | 類似 JOIN 邏輯，需去重 | 大量資料改用 NOT EXISTS / EXISTS 往往更穩定 |
集合運算 vs JOIN 比較
| 場景 | 集合運算 | JOIN 替代 | 建議 |
|---|
| 找差異 (A-B) | EXCEPT | NOT EXISTS / LEFT JOIN ... WHERE B.PK IS NULL | JOIN 可控制比較欄位、支援複合鍵、計畫更穩定 |
| 找共同 (A∩B) | INTERSECT | INNER JOIN / EXISTS | JOIN 彈性高、可選取單邊欄位 |
| 合併去重 | UNION | UNION ALL + ROW_NUMBER() 去重 | 視資料量與索引決定 |
常見陷阱
- NULL 比較：集合運算視
NULL = NULL 為相等 (不同於一般比較)，EXCEPT 會將雙方同位置 NULL 視為相同而排除
- 隱含轉換：
SELECT 1 UNION SELECT '1' 可能因優先順序轉換失敗報錯，建議顯式 CAST
- ORDER BY 位置：只能在最後一個查詢後加
ORDER BY，且需用位置序號或別名
- 大量資料記憶體溢位：
UNION/EXCEPT/INTERSECT 需在 TempDB 建立工作表，大量資料會導致 TempDB 擴充、甚至磁碟溢寫
現代化替代：EXCEPT/INTERSECT 改寫為 EXISTS/NOT EXISTS
-- EXCEPT 改寫 (可控制比較欄位、避免隱含轉換、利用索引)
SELECT A.Col1, A.Col2 FROM TableA A
WHERE NOT EXISTS (
SELECT 1 FROM TableB B
WHERE B.Col1 = A.Col1 AND B.Col2 = A.Col2
);
-- INTERSECT 改寫
SELECT A.Col1, A.Col2 FROM TableA A
WHERE EXISTS (
SELECT 1 FROM TableB B
WHERE B.Col1 = A.Col1 AND B.Col2 = A.Col2
);
集合運算子語意清晰、SQL 簡潔，適合中小資料量、臨時比對、稽核腳本。大量生產資料、複雜比較邏輯、需索引利用時，改寫為 EXISTS/NOT EXISTS 或 JOIN 通常效能更可控。理解其背後的 Distinct Sort/Hash Match/Semi Join 機制，才能在效能調校時做正確選擇。
