SQL not in & in 拆開寫法

2018-07-03 345 words 2 minutes

Contents

在用程式動態組合 SQL 的 IN 和 NOT IN 條件時，很容易搞錯拆開後要用 OR 還是 AND，這篇用德摩根定律來解釋原因，並說明 NOT IN 遇到 NULL 的陷阱。

IN 和 NOT IN 的拆開邏輯

IN 條件拆開 → 用 OR

1
2
3
4
5


-- 原始寫法
WHERE status IN ('active', 'pending')

-- 等同於
WHERE status = 'active' OR status = 'pending'

邏輯：「符合任何一個條件」→ 用 OR

NOT IN 條件拆開 → 用 AND

1
2
3
4
5


-- 原始寫法
WHERE status NOT IN ('deleted', 'blocked')

-- 等同於
WHERE status != 'deleted' AND status != 'blocked'

邏輯：「每一個條件都不符合」→ 用 AND

德摩根定律的解釋

這個邏輯來自數學的德摩根定律（De Morgan’s Laws）：

1
2


NOT (A OR B)  ≡  (NOT A) AND (NOT B)
NOT (A AND B) ≡  (NOT A) OR  (NOT B)

對應到 SQL：

NOT IN ('a', 'b') = NOT (= 'a' OR = 'b') = != 'a' AND != 'b'

所以 NOT IN 拆開必須用 AND，如果用 OR 會得到完全相反的結果：

1
2
3
4


-- 錯誤寫法（邏輯錯誤）
WHERE status != 'deleted' OR status != 'blocked'
-- 這幾乎會匹配所有資料！
-- 因為任何值要麼不是 'deleted'，要麼不是 'blocked'

NOT IN 遇到 NULL 的陷阱

這是 NOT IN 最常見的 Bug 來源！

當 NOT IN 的子查詢或清單中包含 NULL 時，整個條件會回傳空結果：

1
2
3
4


-- 假設 blacklist 表中有一筆資料是 NULL
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM blacklist);
-- 如果 blacklist 中有任何 NULL，這個查詢回傳 0 筆資料！

原因：SQL 的 NULL 比較遵循三值邏輯（TRUE/FALSE/UNKNOWN）。

1
2
3
4
5


-- NULL 的比較結果是 UNKNOWN，不是 TRUE 也不是 FALSE
SELECT 1 WHERE 5 NOT IN (1, 2, NULL);
-- 等同於：5 != 1 AND 5 != 2 AND 5 != NULL
-- 等同於：TRUE AND TRUE AND UNKNOWN
-- 結果：UNKNOWN → 不會回傳任何資料

建議使用 NOT EXISTS 替代

NOT EXISTS 不受 NULL 影響，是更安全的寫法：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


-- NOT IN（有 NULL 風險）
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM blacklist);

-- NOT EXISTS（更安全）
SELECT * FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM blacklist b
    WHERE b.user_id = u.id
);

NOT EXISTS 只要找不到匹配的列就回傳 TRUE，NULL 不會影響結果。

比較總結

條件	拆開用	NULL 安全
`IN (a, b)`	`= a OR = b`	是
`NOT IN (a, b)`	`!= a AND != b`	否（子查詢含 NULL 會回傳空集合）
`EXISTS`	—	是
`NOT EXISTS`	—	是（推薦替代 NOT IN）