SQL 中 NOT IN 與 NOT EXISTS 的細微差異：避開空值陷阱

現象：同樣邏輯，結果卻不同

-- 查詢 1：NOT IN
SELECT * FROM A WHERE id NOT IN (SELECT id FROM B);

-- 查詢 2：NOT EXISTS
SELECT * FROM A WHERE NOT EXISTS (SELECT 1 FROM B WHERE B.id = A.id);

預期結果一致，實際執行：查詢 1 返回 1 行，查詢 2 返回空集。

根因：三值邏輯與 NULL

• NOT IN 內部轉為 id <> ALL (subquery)。若子查詢結果集包含 NULL，id <> NULL 判斷為 UNKNOWN，導致整個 NOT IN 判斷為 UNKNOWN，該行被過濾掉。
• NOT EXISTS 僅關心子查詢是否返回行，NULL 不影響布林判斷，語義更直觀。

驗證腳本

CREATE TABLE A (id INT);
CREATE TABLE B (id INT);
INSERT INTO A VALUES (1),(2);
INSERT INTO B VALUES (1),(NULL);

SELECT 'NOT IN' AS q, * FROM A WHERE id NOT IN (SELECT id FROM B);   -- 0 行
SELECT 'NOT EXISTS' AS q, * FROM A WHERE NOT EXISTS (SELECT 1 FROM B WHERE B.id = A.id); -- 1 行 (id=2)

最佳實踐

1. 優先用 NOT EXISTS：語義清晰、免疫 NULL、執行計畫通常更優（Anti Semi Join）。
2. 若必須用 NOT IN，請在子查詢加 WHERE id IS NOT NULL 或對欄位加 NOT NULL 約束。
3. LEFT JOIN ... WHERE right.id IS NULL 同樣安全，且便於檢查被排除行的屬性。

效能補充

• 現代優化器對 NOT EXISTS 與 LEFT JOIN ... IS NULL 通常產生相同計畫。
• NOT IN 若子查詢無 NULL 且有索引，效能相近；但一旦有 NULL，優化器可能退化為全表掃描。

細節決定成敗，養成「子查詢去 NULL」或「直用 NOT EXISTS」的習慣，能省下無數除錯夜。