🏠 总目录📚 本教程 04 · NULL 与三值逻辑 ← →
📑 本页目录（点开跳转）

04 · NULL 与三值逻辑

⏱ 108 分钟 ｜ ⭐ 实测：NOT IN ('BJ', NULL) 返回空集——一行都没有，且不报错

🎯 一句话

NULL 不是一个值，是「不知道」；任何跟它比较的结果都不是真也不是假，而是第三种真值 UNKNOWN——而 WHERE 只放行判为真的行。 这一条是本教程里「不报错的错」的总源头：一条语法完全正确、跑得飞快、连警告都没有的查询，可以把整个结果集静静地清空。

⚠️ 零、先划清一件事：这不是「缺失值处理」

站内有一章叫《数据这一关》· 05 缺失不是一种东西，它讲的是统计学意义上的缺失：这个值为什么没有（MCAR / MAR / MNAR）、能不能填、填了会不会把偏差带进模型。

这一章讲的是完全不同的另一件事：值已经缺在库里了，NULL 这个东西在 SQL 里怎么参与运算和比较。

那一章问 这一章问
题目 这个值为什么没有？填成什么？ 已经是 NULL 了，WHERE／SUM／JOIN 会怎么对待它？
判据 缺失机制（MCAR/MAR/MNAR） 三值逻辑（TRUE/FALSE/UNKNOWN）
出错的样子 填错了，系数偏了 查询静默少了一批行，数据本身一个字没错

⚠️ 同名不同物，别混。 你可以在缺失机制上做得完全正确，仍然被本章这些坑打中——因为它们发生在你写 SQL 的那一刻，跟数据怎么来的没有半点关系。

⭐ 顺带一提，《数据这一关》· 04 脏数据的十种形态讲了一种更坏的东西：哨兵值（-999、"N/A"、1970-01-01）——它们不是 NULL，所以所有缺失率监控都是全绿的。 相比之下 NULL 至少是诚实的：它明说自己不知道。本章讲的是这份诚实要你付出的代价。

🚦 一、招牌事故：一个 NULL 清空整个结果集

一张 5 行的用户表，一张 2 行的「总部所在城市」表——其中一行的城市还没录：

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE users(id INTEGER PRIMARY KEY, city TEXT)")
db.executemany("INSERT INTO users VALUES(?,?)", [
    (1, "BJ"), (2, "SH"), (3, None), (4, "SH"), (5, "GZ"),
])
db.execute("CREATE TABLE hq(city TEXT)")
db.executemany("INSERT INTO hq VALUES(?)", [("BJ",), (None,)])   # ⭐ 混进了一行 NULL

print("NOT IN 字面量:",
      db.execute("SELECT id FROM users WHERE city NOT IN ('BJ', NULL)").fetchall())
print("NOT IN 子查询:",
      db.execute("SELECT id FROM users WHERE city NOT IN (SELECT city FROM hq)").fetchall())
print("NOT EXISTS:",
      db.execute("SELECT id FROM users u WHERE NOT EXISTS "
                 "(SELECT 1 FROM hq h WHERE h.city = u.city)").fetchall())
print("IN 子查询（对照）:",
      db.execute("SELECT id FROM users WHERE city IN (SELECT city FROM hq)").fetchall())

实跑输出：

NOT IN 字面量: []
NOT IN 子查询: []
NOT EXISTS: [(2,), (3,), (4,), (5,)]
IN 子查询（对照）: [(1,)]

💀 NOT IN 返回了空集。 没有异常，没有警告，sqlite3 高高兴兴地给你一个 []。 如果这条查询挂在一个「非总部城市的用户列表」报表上，你看到的就是「今天没有这样的用户」——而真实答案是四个。

⚠️ 注意最后一行的对照：IN 完全正常，返回了 (1,)。坏掉的只有加了 NOT 的那一半。 这就是它这么难发现的原因：你测 IN 的时候一切正常，NOT IN 是同一个写法反过来，谁会怀疑它？

⭐ 只要 NOT IN 的右边（字面量列表或子查询结果）里出现哪怕一个 NULL，整条 NOT IN 就永远判不出真，结果集必然为空。 为什么，看下一节。

🧩 二、三值逻辑：多出来的那个 UNKNOWN

普通编程语言里布尔值有两个：真、假。SQL 有三个。

NULL 的含义不是「空」也不是「零」，是「这里有个值，但我不知道它是多少」。 于是「不知道的那个值等于 5 吗」这个问题，唯一诚实的回答是：不知道。

import sqlite3

db = sqlite3.connect(":memory:")

def q(sql):
    v = db.execute("SELECT " + sql).fetchone()[0]
    return {None: "UNKNOWN", 1: "TRUE", 0: "FALSE"}[v]

for e in ["NULL = NULL", "NULL <> NULL", "NULL > 1", "NULL = 0",
          "NULL IS NULL", "NULL IS NOT NULL"]:
    print(f"{e:<18} -> {q(e)}")

print("--- AND / OR / NOT ---")
for a in ["1", "0", "NULL"]:
    for b in ["1", "0", "NULL"]:
        print(f"{a:>4} AND {b:<5} -> {q(f'{a} AND {b}'):<8}"
              f"{a:>4} OR {b:<5} -> {q(f'{a} OR {b}')}")
for a in ["1", "0", "NULL"]:
    print(f"NOT {a:<5} -> {q('NOT ' + a)}")

实跑输出：

NULL = NULL        -> UNKNOWN
NULL <> NULL       -> UNKNOWN
NULL > 1           -> UNKNOWN
NULL = 0           -> UNKNOWN
NULL IS NULL       -> TRUE
NULL IS NOT NULL   -> FALSE
--- AND / OR / NOT ---
   1 AND 1     -> TRUE       1 OR 1     -> TRUE
   1 AND 0     -> FALSE      1 OR 0     -> TRUE
   1 AND NULL  -> UNKNOWN    1 OR NULL  -> TRUE
   0 AND 1     -> FALSE      0 OR 1     -> TRUE
   0 AND 0     -> FALSE      0 OR 0     -> FALSE
   0 AND NULL  -> FALSE      0 OR NULL  -> UNKNOWN
NULL AND 1     -> UNKNOWN NULL OR 1     -> TRUE
NULL AND 0     -> FALSE   NULL OR 0     -> UNKNOWN
NULL AND NULL  -> UNKNOWN NULL OR NULL  -> UNKNOWN
NOT 1     -> FALSE
NOT 0     -> TRUE
NOT NULL  -> UNKNOWN

⭐ 第一行就是全章的地基：NULL = NULL 判出来是 UNKNOWN，不是真。 两个「不知道」凑在一起，仍然不知道它们是不是同一个东西。

⭐ 但 UNKNOWN 不会污染一切，看真值表里这两行：

式子 结果 为什么
0 AND NULL FALSE 一个连乘里有个零，另一项是几都不影响
1 OR NULL TRUE 已经有一项为真，另一项是几都不影响

⭐ 判据：只有当「那个未知值到底是多少」会改变答案时，结果才是 UNKNOWN。 这不是随便定的规则，是「不知道」这个语义的直接推论——把它当成推论去理解，比背九宫格可靠得多。

⚠️ 算术和字符串也一样被吞：1 + NULL、0 * NULL、'abc' || NULL、length(NULL)、upper(NULL) 实跑全部返回 None。 注意 0 * NULL 也是 NULL——数学上零乘任何数都是零，但 SQL 不做这个推理，⭐ 只有布尔运算享受上面那两条短路优待。

现在回头看第一节：city NOT IN ('BJ', NULL) 会被展开成

NOT (city = 'BJ' OR city = NULL)

对 city='SH' 这一行：('SH'='BJ') OR ('SH'=NULL) → FALSE OR UNKNOWN → UNKNOWN（正是上表第 6 行），再 NOT 一下还是 UNKNOWN。 每一行都是 UNKNOWN，一行都放不出来。

⭐ 而 IN 没事，因为它展开成 city='BJ' OR city=NULL——对 city='BJ' 那行是 TRUE OR UNKNOWN → TRUE（上表第 3 行），照样放行。 💀 NOT 把「有一项为真就够了」变成了「必须每一项都为假」，而「为假」正是 UNKNOWN 永远给不出的。

🧯 三、WHERE 只放行 TRUE——排中律在这里失效

01 章说「WHERE 逐行判断，扔掉不要的行」。⭐ 更准确的说法是：WHERE 只保留判为 TRUE 的行，FALSE 和 UNKNOWN 一起扔。

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE t(id INTEGER PRIMARY KEY, v INTEGER)")
db.executemany("INSERT INTO t VALUES(?,?)", [(1, 5), (2, None), (3, 20)])

a = db.execute("SELECT id FROM t WHERE v > 10").fetchall()
b = db.execute("SELECT id FROM t WHERE NOT (v > 10)").fetchall()
print("WHERE v > 10       :", a)
print("WHERE NOT (v > 10) :", b)
print("两边相加:", len(a) + len(b), "行 / 全表 3 行")
print("WHERE v > 10 OR NOT (v > 10):",
      db.execute("SELECT id FROM t WHERE v > 10 OR NOT (v > 10)").fetchall())
print("= NULL 写法 :", db.execute("SELECT id FROM t WHERE v = NULL").fetchall())
print("IS NULL 写法:", db.execute("SELECT id FROM t WHERE v IS NULL").fetchall())

实跑输出：

WHERE v > 10       : [(3,)]
WHERE NOT (v > 10) : [(1,)]
两边相加: 2 行 / 全表 3 行
WHERE v > 10 OR NOT (v > 10): [(1,), (3,)]
= NULL 写法 : []
IS NULL 写法: [(2,)]

💀💀 一个条件和它的否定，加起来不等于全表。 3 行的表，两边各拿走 1 行，有 1 行谁都没要。 连 WHERE v > 10 OR NOT (v > 10) 这种看上去必然恒真的条件，都漏掉了 id=2。

⚠️ 这条在业务上非常贵：「大额订单」和「非大额订单」两张报表加起来对不上总数，两边的 SQL 都是对的，两边的开发都查不出问题。

⭐ 判据：任何时候你把一个集合按条件劈成两半，如果那一列可能有 NULL，你就必须显式接管第三种情况（... OR v IS NULL）。

IS NULL 为什么不能写成 = NULL

看实跑：WHERE v = NULL 返回 []，WHERE v IS NULL 返回 [(2,)]。

因为 = 是比较运算，比较两个未知量得到 UNKNOWN（第二节第一行），WHERE 不放行。 而 IS NULL 根本不是比较，它是一个判定式：问的不是「这个值等于什么」，而是「这个格子里有没有值」——这个问题永远有明确答案，所以只会给出 TRUE 或 FALSE。

⭐ 整章最值得记住的一句： = 问「值是多少」，IS NULL 问「有没有值」。 前一个问题在没有值的时候无法回答，后一个永远能回答。

⚠️ 一个反直觉的例外：CHECK 约束放行 UNKNOWN

ON、HAVING 跟 WHERE 一样只认 TRUE，但约束是反过来的：

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE c(id INTEGER, age INTEGER CHECK (age >= 18))")
db.execute("INSERT INTO c VALUES(1, 30)")
for label, age in [("age=10", 10), ("age=NULL", None)]:
    try:
        db.execute("INSERT INTO c VALUES(?,?)", (99, age))
        print(f"  {label:<9} 插入成功")
    except Exception as e:
        print(f"  {label:<9} ->", type(e).__name__, ":", e)
print("  表里最终:", db.execute("SELECT id, age FROM c").fetchall())

实跑输出：

  age=10    -> IntegrityError : CHECK constraint failed: age >= 18
  age=NULL  插入成功
  表里最终: [(1, 30), (99, None)]

⭐ CHECK 的规则是「只在判为 FALSE 时拒绝」——UNKNOWN 放行。 💀 所以 CHECK (age >= 18) 挡不住 NULL。要真的挡住，得写 CHECK (age IS NOT NULL AND age >= 18)，或者给列加 NOT NULL。

⭐ 两条规则记成一对：查询要求「证明为真」，约束要求「证明为假」，UNKNOWN 在两边的待遇正好相反。

🩹 四、反连接的三种写法，只有一种不会被毒化

「A 表里、B 表里没有的那些」叫反连接。三种常见写法在有 NULL 时给出三个不同答案：

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE users(id INTEGER PRIMARY KEY, city TEXT)")
db.executemany("INSERT INTO users VALUES(?,?)", [
    (1, "BJ"), (2, "SH"), (3, None), (4, "SH"), (5, "GZ")])
db.execute("CREATE TABLE hq(city TEXT)")
db.executemany("INSERT INTO hq VALUES(?)", [("BJ",), (None,)])

print("A. NOT IN            :",
      db.execute("SELECT id FROM users WHERE city NOT IN (SELECT city FROM hq)").fetchall())
print("B. NOT EXISTS        :",
      db.execute("SELECT id FROM users u WHERE NOT EXISTS "
                 "(SELECT 1 FROM hq h WHERE h.city = u.city)").fetchall())
print("C. LEFT JOIN IS NULL :",
      db.execute("SELECT u.id FROM users u LEFT JOIN hq h ON h.city = u.city "
                 "WHERE h.city IS NULL").fetchall())
print("D. NOT IN + 内层过滤  :",
      db.execute("SELECT id FROM users WHERE city NOT IN "
                 "(SELECT city FROM hq WHERE city IS NOT NULL)").fetchall())
print("--- 子查询为空表时 ---")
db.execute("CREATE TABLE empty_hq(city TEXT)")
print("NOT IN 空表:",
      db.execute("SELECT id FROM users WHERE city NOT IN (SELECT city FROM empty_hq)").fetchall())

实跑输出：

A. NOT IN            : []
B. NOT EXISTS        : [(2,), (3,), (4,), (5,)]
C. LEFT JOIN IS NULL : [(2,), (3,), (4,), (5,)]
D. NOT IN + 内层过滤  : [(2,), (4,), (5,)]
--- 子查询为空表时 ---
NOT IN 空表: [(1,), (2,), (3,), (4,), (5,)]

三种写法 + 一个补救版，跑出三个不同答案。 逐个解释：

写法 结果 发生了什么
A NOT IN 空 💀 内层的 NULL 让每一行都判成 UNKNOWN
B NOT EXISTS 4 行 ⭐ 不被毒化：EXISTS 只问「有没有配上的行」，h.city = u.city 判 UNKNOWN 就是没配上，是个明确的「否」
C LEFT JOIN … IS NULL 4 行 和 B 一致（02 章第二节那个合法例外）
D NOT IN + 内层滤掉 NULL 3 行 内层干净了，但 ⚠️ 外层的 id=3 自己是 NULL，NULL NOT IN ('BJ') 仍是 UNKNOWN，被扔了

⭐ B 和 D 差的那一行（id=3）不是 bug，是两个不同的问题。 id=3 这个用户的城市未知——他到底在不在总部城市？NOT EXISTS 的口径是「配不上就算不在」，NOT IN 的口径是「不知道就不给结论」。 这是业务判断，你得自己决定要哪个，但你至少要知道自己选了哪个。

⭐ 默认结论：反连接一律写 NOT EXISTS。 它不需要你去操心内层有没有 NULL，而 NOT IN 需要你记得加 WHERE ... IS NOT NULL——而人是会忘的，尤其是在那一列今天还没有 NULL 的时候。

⚠️ 最后一个实跑值得单独看：子查询返回空表时，NOT IN 把 5 行全部放行（包括城市为 NULL 的 id=3）。 这是对的——「不在一个空集合里」对任何东西都成立。⭐ 但它意味着 NOT IN 的行为在「内层空」和「内层有一个 NULL」之间是从全放到全不放的跳变，而这两种情况在数据上只差一行。

⚠️ NOT IN 到底该不该完全禁用？ 不必——NOT IN 接一串写死的字面量（status NOT IN ('paid','refunded')）是安全常见的写法，只要那串字面量里没有 NULL。⭐ 危险的只有「右边是子查询、而那一列可空」这一种组合。

🛑 读到这里可以停 —— 前半章讲完了（约 42 分钟）。 后半章还有：那些 NULL 行，到底是在哪一步没的 · ⭐ SQL 里其实有两套 NULL 语义 · 日常工具：COALESCE / NULLIF / 空串不是 NULL 回来的时候不用重读，直接从下一节接着看就行。

🧮 五、那些 NULL 行，到底是在哪一步没的

⚠️ 先划界：聚合函数怎么对待 NULL（只有 COUNT(*) 数行、其余都数值，AVG 的分母是 COUNT(score) 不是 COUNT(*)，空集上 SUM 给 NULL 要用 COALESCE 兜底）——这一整套是 03 章的正题，那里讲得比这里细，本节不重讲。

本节只回答一个 NULL 语义的问题：一份数据里有一批行的键是 NULL，它们是在哪一步从你的看板上消失的？ ⭐ 答案很反直觉——不是 GROUP BY：

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE calls(id INTEGER PRIMARY KEY, city TEXT, cents REAL)")
db.executemany("INSERT INTO calls VALUES(?,?,?)", [
    (1, "BJ", 1.0), (2, "SH", 2.0), (3, None, 4.0), (4, "BJ", 8.0), (5, None, 16.0)])
db.execute("CREATE TABLE dim_city(code TEXT, name TEXT)")
db.executemany("INSERT INTO dim_city VALUES(?,?)", [("BJ", "北京"), ("SH", "上海")])

print("全表 SUM =", db.execute("SELECT SUM(cents) FROM calls").fetchone()[0])
print("GROUP BY :", db.execute("SELECT city, SUM(cents) FROM calls GROUP BY city").fetchall())
print("--- 换成下面任意一步，NULL 那 20.0 就没了（都不报错）---")
print("a) INNER JOIN 维表    :",
      db.execute("SELECT SUM(c.cents) FROM calls c JOIN dim_city d ON d.code=c.city").fetchone()[0])
print("b) WHERE city <> 'BJ' :",
      db.execute("SELECT SUM(cents) FROM calls WHERE city <> 'BJ'").fetchone()[0],
      "  <- 期望 2+4+16=22")
print("c) WHERE city IN (…)  :",
      db.execute("SELECT SUM(cents) FROM calls WHERE city IN ('BJ','SH')").fetchone()[0])
print("b 补上 NULL 之后      :",
      db.execute("SELECT SUM(cents) FROM calls WHERE city IS NULL OR city <> 'BJ'").fetchone()[0])

实跑输出：

全表 SUM = 31.0
GROUP BY : [(None, 20.0), ('BJ', 9.0), ('SH', 2.0)]
--- 换成下面任意一步，NULL 那 20.0 就没了（都不报错）---
a) INNER JOIN 维表    : 11.0
b) WHERE city <> 'BJ' : 2.0   <- 期望 2+4+16=22
c) WHERE city IN (…)  : 11.0
b 补上 NULL 之后      : 22.0

⭐ GROUP BY 一分钱没丢：三组加起来正好 31.0，NULL 老老实实自成一组，占 20.0——接近全表的三分之二。 （GROUP BY 把所有 NULL 收进同一组，用的是本章第六节要讲的「另一套语义」。）

💀 丢在后面三步，而且都不报错：

那一步 结果 为什么
a 连维表（INNER JOIN） 31.0 → 11.0 连接键 NULL 配不上对（02 章第六节），那两行被内连接扔了
b WHERE city <> 'BJ' 应是 22.0，实得 2.0 ⚠️ NULL <> 'BJ' 判 UNKNOWN，WHERE 不放行——一个「不等于」吞掉 20.0
c WHERE city IN (…) 11.0 白名单同理，NULL 不在任何名单里

⚠️ b 这一格最值得盯：写的人想表达「除了北京的都算上」，实际算出来的是「城市已知且不是北京的」——少了 20.0，占全表 64.5%。 补一句 city IS NULL OR 就回到 22.0。

⭐ 判据：凡是写 <>、NOT IN、NOT LIKE 这类「否定式过滤」，先问一句「这一列可空吗」。 肯定式过滤（=、IN、LIKE）不需要问，因为 NULL 本来也不该被它选中； 否定式过滤则默认把「未知」也一起排除掉了，而这几乎从来不是你的本意。

🔀 六、⭐ SQL 里其实有两套 NULL 语义

第二节刚说 NULL = NULL 判不出真。但下面这些地方，NULL 又被当成能和自己配上的一个值：

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE u(id INTEGER PRIMARY KEY, city TEXT)")
db.executemany("INSERT INTO u VALUES(?,?)", [(1, "BJ"), (2, None), (3, None), (4, "SH")])

print("GROUP BY :", db.execute("SELECT city, COUNT(*) FROM u GROUP BY city").fetchall())
print("DISTINCT :", db.execute("SELECT DISTINCT city FROM u").fetchall())
print("UNION    :", db.execute("SELECT city FROM u UNION SELECT city FROM u").fetchall())
print("NULL IS NULL / 1 IS NULL:", db.execute("SELECT (NULL IS NULL), (1 IS NULL)").fetchone())

db.execute("CREATE TABLE uq(id INTEGER PRIMARY KEY, email TEXT UNIQUE)")
db.execute("INSERT INTO uq VALUES(1, 'a@x.com')")
try:
    db.execute("INSERT INTO uq VALUES(2, 'a@x.com')")
except Exception as ex:
    print("重复字符串 ->", type(ex).__name__, ":", ex)
db.executemany("INSERT INTO uq VALUES(?,?)", [(3, None), (4, None), (5, None)])
print("UNIQUE 列里的三个 NULL:", db.execute("SELECT id, email FROM uq").fetchall())

实跑输出：

GROUP BY : [(None, 2), ('BJ', 1), ('SH', 1)]
DISTINCT : [('BJ',), (None,), ('SH',)]
UNION    : [(None,), ('BJ',), ('SH',)]
NULL IS NULL / 1 IS NULL: (1, 0)
重复字符串 -> IntegrityError : UNIQUE constraint failed: uq.email
UNIQUE 列里的三个 NULL: [(1, 'a@x.com'), (3, None), (4, None), (5, None)]

⭐ 两套语义，记住这张表就够了：

用「不知道」语义（NULL 配不上自己） 用「同一个值」语义（NULL 配得上自己）
WHERE / ON / HAVING 里的 =、<>、IN GROUP BY（所有 NULL 一组）
连接键的匹配（02 章第六节） DISTINCT、UNION、INTERSECT、EXCEPT
CHECK 约束（判 FALSE 才拒） ORDER BY（NULL 之间视为并列）
IS / IS NOT（判定式，不是比较）

💀 UNIQUE 是最贵的一格：实跑里同一个 email 插第二遍被拒，但三行 NULL 全部插入成功。 ⚠️ 一个「邮箱唯一」的约束完全挡不住三个没填邮箱的账号——它用的是比较语义，NULL = NULL 判不出真，也就谈不上重复。 ⭐ 真想唯一，那一列得同时是 NOT NULL。（🗓️ 未实跑：Postgres 15+ 提供 UNIQUE NULLS NOT DISTINCT 改变这一行为，SQLite 无此语法。）

⭐ 想要一个「NULL 安全的等号」：SQLite 用 IS（实跑：NULL IS NULL → 1、1 IS NULL → 0），Postgres 用 IS NOT DISTINCT FROM——02 章第六节讲连接键时提过，那里是它最常见的用武之地。

ORDER BY 里 NULL 排哪儿：三个库不一样

import sqlite3

print("sqlite version:", sqlite3.sqlite_version)
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE t(id INTEGER PRIMARY KEY, v INTEGER)")
db.executemany("INSERT INTO t VALUES(?,?)", [(1, 30), (2, None), (3, 10), (4, None), (5, 20)])

print("ORDER BY v          :", db.execute("SELECT id, v FROM t ORDER BY v").fetchall())
print("ORDER BY v DESC     :", db.execute("SELECT id, v FROM t ORDER BY v DESC").fetchall())
print("ORDER BY (v IS NULL), v :",
      db.execute("SELECT id, v FROM t ORDER BY (v IS NULL), v").fetchall())
print("MIN(v)              =", db.execute("SELECT MIN(v) FROM t").fetchone()[0])
print("ORDER BY v LIMIT 1  =", db.execute("SELECT v FROM t ORDER BY v LIMIT 1").fetchone()[0])

实跑输出（SQLite 3.50.4）：

sqlite version: 3.50.4
ORDER BY v          : [(2, None), (4, None), (3, 10), (5, 20), (1, 30)]
ORDER BY v DESC     : [(1, 30), (5, 20), (3, 10), (2, None), (4, None)]
ORDER BY (v IS NULL), v : [(3, 10), (5, 20), (1, 30), (2, None), (4, None)]
MIN(v)              = 10
ORDER BY v LIMIT 1  = None

💀 MIN(v) 是 10，ORDER BY v LIMIT 1 是 NULL。 两句在英语里都念作「最小的那个」，SQLite 上给出不同答案——因为 MIN 跳过 NULL（聚合的规则，03 章），而 ORDER BY 把它排在最前（排序的规则，上面那张表）。⭐ 同一个 NULL，两套语义，两个答案。

库 ORDER BY x ASC 里 NULL 在 支持 NULLS FIRST/LAST 验证情况
SQLite 最前（视为最小） ✅ 3.30+ ⭐ 实跑于 3.50.4
PostgreSQL 最后（视为最大） ✅ 🗓️ 未实跑
MySQL 最前（视为最小） ❌ 无此语法 🗓️ 未实跑

⚠️ 同一条「取最新的一条」查询，从 SQLite 搬到 Postgres 上，ORDER BY updated_at DESC LIMIT 1 取到的可能是完全不同的一行——SQLite 上 DESC 把 NULL 排在最后，Postgres 上排在最前。

⭐ 可移植的写法：ORDER BY (v IS NULL), v（实跑把 NULL 稳定压到末尾，把 IS NULL 换成 IS NOT NULL 就置顶）。三个库都支持，不依赖任何一家的默认值。

🛑 第二个休息点 —— 中段讲完了（约 29 分钟）。 最后一段还有：日常工具：COALESCE / NULLIF / 空串不是 NULL 这一章确实长，分三次读完全没问题 —— 回来直接从下一节接着看。

🛑 读到这里可以停 —— 已经读了约 71 分钟。 最后一段还有（约 33 分钟）：日常工具：COALESCE / NULLIF / 空串不是 NULL · 检查点与走神救援 回来的时候不用重读，直接从下一节接着看就行。

🧰 七、日常工具：COALESCE / NULLIF / 空串不是 NULL

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE p(id INTEGER PRIMARY KEY, nick TEXT, name TEXT, note TEXT)")
db.executemany("INSERT INTO p VALUES(?,?,?,?)", [
    (1, "小明", "王明", "hi"),
    (2, None,   "李雷", ""),      # note 是空串
    (3, "",     "韩梅", None),    # nick 是空串
    (4, None,   None,   None),
])
print("COALESCE          :", db.execute("SELECT id, COALESCE(nick, name, '(匿名)') FROM p").fetchall())
print("套一层 NULLIF     :",
      db.execute("SELECT id, COALESCE(NULLIF(nick,''), name, '(匿名)') FROM p").fetchall())
print("note = ''         :", db.execute("SELECT id FROM p WHERE note = ''").fetchall())
print("note IS NULL      :", db.execute("SELECT id FROM p WHERE note IS NULL").fetchall())
print("COUNT(note)       :", db.execute("SELECT COUNT(note) FROM p").fetchone()[0])
print("length('') / length(NULL):", db.execute("SELECT length(''), length(NULL)").fetchone())
print("拼接被吞          :", db.execute("SELECT id, '你好, ' || nick FROM p").fetchall())
print("NULLIF 防除零     :", db.execute("SELECT NULLIF(5,5), 10 / NULLIF(0,0)").fetchone())

实跑输出：

COALESCE          : [(1, '小明'), (2, '李雷'), (3, ''), (4, '(匿名)')]
套一层 NULLIF     : [(1, '小明'), (2, '李雷'), (3, '韩梅'), (4, '(匿名)')]
note = ''         : [(2,)]
note IS NULL      : [(3,), (4,)]
COUNT(note)       : 2
length('') / length(NULL): (0, None)
拼接被吞          : [(1, '你好, 小明'), (2, None), (3, '你好, '), (4, None)]
NULLIF 防除零     : (None, None)

⚠️ 先看「拼接被吞」那行：id=2 和 id=4 的 nick 是 NULL，'你好, ' || NULL 整体变成 NULL（第二节那条「字符串也被吞」）——你想拼一句问候语，拿到的是一个空值。 而 id=3 的 nick 是空串，拼出来是 '你好, '：不是 NULL，只是少了个名字。⭐ 这一行输出里同时藏着本节要讲的两件事。

函数 做什么 典型用途
COALESCE(a, b, c) 返回第一个非 NULL 的参数 兜底默认值；⭐ 三个库通用，优先于 SQLite 的 IFNULL / MySQL 的 IFNULL / Oracle 的 NVL
NULLIF(a, b) a = b 时返回 NULL，否则返回 a ⭐ 防除零（x / NULLIF(y, 0)）；把哨兵值转回 NULL（NULLIF(age, -999)）

💀 空串 '' 不是 NULL，它是一个长度为 0 的真实字符串。 实跑三处都在证明这件事： note IS NULL 只找到 id=3,4，note = '' 找到的是 id=2；COUNT(note) 是 2（空串被数进去了）；length('') 是 0 而 length(NULL) 是 NULL。

⚠️ 所以 COALESCE(nick, name, '(匿名)') 对 id=3 返回空串——它的 nick 是空串不是 NULL，COALESCE 认这个值。 ⭐ 修法是套一层 NULLIF：COALESCE(NULLIF(nick,''), name, '(匿名)')，实跑正确返回 韩梅。

⭐ 判据：在写入端就定一个规矩——一列里要么统一存 NULL，要么统一存空串，别两种都有。 一个前端没填就提交空串的表单，会让表里同时存在两种「没有」，而所有 IS NULL 检查只看得见其中一种。 （🗓️ 未实跑：Oracle 是唯一把空串当作 NULL 的主流库，本机无该环境。）

🔗 这一章连到哪里

相关的地方 为什么
01-一条查询是怎么跑的.html 那一章说「WHERE 逐行判断，扔掉不要的行」——本章第三节给出精确版本：只放行 TRUE，FALSE 和 UNKNOWN 一起扔，于是一个条件和它的否定加起来不等于全表
02-JOIN的几种语义.html 那一章有两处欠着本章一个解释：LEFT JOIN 补出来的 NULL 为什么会让 WHERE 把整行扔掉，以及连接键是 NULL 时为什么两边都 NULL 也配不上对
03-聚合与分组.html ⭐ 聚合函数怎么对待 NULL 全部归它（COUNT(*) 数行、其余数值，AVG 的分母，空集上 SUM 给 NULL）。本章第五节接着往下问一步：那些 NULL 行是在哪一步从看板上消失的
05-子查询的三种形状.html 本章第四节说「反连接一律写 NOT EXISTS」——IN / EXISTS / JOIN 三种形状还有性能和可读性上的取舍，那一章接着讲
10-拿SQL审问一份数据.html ⭐ 那一章把 COUNT(*) - COUNT(city) 当探针用来量一份数据脏在哪；本章解释这个探针为什么成立，以及它那条白名单断言的 NULL 陷阱
数据这一关 · 05 缺失不是一种东西 ⚠️ 同名不同物：那一章讲缺失的成因（MCAR/MAR/MNAR）和该不该填；本章讲已经是 NULL 之后 SQL 怎么对待它。见本章第零节
数据这一关 · 04 脏数据的十种形态 比 NULL 更坏的是哨兵值（-999、"N/A"）——它们不是 NULL，缺失率监控全绿。本章第七节的 NULLIF(age, -999) 就是把它们转回 NULL 的那一步

✅ 检查点

  1. 本章和《数据这一关》05 章都在讲「值没有」，两者的题目分别是什么？
  2. NULL = NULL 和 NULL IS NULL 判出来分别是什么，为什么不同？另外 0 AND NULL 和 1 OR NULL 是什么，这两条为什么不算例外？
  3. 把 city NOT IN ('BJ', NULL) 展开成 AND/OR 的形式，说明它为什么对每一行都判不出真。为什么 IN 没事？
  4. 实跑那张 3 行表上，WHERE v > 10 和 WHERE NOT (v > 10) 各返回几行？加起来是几？说明了什么？
  5. CHECK (age >= 18) 挡得住 age = NULL 吗？查询和约束对 UNKNOWN 的处理有什么区别？
  6. 反连接的四种写法（NOT IN / NOT EXISTS / LEFT JOIN … IS NULL / NOT IN + 内层过滤）在实跑里各返回几行？B 和 D 差的那一行差在哪？
  7. GROUP BY city 会丢掉 city 为 NULL 的行吗？那 31.0 变成 11.0 和 2.0 分别是在哪一步发生的？由此得到的判据是什么？
  8. 列出至少三个「NULL 被当成能和自己配上的同一个值」的地方。UNIQUE 属于哪一套语义，后果是什么？再说明 SQLite 上 MIN(v) 和 ORDER BY v LIMIT 1 为什么给出不同答案、可移植的排序写法是什么。
  9. COALESCE(nick, name, '(匿名)') 对一个 nick 是空串的行返回什么？怎么修？
👀 答案
  1. 《数据这一关》05 讲统计学意义上的缺失：这个值为什么没有（MCAR / MAR / MNAR）、能不能填、填了会不会把偏差带进模型。 本章讲 SQL 的 NULL 语义：值已经缺在库里了，它怎么参与比较和运算。⚠️ 同名不同物——缺失机制处理得再对，照样会被三值逻辑打中。
  2. NULL = NULL → UNKNOWN；NULL IS NULL → TRUE。因为 = 是比较运算（问「值是多少」，两个未知量比不出结果），IS NULL 是判定式（问「有没有值」，这个问题永远有明确答案）。 0 AND NULL → FALSE，1 OR NULL → TRUE。不是例外，是「不知道」这个语义的直接推论：只有当那个未知值到底是多少会改变答案时，结果才是 UNKNOWN——这两种情况下它改变不了答案。 ⚠️ 但算术不享受这个待遇：0 * NULL 实跑仍是 NULL。
  3. 展开成 NOT (city = 'BJ' OR city = NULL)。对 city='SH'：FALSE OR UNKNOWN → UNKNOWN，NOT UNKNOWN 还是 UNKNOWN，WHERE 不放行；每一行都如此，所以返回 []。 IN 没事是因为它是 city='BJ' OR city=NULL，对 city='BJ' 那行是 TRUE OR UNKNOWN → TRUE。 💀 NOT 把「有一项为真就够了」变成了「必须每一项都为假」，而「为假」正是 UNKNOWN 给不出的。
  4. 各 1 行（[(3,)] 和 [(1,)]），加起来 2 行，而全表 3 行。 💀 一个条件和它的否定加起来不等于全表——id=2（v 是 NULL）谁都没要。连 WHERE v > 10 OR NOT (v > 10) 都漏掉它。
  5. 挡不住——实跑 age=NULL 插入成功，而 age=10 被 IntegrityError : CHECK constraint failed: age >= 18 拒绝。 查询要求「证明为真」，约束要求「证明为假」：WHERE/ON/HAVING 只放行 TRUE，CHECK 只在判为 FALSE 时拒绝，UNKNOWN 在两边待遇正好相反。要挡住得写 CHECK (age IS NOT NULL AND age >= 18) 或给列加 NOT NULL。
  6. A NOT IN → 0 行（[]）；B NOT EXISTS → 4 行 (2,3,4,5)；C LEFT JOIN … IS NULL → 4 行（同 B）；D NOT IN + 内层过滤 → 3 行 (2,4,5)。 B 和 D 差在 id=3，它自己的 city 是 NULL：NOT EXISTS 的口径是「配不上就算不在」，NOT IN 的口径是「不知道就不给结论」。这是业务判断，不是 bug，但你得知道自己选了哪个。默认写 NOT EXISTS。
  7. 不会丢——实跑 GROUP BY city 给出三组 [(None, 20.0), ('BJ', 9.0), ('SH', 2.0)]，加起来正好 31.0，NULL 自成一组占 20.0（接近全表三分之二）。 丢在后面：a 连维表（INNER JOIN） → 11.0（连接键 NULL 配不上对）；b WHERE city <> 'BJ' → 2.0 而正确答案是 22.0（⚠️ NULL <> 'BJ' 判 UNKNOWN，一个「不等于」吞掉 20.0，占全表 64.5%）；c WHERE city IN (…) → 11.0。补 city IS NULL OR 后回到 22.0。 判据：写 <>、NOT IN、NOT LIKE 这类否定式过滤前，先问「这一列可空吗」——肯定式过滤不用问，否定式过滤默认把「未知」也一起排除了。
  8. GROUP BY（所有 NULL 一组）、DISTINCT、UNION/INTERSECT/EXCEPT、ORDER BY（NULL 之间视为并列）、IS/IS NOT。 ⚠️ UNIQUE 属于另一套（比较语义）：实跑里重复的 'a@x.com' 被拒，但三行 NULL 全部插入成功——「邮箱唯一」的约束完全挡不住三个没填邮箱的账号。要真唯一，那列得同时 NOT NULL。 排序上 MIN(v) = 10（聚合跳过 NULL），ORDER BY v LIMIT 1 = NULL（SQLite 把 NULL 视为最小排在最前）；而 Postgres 视 NULL 为最大排在最后，MySQL 同 SQLite 且不支持 NULLS FIRST/LAST——同一条「取最新一条」跨库搬会取到不同的行。 可移植写法：ORDER BY (v IS NULL), v（垫底）或 ORDER BY (v IS NOT NULL), v（置顶）。
  9. 返回空串——因为 id=3 的 nick 是空串不是 NULL，COALESCE 认这个值（空串是长度为 0 的真实字符串：length('') 是 0，length(NULL) 是 NULL，COUNT(note) 把空串数了进去所以是 2）。 修法：套一层 NULLIF —— COALESCE(NULLIF(nick,''), name, '(匿名)')，实跑返回 韩梅。

🛑 可以停在这里

⚡ 走神救援

⭐ NULL 不是值，是「不知道」，所以 SQL 的布尔值有三个：TRUE / FALSE / UNKNOWN。

地基是 NULL = NULL 判出来是 UNKNOWN 而不是真——⭐ 因为 = 问「值是多少」，IS NULL 问「有没有值」。UNKNOWN 不污染一切：判据是「只有当那个未知值会改变答案时，结果才是 UNKNOWN」。但算术不享受这个优待，任何数乘 NULL、任何串接 NULL 都是 NULL。

💀 招牌事故：WHERE city NOT IN (SELECT …) 在子查询里混进一个 NULL 之后返回空集、一行都没有、而且不报错；⚠️ 同时不带 NOT 的 IN 完全正常——坏掉的只有加了 NOT 的那一半。

⭐ WHERE 只放行 TRUE，FALSE 和 UNKNOWN 一起扔。 所以一个条件和它的否定加起来不等于全表。⚠️ 约束反过来：CHECK 只在判 FALSE 时拒绝，于是它会放行 NULL——查询要求证明为真，约束要求证明为假。

反连接四种写法实测给出三个不同答案，⭐ 默认一律写 NOT EXISTS，它不会被内层的 NULL 毒化。

⚠️ 而「那些 NULL 行是在哪一步从看板上没的」，答案不是 GROUP BY——NULL 会老老实实自成一组。真正丢在后面三步，而且都不报错：INNER JOIN 维表（连接键 NULL 配不上对）、<> 过滤、IN (…) 过滤。💀 实测里一个「不等于」就吞掉了近三分之二的金额。

下一节 👉 05-子查询的三种形状.md

打卡记录保存在你的浏览器里，首页能看到总进度