Oracle 集合運算符詳解：UNION、INTERSECT、MINUS 實戰指南

Oracle 集合運算符詳解：UNION、INTERSECT、MINUS 實戰指南

集合運算符用於合併多個查詢結果集，是 SQL 編寫中處理「並集、交集、差集」的利器。本文通過語法、去重規則、排序行為、列對應要求及實戰示例，全面解析四大運算符。

一、 四大運算符對照表

二、 核心語法規則 (必須遵守，否則報錯)

1. 列數一致：每個 SELECT 列表的列數必須相同。
2. 數據類型兼容：對應位置的列類型必須隱式可轉換 (數字/字符/日期等)。
3. 列名以第一個查詢為準：結果集列名取自第一個 SELECT。
4. ORDER BY 只能放在最後：且只能引用第一個查詢的列別名或位置序號。

三、 實戰示例

-- 假設表 A、B 結構相同 (id, name)

-- 1. UNION 去重合併
SELECT id, name FROM tableA
UNION
SELECT id, name FROM tableB;

-- 2. UNION ALL 保留重複 (性能最佳，無排序開銷)
SELECT id, name FROM tableA
UNION ALL
SELECT id, name FROM tableB;

-- 3. INTERSECT 交集
SELECT id, name FROM tableA
INTERSECT
SELECT id, name FROM tableB;

-- 4. MINUS 差集 (在 A 中但不在 B 中)
SELECT id, name FROM tableA
MINUS
SELECT id, name FROM tableB;

四、 進階技巧與避坑

1. 控制排序：包裝外層查詢

SELECT * FROM (
  SELECT id, name FROM tableA
  UNION ALL
  SELECT id, name FROM tableB
) ORDER BY id DESC; -- 這樣才能自定義排序

2. 模擬 FULL OUTER JOIN (Oracle 支持 FULL JOIN，但集合運算思路通用)

SELECT * FROM tableA
FULL OUTER JOIN tableB USING (id)
-- 等價於
SELECT id, name FROM tableA
UNION
SELECT id, name FROM tableB; -- 僅當 name 也相同才等價

3. 多集合鏈式運算：優先級相同，左到右執行

SELECT * FROM t1
UNION
SELECT * FROM t2
INTERSECT
SELECT * FROM t3
-- 等價於 (t1 UNION t2) INTERSECT t3
-- 建議用括號明確意圖 (Oracle 不支持括號，需用內聯視圖)
SELECT * FROM (
  SELECT * FROM t1 UNION SELECT * FROM t2
) INTERSECT SELECT * FROM t3;

4. 性能選型建議

• 明確不需要去重、或業務允許重複 → 首選 UNION ALL，避免昂貴的 SORT UNIQUE。
• 大數據量 INTERSECT/MINUS → 確保有合適索引，考慮改寫為 EXISTS/NOT EXISTS 或 JOIN 由優化器選擇 Hash/Anti Join。
• 配合 /*+ PARALLEL */ 提升大集合運算吞吐。

五、 總結

集合運算符語義清晰、SQL 簡潔，但隱式排序去重 (除 UNION ALL) 在大數據量下代價高昂。實戰中：

• 報表合併、日志匯總 → UNION ALL
• 權限對賬、差異比對 → MINUS / INTERSECT
• 結果需去重且數據量可控 → UNION
• 始終驗證執行計劃，必要時改寫為 JOIN/EXISTS 交給優化器。