Oracle IN vs EXISTS、NOT IN vs NOT EXISTS 深度效能解析與選擇指南

核心結論先行

IN 與 EXISTS 執行機制差異

IN：半連接 + Hash Join

• 優化器將子查詢轉為內部視圖，構建雜湊表。
• 外表掃描時探測雜湊表，適合內表小、外表大。
• 直觀易寫，支援非相關子查詢。

EXISTS：相關子查詢 + Nested Loop

• 對外表每一行，執行一次子查詢（類似迴圈）。
• 子查詢可利用外表欄位值過濾，適合外表小、內表大且有索引。
• 僅支援相關子查詢，語義為「存在即停止」。

NOT IN 致命缺陷

-- 危險範例：子查詢含 NULL 導致無結果
SELECT * FROM t1 WHERE id NOT IN (SELECT id FROM t2);  -- 若 t2.id 有 NULL，全表無結果

原因：NULL 參與比較結果為 UNKNOWN，導致整體判斷失效。且 NOT IN 通常退化為全表掃描 + Filter，無法利用索引。

NOT EXISTS 安全高效

-- 推薦寫法：語義精確、可用索引、免疫 NULL
SELECT * FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t2.id = t1.id);

• 子查詢可走 t2.id 索引。
• 語義明確：不存在匹配行即滿足。
• 執行計畫通常為 ANTI JOIN（Hash Join Anti 或 Nested Loop Anti）。

實戰決策流程圖

1. 是否為否定查詢（NOT）？
   → 是 → 直接用 NOT EXISTS（若確定無 NULL 且極小集合可考慮 NOT IN）。
2. 肯定查詢（IN/EXISTS）：
   → 估算子查詢結果集大小（執行計畫 CARDINALITY）。
   → 子查詢小（< 1萬行） → IN。
   → 子查詢大、外表小、有合適索引 → EXISTS。
   → 難以判斷 → 寫兩版對比執行計畫與實測時間。

進階提示

• Oracle 12c+ 適應性計畫可在運行時在 Nested Loop 與 Hash Join 間動態切換，減少人工調優負擔。
• 使用 /*+ UNNEST */ 或 /*+ NO_UNNEST */ Hint 強制/禁止子查詢展開為連接。
• IN (SELECT ...) 等價於 EXISTS (SELECT 1 FROM ... WHERE ... = outer.col)，優化器常自動轉換。

總結

「NOT IN 棄用，NOT EXISTS 首選」是鐵律。肯定查詢視數據量分佈與索引情況靈活選擇，執行計畫才是唯一標準，切勿迷信經驗法則。