Oracle 集合運算子實戰：INTERSECT 交集與 MINUS 差集完整指南

Oracle 提供三大集合運算子：UNION（聯集）、INTERSECT（交集）、MINUS（差集）。本文聚焦 INTERSECT 與 MINUS 實戰場景、語法規則、效能考量與替代寫法。

INTERSECT：求多個查詢結果的交集

語法規則

• 參與查詢的欄位數、順序、資料型別必須相容。
• 結果自動去重（等同 DISTINCT）。
• ORDER BY 僅能放在最後一個查詢之後，且需使用位置編號或別名。

實戰範例：找出所有部門共有的職稱

SELECT job FROM account
INTERSECT
SELECT job FROM research
INTERSECT
SELECT job FROM sales;

等價 JOIN 寫法（效能通常更好，可利用索引）：

SELECT DISTINCT a.job
FROM   account a
JOIN   research r ON a.job = r.job
JOIN   sales s    ON a.job = s.job;

MINUS：求第一個查詢結果中不存在於第二個查詢的列

語法規則

• 同 INTERSECT：欄位數/順序/型別相容、自動去重。
• A MINUS B ≠ B MINUS A（非交換律）。

實戰範例：財會部有而銷售部沒有的職稱

SELECT job FROM account
MINUS
SELECT job FROM sales;

等價 NOT EXISTS/LEFT JOIN 寫法：

-- NOT EXISTS（推薦，語意清晰）
SELECT DISTINCT a.job
FROM   account a
WHERE  NOT EXISTS (SELECT 1 FROM sales s WHERE s.job = a.job);

-- LEFT JOIN + IS NULL
SELECT DISTINCT a.job
FROM   account a
LEFT JOIN sales s ON a.job = s.job
WHERE  s.job IS NULL;

效能與優化建議

進階：UNION ALL + 分組計數模擬 INTERSECT/MINUS（大數據量）

-- INTERSECT 模擬：出現次數 = 表數
SELECT job
FROM (
  SELECT job FROM account
  UNION ALL
  SELECT job FROM research
  UNION ALL
  SELECT job FROM sales
)
GROUP BY job
HAVING COUNT(*) = 3;

-- MINUS 模擬：僅出現在第一個來源
SELECT job
FROM (
  SELECT job, 1 AS src FROM account
  UNION ALL
  SELECT job, 2 AS src FROM sales
)
GROUP BY job
HAVING COUNT(CASE WHEN src=1 THEN 1 END) > 0
   AND COUNT(CASE WHEN src=2 THEN 1 END) = 0;

此法避免排序去重，配合並行查詢在億級資料上效能遠優於集合運算子。

常見錯誤

• 欄位順序不一致：SELECT a,b FROM t1 INTERSECT SELECT b,a FROM t2 → 錯誤比較。
• 資料型別隱式轉換失敗：NUMBER vs VARCHAR2 存數字字串 → ORA-01790。
• ORDER BY 放在中間查詢 → ORA-00933。

善用 INTERSECT/MINUS 解決「共同擁有」「僅擁有」類業務問題，大數據量時轉為 JOIN/NOT EXISTS 或 UNION ALL + GROUP BY 獲取最佳效能。