卷 II · 取数CH 06深度 06/24

WHERE 与三值逻辑：NULL 不是值

几乎每个 SQL 用户都被 NULL 坑过，然后学会「小心它」，但很少有人说得清它为什么这么别扭。这一章把根挖出来：NULL 不是一个值，它是「不知道」——一旦接受这一点，它的所有怪脾气都变得合情合理。

NULL三值逻辑NOT IN 坑COALESCE

NULL 是「不知道」，不是「空」也不是「零」

第一个要扭转的直觉：NULL 不是空字符串 ''，不是数字 0，甚至不是「没有值」。它表示「这里有个值，但我不知道是什么」。

回到第 4 章那张表，Tolkien 的 country 是 NULL。它的意思不是「Tolkien 没有国籍」，而是「Tolkien 的国籍我们没记录、不知道」。这个「不知道」的语义，决定了 NULL 参与任何运算时的行为——因为「不知道」和任何东西运算，结果往往还是「不知道」。

SELECT NULL + 1;        -- NULL：不知道的数加一，还是不知道
SELECT NULL || 'abc';   -- NULL：不知道的字符串拼上 abc，还是不知道
SELECT NULL = NULL;     -- NULL：不知道 等不等于 不知道 ？—— 不知道！

最后一句是关键中的关键：NULL = NULL 的结果不是 TRUE，而是 NULL。两个「不知道」的东西，你没法断定它们相等——万一它们其实是不同的值呢？所以答案只能是「不知道」。

于是 SQL 有三个真值，不是两个

普通编程语言的布尔只有两档：真、假。但一旦引入「不知道」，逻辑就得扩成三值逻辑：TRUE、FALSE、UNKNOWN（就是 NULL）。AND、OR、NOT 都要为这第三档重新定义：

aba AND ba OR b
TRUEFALSEFALSETRUE
TRUENULLNULLTRUE
FALSENULLFALSENULL
NULLNULLNULLNULL

规律其实很符合直觉：TRUE OR 不知道 = TRUE（反正有个真的，整体就真，管另一个是啥）；FALSE AND 不知道 = FALSE（有个假的，与出来就假）。但 TRUE AND 不知道 = 不知道——结果取决于那个不知道的东西，所以整体也不知道。

▶ 动手 · 三值逻辑计算器

下面这台真引擎让你随便给 a、b 挑 TRUE / FALSE / NULL，选一种运算，看它算出真正的三值结果。多试几组，把上面那张表的每一格都验证一遍——尤其盯着 NULL = NULL。

WHERE 只放行「TRUE」，不放行「不知道」

现在来看这三值逻辑对查询的直接后果。WHERE 的规则是：只保留条件为 TRUE 的行；FALSE 和 NULL（不知道）都丢掉。

所以想找「国籍不是 US 的作者」，这样写会漏掉 Tolkien：

SELECT name FROM authors WHERE country != 'US';
-- 只返回 Liu、Borges —— Tolkien 不见了！

因为 Tolkien 的 country 是 NULL，NULL != 'US' 算出来是 NULL（不知道），不是 TRUE，于是这一行被 WHERE 丢掉。「国籍未知」的人，既不满足「= US」，也不满足「!= US」——他掉进了三值逻辑的缝里。要把他捞回来，得显式处理 NULL：

SELECT name FROM authors
WHERE country != 'US' OR country IS NULL;

注意判断是不是 NULL，只能用 IS NULL / IS NOT NULL，不能用 = NULL（那永远是 NULL，永远不成立）。这是新手最常见的错。

那个会让你线上出事的坑：NOT IN + NULL

三值逻辑最阴险的一次伏击，是 NOT IN 遇上 NULL。看这句本该返回「id 不是 1、2 的书」的查询：

SELECT * FROM books
WHERE id NOT IN (1, 2, NULL);   -- 期望：id 为 3,4,5,6 的书
(0 rows)

一行都没有。为什么？因为 id NOT IN (1, 2, NULL) 会展开成：

id != 1  AND  id != 2  AND  id != NULL

最后那个 id != NULL 永远是 NULL（不知道）。而 TRUE AND TRUE AND NULL = NULL——整个条件再也不可能是 TRUE，于是每一行都被丢掉。上面 demo 里也复现了这个结果：0 行。

可怕的是，那个 NULL 常常不是你手写的，而是来自一个子查询：WHERE id NOT IN (SELECT author_id FROM ...)，只要子查询结果里混进一个 NULL，整个查询就悄无声息地返回空——本地测试数据没 NULL 时一切正常，线上真实数据有 NULL 时全线崩溃。

◆ 三条自保法则
  • 用 NOT EXISTS 代替 NOT IN（子查询版）：NOT EXISTS 用「存不存在匹配行」判断，不受子查询里 NULL 的影响，语义更稳，通常性能也更好。
  • 需要「NULL 安全的相等」时，用 IS DISTINCT FROM：a IS DISTINCT FROM b 把 NULL 当普通值比，两个 NULL 算「不 distinct」（相等），返回真正的 TRUE/FALSE，绝不返回 NULL。它的反面是 IS NOT DISTINCT FROM。
  • 该非空的列，建表时就 NOT NULL：从源头上不让 NULL 进来，是最省心的防御——这又回到了「当真」，卷 III 细说。

COALESCE 与聚合里的 NULL

处理 NULL 最常用的两个工具：

  • COALESCE(a, b, c, ...)：返回第一个非 NULL 的参数。COALESCE(country, '未知') 把 NULL 国籍显示成「未知」。
  • NULLIF(a, b)：当 a = b 时返回 NULL，否则返回 a。常用来把某个「哨兵值」转回 NULL，比如 NULLIF(divisor, 0) 防除零。

还有一个几乎人人踩过的点：聚合函数会跳过 NULL。这就是 count(*) 和 count(列) 的根本区别：

SELECT count(*)       FROM authors;   -- 4 —— 数所有行
SELECT count(country) FROM authors;   -- 3 —— 只数 country 非 NULL 的行（Tolkien 被跳过）
SELECT avg(price)     FROM books;     -- 只对非 NULL 的 price 求平均

count(*) 数行，count(列) 数「该列非空的行」。avg、sum 同理，只把非 NULL 值算进去。这在算「平均值」时尤其要留神——你以为的分母，可能悄悄小了。第 8 章讲聚合时还会碰到它。

⇄ 对照 MySQL
  • 三值逻辑、NULL = NULL 为 NULL、NOT IN 的坑：这些是 SQL 标准语义，MySQL 和 PostgreSQL 完全一致——这个坑在两家都存在，别以为换了库就没事。
  • NULL 安全相等：MySQL 有个专门的运算符 <=>（读作 null-safe equal），a <=> b 在两个都是 NULL 时返回 1。PostgreSQL 没有 <=>，对应的是标准 SQL 的 a IS NOT DISTINCT FROM b——更长，但两家都认标准写法。
  • 排序时 NULL 的位置：默认不同。PG 把 NULL 当「最大」（ASC 时排最后），MySQL 当「最小」（排最前）。迁移时想保持行为一致，就显式写 NULLS FIRST / NULLS LAST。

这一章的一句话

NULL 是「不知道」，所以 SQL 是三值逻辑；WHERE 只放行 TRUE，「不知道」的行会掉进缝里；NOT IN 混进 NULL 会让查询一行不返回——用 NOT EXISTS、IS DISTINCT FROM、和建表时的 NOT NULL 来自保。

下一章进入卷 II 的技术重头：JOIN。我们不光讲五种连接怎么写，还要顺带看 PostgreSQL 到底用什么手法去执行它们——这会为卷 V 的查询规划器埋下伏笔。