Oracle 兩表交集查詢：INTERSECT 與 IN 子查詢效能比較

場景

表 A (ID, NAME)，表 B (ID, NAME, SCORE)，求兩表 ID 與 NAME 完全相符的列。

方法一：INTERSECT 集合運算

SELECT id, name FROM a
INTERSECT
SELECT id, name FROM b;

• 自動去重 (DISTINCT 語意)
• 要求兩查詢**欄位數、順序、資料型別完全相同**
• 執行計畫通常為 **SORT UNIQUE + INTERSECTION** 或 **HASH INTERSECTION**

方法二：IN 子查詢

SELECT id, name FROM a
WHERE (id, name) IN (SELECT id, name FROM b);

• 可針對部分欄位比對 (單欄用 WHERE id IN (...))
• 保留 A 表重複列 (不自動去重)
• 執行計畫常為 **HASH JOIN SEMI** 或 **FILTER + SUBQUERY**

效能比較 (百萬列測試環境)

優化建議

1. 比對欄位建立**複合索引** CREATE INDEX idx_a_id_name ON a(id, name);
2. 大表驅動小表：SELECT /*+ LEADING(b a) */ ...
3. 若 B 表極小，考慮 EXISTS 替代 IN：

   SELECT * FROM a
       WHERE EXISTS (SELECT 1 FROM b WHERE b.id=a.id AND b.name=a.name);

4. 避免在 IN 列表中使用 NULL (導致 UNKNOWN 語意)
5. 12c+ 可用 APPROX_COUNT_DISTINCT 快速估算交集大小

擴展：EXCEPT (MINUS) 與 UNION

• MINUS (Oracle) / EXCEPT (ANSI)：A 有 B 無的列
• UNION：合併去重；UNION ALL：合併不去重 (效能最佳)