NULL 不是值,是「不知道」

· tech

#sql#concept

上一篇的 LEFT JOIN,會幫沒配到的列補上 NULL。那個 NULL 就是這篇的主角——它是無數 SQL bug 的源頭,而根本原因只有一句:NULL 不是一個值,是「不知道」。 一旦你把它讀成「不知道」而不是「0」或「空字串」,一票怪事就全解釋得通了。

跟 NULL 比較,結果是第三種:UNKNOWN

因為 NULL 代表「不知道」,所以任何跟它的比較,答案也只能是「不知道」。age = NULL 不是 TRUE、也不是 FALSE,而是第三種邏輯值:UNKNOWN。SQL 的邏輯有三個值,不是兩個:

SQL 的邏輯有三個值(不是兩個) TRUE 條件成立 FALSE 條件不成立 UNKNOWN 不知道 ↑ 任何跟 NULL 的比較都落這裡 age = NULL、age <> NULL 都是 WHERE / ON / HAVING 只放行 TRUE TRUE → ✓ 保留 FALSE → ✗ 丟 UNKNOWN → ✗ 丟
= NULL 得到的是 UNKNOWN,不是 FALSE;而 WHERE 只放行 TRUE,所以 UNKNOWN 跟 FALSE 一樣被丟。這就是為什麼 WHERE age = NULL 永遠篩不到東西——要改用 IS NULL

所以第一條鐵律:判斷 NULL,只能用 IS NULL / IS NOT NULL,不能用 = NULL / <> NULL。 後者不會報錯,只會默默永遠不成立——這正是它陰險的地方。

三值邏輯:UNKNOWN 會「傳染」

有了第三個值,AND / OR 的真值表就多了一整排。重點只有兩格要記:

ANDTRUEFALSEUNKNOWN
TRUETRUEFALSEUNKNOWN
FALSEFALSEFALSEFALSE
UNKNOWNUNKNOWNFALSEUNKNOWN

看這兩格就好:TRUE AND UNKNOWN = UNKNOWN(一串 AND 只要摻進一個 UNKNOWN、又沒有任何 FALSE,結果就卡在 UNKNOWN,永遠到不了 TRUE);而 FALSE AND UNKNOWN = FALSE(FALSE 有吸收性,能擋住傳染)。這條「UNKNOWN 會傳染」的規則,正是下面那個經典坑的根。

最陰險的坑:NOT IN 遇到 NULL 回空

這是我看過最多人栽、也最難自己看出來的一個。你想「找出不在黑名單裡的使用者」:

SELECT * FROM users
WHERE id NOT IN (SELECT blocked_id FROM blacklist);

只要那個子查詢回傳的清單裡有一個 NULL,這段就會回傳空集合——一筆都選不出來。為什麼?把 NOT IN 展開就看穿了:

x NOT IN (1, 2, NULL) 展開成一串 AND x<>1 AND x<>2 AND x<>NULL 代入 x=5 求值 TRUE AND TRUE AND UNKNOWN 整串永遠到不了 TRUE → 每一列都被丟 → 結果是空集合 ❌
NOT IN 展開成一串 AND,只要清單裡有 NULL,就多一項 x <> NULL = UNKNOWN。由「UNKNOWN 會傳染」,整串卡在 UNKNOWN、到不了 TRUE,於是**每一列**都被淘汰

修法有兩個,擇一:改用 NOT EXISTS(它不受 NULL 影響,語義也更清楚),或在子查詢裡先 WHERE blocked_id IS NOT NULL 把 NULL 濾掉。我的預設是前者:

SELECT * FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM blacklist b WHERE b.blocked_id = u.id
);

其他一定會遇到的 NULL 行為

同一個「NULL = 不知道」的原則,還延伸出一串你遲早會撞到的行為:

  • COUNT(*) 數所有列,COUNT(col) 只數非 NULL 的——兩者差幾筆,就是那欄有幾個 NULL。更精確地說,COUNT(x) 的定義是「數 x 不為 NULL 的列數」,所以 COUNT(<常數>) 只看那個常數是不是 NULL:COUNT(1) = 每列都算 = COUNT(*);而 COUNT(NULL) 恆為 0(常數 NULL 對每列都是 NULL,一列都數不到)。順帶破除迷思:COUNT(1) 不會比 COUNT(*) 快,優化器當同一件事處理,COUNT(*) 語義最清楚。
  • SUM / AVG / MAX 忽略 NULL。注意 AVG:它的分母是「非 NULL 的筆數」,不是把 NULL 當 0 算——你以為的平均,可能不是你算的那個。
  • GROUP BY 把所有 NULL 併成同一組(這裡 SQL 反而當它們「相等」,是個例外)。
  • ORDER BY:PostgreSQL 預設 ASC 時 NULL 排最後,可用 NULLS FIRST / NULLS LAST 明講。
  • UNIQUE 約束允許多個 NULL:因為 NULL = NULL 也是 UNKNOWN,兩個 NULL 不算「重複」。

防身工具

處理 NULL 有幾個很實用的工具,記起來:

COALESCE(x, 0)              -- x 是 NULL 就給預設值 0(可接多個候選)
NULLIF(a, b)               -- a = b 時回 NULL;常用來防除以零:x / NULLIF(y, 0)
x IS DISTINCT FROM y       -- null-safe 的「不等於」:把 NULL 當普通值比,NULL 跟 NULL 算相等

IS DISTINCT FROM 特別好用:當你要比較兩個「可能是 NULL」的欄位、又希望「兩邊都是 NULL 算相同」時,用它就不會掉進 UNKNOWN 的坑。

反思

把 NULL 讀成「不知道」,一整排坑一次填平

我剛學 SQL 時,把這些當成一條條要背的怪規則:= NULL 不行、NOT IN 會出事、AVG 忽略 NULL……後來發現它們是同一個念頭的延伸——NULL 是「不知道」,不是「0」也不是「空」。跟「不知道」比較,結果當然是「不知道」(UNKNOWN);對一堆「不知道」取平均,當然得先把「不知道」排除在外。一旦這個語義換過來,我不再需要背規則,而是能推出 NULL 在任何情境下的行為。這跟執行順序、JOIN那兩篇的收穫是同一種:找到那個一以貫之的念頭,規則就退化成推論。

最貴的 bug,是「跑得出來、還不報錯」那種

NULL 的坑幾乎都不會讓你的 query 報錯——NOT IN 默默回空、AVG 默默算錯、= NULL 默默篩不到。這跟上一篇的「LEFT JOIN 悄悄變 INNER」是同一種陰險:能跑、不報錯、數字看起來也像對的,只是錯了。 這類 bug 最貴,因為沒有紅字提醒你,往往是下游對數字對到懷疑人生才追回來。我因此養成一條反射:只要條件裡出現 NOT IN,或對一個可能為 NULL 的欄位做比較,就先停下來問一句「這欄會不會有 NULL?」 這一問,擋掉的 bug 比任何工具都多。

在源頭就把 NULL 的語義定清楚

寫 query 時被 NULL 咬,很多時候問題其實在更上游:這個欄位當初為什麼允許 NULL?而且更麻煩的是,NULL 常常一詞多義——同一欄的 NULL,可能是「還沒發生」、「不適用」、也可能是「資料遺失」。我現在設計 schema 或 pipeline,會盡量在源頭就把它講清楚:能不放 NULL 就用明確的預設值,真要放,也想清楚它代表哪一種「沒有」。語義在源頭定清楚,下游才不用一路 COALESCE 猜它到底是什麼意思——這跟我對資料建模一貫的態度一致:混亂往上游推一步解決,比在每個下游各補一塊補丁划算。