Oracle 中 EXISTS、NOT EXISTS、IN、NOT IN 對 NULL 處理的深度差異分析

Oracle 子查詢與集合運算中 NULL 值的邏輯陷阱

在 Oracle SQL 中，IN/NOT IN、EXISTS/NOT EXISTS 對 NULL 的處理邏輯截然不同，誤用極易導致查詢結果與預期不符。本文通過實測案例深度解析其底層邏輯差異。

一、IN / NOT IN 與 NULL 的三值邏輯陷阱

核心規則： Oracle 中 NULL 無法與任何值比較（含自身），NULL = NULL 結果為 UNKNOWN，非 TRUE。

實測案例：

SELECT 1 result1 FROM dual WHERE 1 NOT IN (2, 3);           -- 返回 1
SELECT 1 result2 FROM dual WHERE 1 NOT IN (2, 3, NULL);     -- 無返回
SELECT 1 result3 FROM dual WHERE 1 IN (2, 3, NULL, 1);      -- 返回 1
SELECT 1 result4 FROM dual WHERE 1 IN (2, 3, NULL);         -- 無返回

邏輯解析：

• a IN (b, c, d) 等價於 (a=b) OR (a=c) OR (a=d)。只要有一個為 TRUE，整體為 TRUE；NULL 導致該項為 UNKNOWN，不影響其他 TRUE 項。
• a NOT IN (b, c, d) 等價於 (a!=b) AND (a!=c) AND (a!=d)。只要列表中包含 NULL，該項為 UNKNOWN，導致整體 AND 為 UNKNOWN（視同 FALSE），結果永不返回任何行。

二、EXISTS / NOT EXISTS：基於行存在性的布爾判斷

核心規則： EXISTS 僅判斷子查詢是否返回至少一行，不關心具體值；NOT EXISTS 判斷是否無行返回。

實測案例：

SELECT 1 result5 FROM dual WHERE NOT EXISTS (SELECT 1 FROM dual t WHERE t.dummy = NULL); -- 返回 1
SELECT 1 result6 FROM dual WHERE EXISTS (SELECT 1 FROM dual t WHERE t.dummy = NULL);      -- 無返回

邏輯解析：

• 子查詢 WHERE t.dummy = NULL 永無行返回（因 NULL 不可比較）。
• NOT EXISTS 見到空結果集 → 判斷為 TRUE → 外層返回行。
• EXISTS 見到空結果集 → 判斷為 FALSE → 外層無返回。

三、多欄位 NOT IN 的坐標比較邏輯

WHERE (a, b) NOT IN ((c, d), (e, f))
-- 等價於 ((a!=c OR b!=d)) AND ((a!=e OR b!=f))

任一座標不匹配即視為該元組不相等。若元組中含 NULL，該元組比較結果為 UNKNOWN，導致整體 AND 為 UNKNOWN。

四、總結對照表

五、效能與最佳實踐建議

• 避免 NOT IN 含子查詢： 若子查詢可能返回 NULL，結果將錯誤且無法利用索引有效執行。
• 優先使用 NOT EXISTS： 語義明確、不受 NULL 干擾、通常有更好執行計劃（可轉為 Anti-Join）。
• 若必須用 NOT IN： 在子查詢加 WHERE col IS NOT NULL 過濾，或改寫為 NOT EXISTS。
• 效能非絕對： 子查詢極小結果集時 NOT IN 可能較快；大結果集、有索引時 NOT EXISTS 通常優勝。建議實測執行計劃決定。