| 特性 | UNION | UNION ALL |
|---|---|---|
| 重複行處理 | 自動去重(Distinct) | 保留所有行,含重複 |
| 排序/雜湊成本 | 需排序或雜湊去重,資源消耗大 | 單純串聯,無額外成本 |
| 適用場景 | 業務要求結果唯一,且無法確定來源無重複 | 來源互斥、或允許重複、或追求極致效能 |
假設表 UserTB:

SELECT * FROM dbo.UserTB WHERE deptId IN (1, 3)
UNION
SELECT * FROM dbo.UserTB WHERE deptId IN (1, 2);
結果: deptId=1 的資料僅出現一次。

SELECT * FROM dbo.UserTB WHERE deptId IN (1, 3)
UNION ALL
SELECT * FROM dbo.UserTB WHERE deptId IN (1, 2);
結果: deptId=1 的資料出現兩次。

UNION 會出現 Hash Match (Aggregate) 或 Sort (Distinct) 運算子;UNION ALL 僅有 Concatenation。UNION 可能導致 TempDB 壓力劇增、記憶體授予等待。若需去重但想控制策略,可用 UNION ALL + ROW_NUMBER():
WITH Combined AS (
SELECT *, 1 AS src FROM dbo.UserTB WHERE deptId IN (1, 3)
UNION ALL
SELECT *, 2 AS src FROM dbo.UserTB WHERE deptId IN (1, 2)
)
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY src) AS rn
FROM Combined
) t WHERE rn = 1;
此法可指定保留來源優先順序,並避免全域排序。