核心差異速查
| 運算子 | 重複列處理 | 排序/去重成本 | 適用場景 |
|---|
| UNION | 移除重複列 (DISTINCT) | 高 (需排序/雜湊去重) | 業務邏輯要求結果唯一，且兩集合可能重疊 |
| UNION ALL | 保留所有列 (含重複) | 極低 (單純串接) | 明確知道不重疊、或需保留重複、或後續再處理去重 |
實戰範例
-- 假設 UserTB 有 deptId 欄位
-- 查詢部門 1,3 並集 部門 1,2
-- UNION (去重)
SELECT * FROM dbo.UserTB WHERE deptId IN (1, 3)
UNION
SELECT * FROM dbo.UserTB WHERE deptId IN (1, 2);
-- 部門 1 的資料僅出現一次
-- UNION ALL (保留重複)
SELECT * FROM dbo.UserTB WHERE deptId IN (1, 3)
UNION ALL
SELECT * FROM dbo.UserTB WHERE deptId IN (1, 2);
-- 部門 1 的資料出現兩次
效能實測建議
- 資料量大 (百萬列+) 且確知無重複時，**永遠優先用 UNION ALL**，避免昂貴的 Sort Distinct 操作符。
- 若必須去重，考慮：
① 先 `UNION ALL` 再外層 `SELECT DISTINCT * FROM (...)` (有時優化器可下推去重)；
② 確保參與合併的欄位有適當索引支援排序。
- 執行計畫關鍵字：`UNION` → `Merge Join (Concatenation) + Distinct Sort`；`UNION ALL` → `Concatenation` 單一操作符。
進階用法
1. 多集合合併 (避免巢狀 UNION)
SELECT col FROM A
UNION ALL SELECT col FROM B
UNION ALL SELECT col FROM C
UNION ALL SELECT col FROM D;
2. 加入來源標記
SELECT 'Dept13' AS src, * FROM UserTB WHERE deptId IN (1,3)
UNION ALL
SELECT 'Dept12' AS src, * FROM UserTB WHERE deptId IN (1,2);
3. 遞歸 CTE 結合 UNION ALL (階層查詢)
WITH Hierarchy AS (
SELECT id, parent_id, name, 0 AS lvl FROM Org WHERE parent_id IS NULL
UNION ALL
SELECT o.id, o.parent_id, o.name, h.lvl + 1
FROM Org o JOIN Hierarchy h ON o.parent_id = h.id
)
SELECT * FROM Hierarchy OPTION (MAXRECURSION 0);
相容性提醒
- 所有參與合併的 `SELECT` 必須：
① 欄位數量相同
② 對應位置資料類型相容 (隱含轉換規則)
③ `ORDER BY` 僅能放在最後一個 `SELECT` 之後 (或包裝在子查詢/CTV 中)。
- SQL Server 不支援 `UNION DISTINCT` 關鍵字 (標準 SQL)，`UNION` 預設即為 `DISTINCT`。
