📑 本页目录(点开跳转)
04 · NULL 与三值逻辑
实验记录怎么读
本页原有耗时、内存、报错和环境值保留为原文记录;本轮内容审校未逐项复跑这些实验。版本、硬件、数据和测量方法都会影响结果,示例中的倍数也不是保证。需要复现时先核对依赖版本,再在同一输入下比较。
⏱ 108 分钟 | ⭐ 实测:NOT IN ('BJ', NULL) 返回空集——一行都没有,且不报错
🎯 一句话
报表想找不在总部名单里的用户,查询却一行也没返回。先看名单里有没有 NULL,也就是 SQL 的缺值标记。普通等号遇到 NULL 会得到未知(UNKNOWN)。WHERE 只留下条件为真的行。
先回忆 01 章的查询顺序、02 章的连接和 03 章的分组。本章用小表定位缺值影响了哪一步。
⚠️ 零、先划清一件事:这不是「缺失值处理」
站内有一章叫《数据这一关》· 05 缺失不是一种东西,它讲的是统计学意义上的缺失:这个值为什么没有(完全随机缺失 MCAR、随机缺失 MAR、非随机缺失 MNAR)、能不能填、填了会不会把偏差带进模型。
这一章讲的是完全不同的另一件事:值已经缺在库里了,NULL 这个东西在 SQL 里怎么参与运算和比较。
| 比较项 | 缺失机制 | SQL 缺值 |
|---|---|---|
| 题目 | 这个值为什么没有?填成什么? | 已经是 NULL 了,WHERE/SUM/JOIN 会怎么对待它? |
| 判据 | 缺失机制(MCAR/MAR/MNAR) | 三值逻辑(TRUE/FALSE/UNKNOWN) |
| 出错的样子 | 填错了,系数偏了 | 查询静默少了一批行,数据本身一个字没错 |
⚠️ 同名不同物,别混。 你可以在缺失机制上做得完全正确,仍然被本章这些坑打中——因为它们发生在你写 SQL 的那一刻,跟数据怎么来的没有半点关系。
哨兵值(sentinel value)是业务约定用来表示特殊状态的普通值。例如系统约定年龄
-999表示未填,数据库里存的仍是一个数,IS NULL查不到它。《数据这一关》· 04 脏数据的十种形态还有文本"N/A"和占位日期等例子。
🚦 一、招牌事故:一个 NULL 清空整个结果集
users 记录用户编号 id 和城市 city。hq 是总部城市名单,已录入北京(BJ),还有一行待补。试着找出未匹配到已录入城市的用户。直接写在 SQL 里的常量叫字面量,例如名单 ('BJ', NULL) 里的文本 'BJ' 和缺值标记 NULL。
| 用户编号 | 城市 |
|---|---|
| 1 | BJ |
| 2 | SH |
| 3 | NULL |
| 4 | SH |
| 5 | GZ |
IN 判断左侧值是否在名单中,NOT IN 取反。先用城市已知的用户 2 做一次存在性检查,再看缺值怎样影响两个判断。
在 FROM users u 和 FROM hq h 中,u、h 是表的别名(alias),用来指明 city 来自哪张表。括号里的查询叫子查询(subquery)。这里的内层用 h.city = u.city 引用外层当前行,叫相关子查询(correlated subquery)。
u.city 是 SH。'BJ' = 'SH' 为假,NULL = 'SH' 为未知。内层 WHERE 留下 0 行。EXISTS 为假,NOT EXISTS 为真,用户 2 留在外层结果。每条满足内层 WHERE 的总部记录都会产生一行,SELECT 1 给这一行一个占位值。用户 1 只匹配到北京这一条,所以内层返回一行。EXISTS 只判断有没有行。第 05 章会继续讲子查询。
下面每段 Python 都能独立运行。标准库 sqlite3 在内存中建临时数据库,无需安装第三方库或准备数据文件。Python 的 None 写入 SQLite 就是 NULL,读出时也显示为 None。
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,)]
输出 [(1,)] 表示一行,只有编号一列。两条 NOT IN 分别用字面量和子查询得到同一份名单,所以都返回 [](零行)。没有 ORDER BY 时,只核对返回了哪些行。
IN 找到用户 1。NOT EXISTS 返回 2、3、4、5,这四位都没有找到城市相等的总部记录。城市待补的用户 3 应不应该计入报表,留到第四节按口径决定。
🧩 二、三值逻辑:多出来的那个 UNKNOWN
系统只记录了用户 3 的编号。现在问“用户 3 的城市是 BJ 吗”,已有信息无法确定。NULL 标记缺少可用的值,具体原因由业务定义,可以是尚未录入或不适用。三值逻辑(Three-Valued Logic)给条件表达式三种结果:成立(TRUE)、不成立(FALSE)、无法判定(UNKNOWN)。例如 'BJ' = 'BJ' 为真,'SH' = 'BJ' 为假,NULL = 'BJ' 为未知。
设 A、B 是两个条件。AND 要求两者都成立,OR 要求至少一个成立,NOT 把真与假互换。如果已经有一个条件为假,AND 就是假;如果已经有一个条件为真,OR 就是真。 对未知取反仍是未知。表格中的 T、F、U 分别表示 TRUE、FALSE、UNKNOWN。
| A | B | A AND B | A OR B |
|---|---|---|---|
| T | T | T | T |
| T | F | F | T |
| T | U | U | T |
| F | T | F | T |
| F | F | F | F |
| F | U | F | U |
| U | T | U | T |
| U | F | F | U |
| U | U | U | U |
NOT T 得 F,NOT F 得 T,NOT U 得 U。例如条件 A 是“编号等于 3”,条件 B 是“城市等于 BJ”。用户 3 的 A 为 T、B 为 U,所以 A AND B 仍为 U,A OR B 已经为 T。这些规则描述最终真值。数据库何时求值属于执行细节。
运行下例时,q 把每个条件接在 SELECT 后,直接查看条件结果。SQLite 在这里返回 1、0 或 NULL,字典把 Python 收到的 1、0、None 换成三个真值的名字。
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
回到用户名单。在这个 SQLite 例子里,城市是文本,右侧是字面量 'BJ' 和 NULL。可以用 city = 'BJ' OR city = NULL 算 IN,再取反算 NOT IN。
| 用户与城市 | 两次比较 | 真值与筛选 |
|---|---|---|
| 1 · BJ | 等于 BJ → T 等于 NULL → U | IN → TNOT IN → F,排除 |
| 2 · SH | 等于 BJ → F 等于 NULL → U | IN → UNOT IN → U,排除 |
| 3 · NULL | 等于 BJ → U 等于 NULL → U | IN → UNOT IN → U,排除 |
| 4 · SH | 等于 BJ → F 等于 NULL → U | IN → UNOT IN → U,排除 |
| 5 · GZ | 等于 BJ → F 等于 NULL → U | IN → UNOT IN → U,排除 |
用户 1 的 TRUE OR UNKNOWN 为真,取反为假。用户 2 的 FALSE OR UNKNOWN 为未知,取反仍为未知。五行的 NOT IN 都没得到真,单独作为 WHERE 条件时返回零行。
混合类型时,能照样改写吗?
SQLite 的列亲和性(type affinity)是列倾向采用的数据类型,例如文本或数字。两列用等号比较时,亲和性可以影响比较前的转换。IN 右侧写成括号里的列表时,SQLite 视列表项为没有亲和性,即使该项引用了一列。
设 x 是 TEXT 列中的文本 '01',y 是 NUMERIC 列中的数字 1。对照两种计算:
x NOT IN (y, NULL)。列表项y没有亲和性,第一项按文本比较。'01'与'1'不同,第二项与NULL比较为未知,取反后仍未知。NOT (x = y OR x = NULL)。裸等号比较中,y的数字亲和性把'01'转成数字 1。第一项为真,整个OR为真,取反得到假。
手算前先确认比较时的类型转换一致。SQLite 比较前的类型转换规则
SQLite 中 1 + NULL、0 * NULL、'abc' || NULL、length(NULL) 和 upper(NULL) 都返回 NULL。聚合函数和其他数据库的字符串函数需要按各自规则判断。
🧯 三、WHERE 筛行、ON 配对、HAVING 筛组
WHERE 逐行判断,只保留条件为 TRUE 的行。假设 v 是订单金额,单位为元,报表要找超过 10 元的订单。金额待补的订单在原条件与取反条件下都得不到确定的答案。
| 订单金额 | 门槛与取反 | 缺值判断 |
|---|---|---|
| 1 · 5 元 | v > 10 → FNOT (v > 10) → T | v = NULL → Uv IS NULL → F |
| 2 · NULL | v > 10 → UNOT (v > 10) → U | v = NULL → Uv IS NULL → T |
| 3 · 20 元 | v > 10 → TNOT (v > 10) → F | v = NULL → Uv IS NULL → F |
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,)]
设 P 表示一整条筛选条件,本例中是 v > 10。只有当 P 为 UNKNOWN 时,WHERE P 和 WHERE NOT (P) 才会同时排除这一行。P 为真时,第一条留下该行。P 为假时,第二条留下该行。本例只有 id=2 在两边都被排除。
本例超过 10 元和不超过 10 元各一笔。第三笔金额待补,可以单列一组;若口径要求把它计入第二组,就用 WHERE NOT (v > 10) OR v IS NULL。
IS NULL 为什么不能写成 = NULL
v = NULL 用普通等号比较,右侧始终缺值,因此每行都得到 UNKNOWN。v IS NULL 检查这一行的 v 是否为 NULL,结果只有真或假,只有编号 2 为真。IS NOT NULL 检查是否有值,保留编号 1、3。
ON 决定配对,HAVING 决定留组
沿用 20 元、5 元、金额待补三种情况。账户表 accounts 有编号 1、2、3,付款表 payments 各有一笔付款,付款编号分别为 11、22、33。按账户编号相等找候选配对。每个账户只有一个编号相等的候选,其余候选因编号不等而无法通过 AND。
ON 为真才建立配对。LEFT JOIN(左连接)会为没有配对的左行补一行,右侧列填 NULL。后续 WHERE 仍检查这些补行,只保留整个条件为真的行。
| 账户与金额 | 左连接的配对与输出 | 原付款表分组 |
|---|---|---|
| 1 · 20 元 | ON → T付款编号 11 | 合计 20 元HAVING → T,保留 |
| 2 · 5 元 | ON → F补出 NULL | 合计 5 元HAVING → F,排除 |
| 3 · NULL | ON → U补出 NULL | 合计 NULLHAVING → U,排除 |
用原始付款表按账户分组。SUM(amount) 对非 NULL 金额求和。三组的合计分别是 20、5、NULL,因为第三组没有可加的数。HAVING SUM(amount) > 10 只让账户 1 的组通过。这里的 HAVING 与 WHERE 使用相同的真值门槛,判断的单位分别是组和行。
import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE accounts(id INTEGER PRIMARY KEY);
INSERT INTO accounts VALUES (1), (2), (3);
CREATE TABLE payments(id INTEGER PRIMARY KEY, account_id INTEGER, amount INTEGER);
INSERT INTO payments VALUES (11, 1, 20), (22, 2, 5), (33, 3, NULL);
""")
join = """SELECT a.id, p.id
FROM accounts a LEFT JOIN payments p ON p.account_id = a.id"""
print("ON 筛配对:", db.execute(join + " AND p.amount > 10 ORDER BY a.id").fetchall())
print("WHERE 筛行:", db.execute(join + " WHERE p.amount > 10 ORDER BY a.id").fetchall())
groups = "SELECT account_id, SUM(amount) FROM payments GROUP BY account_id"
print("所有组:", db.execute(groups + " ORDER BY account_id").fetchall())
print("HAVING 筛组:", db.execute(groups + " HAVING SUM(amount) > 10 ORDER BY account_id").fetchall())
运行结果(Python 用 None 显示 SQL 的 NULL)。
ON 筛配对: [(1, 11), (2, None), (3, None)]
WHERE 筛行: [(1, 11)]
所有组: [(1, 20), (2, 5), (3, None)]
HAVING 筛组: [(1, 20)]
前两行结果中的每对数字依次是账户编号、付款编号。后两行的第二列是组内金额合计。账户 3 的付款 33 确实存在,但 ON 里的金额条件为未知,输出中的右侧编号因此是补出来的 NULL。
例如补行的 p.amount > 10 为未知。如果金额门槛留在 ON,后续改用 WHERE a.id = 3,账户 3 的补行符合这个条件,输出仍有 (3, NULL)。
⚠️ 一个反直觉的例外:CHECK 约束放行 UNKNOWN
检查约束(CHECK)在写入时检查数据是否符合规则。例如年龄已填写时要求至少 18 岁。30 岁通过,10 岁判为假而被拒,年龄待补时条件为未知。
如果年龄必填,用非空约束(NOT NULL)拒绝缺值。下面在第二张表里把两条约束一起声明,再尝试插入缺值。
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())
db.execute("CREATE TABLE required_age(id INTEGER, age INTEGER NOT NULL CHECK (age >= 18))")
try:
db.execute("INSERT INTO required_age VALUES(99, NULL)")
except sqlite3.IntegrityError as e:
print(" 加 NOT NULL:", type(e).__name__, ":", e)
实跑输出:
age=10 -> IntegrityError : CHECK constraint failed: age >= 18
age=NULL 插入成功
表里最终: [(1, 30), (99, None)]
加 NOT NULL: IntegrityError : NOT NULL constraint failed: required_age.age
CHECK 遇到 FALSE 才拒绝,所以第一张表保留了年龄缺值的记录。第二张表的 NOT NULL 拒绝了同一个缺值。也可把缺值检查写进一个条件 CHECK (age IS NOT NULL AND age >= 18),此时缺值使整个条件为假。
按使用位置判断结果:WHERE 与 HAVING 要求真才保留行或组,ON 要求真才配对,CHECK 遇到假才拒绝写入。
🩹 四、反连接:找不到匹配行时留谁
找左表中没有匹配右行的记录,称为反连接(anti-join)。沿用第一节的 users 与 hq。先确定报表要保留所有找不到匹配的人,还是只保留城市已知且找不到匹配的人。
回到 h.city = u.city。这次把 u 固定在城市待补的用户 3。先算括号里的相关子查询,再决定外层是否留人。
u.city 是 NULL。'BJ' = NULL 和 NULL = NULL 都为未知。没有右行通过 WHERE,SELECT 1 返回零行。NOT EXISTS 为真,用户 3 会留下。只统计已知城市时,外层还要加 u.city IS NOT NULL。运行下面四种写法,核对用户 3 是否留下,再观察右侧名单为空时结果怎样变。
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())
print("B + 外层只留已知城市:", db.execute(
"SELECT u.id FROM users u WHERE u.city IS NOT NULL AND NOT EXISTS "
"(SELECT 1 FROM hq h WHERE h.city = u.city) ORDER BY u.id"
).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,)]
B + 外层只留已知城市: [(2,), (4,), (5,)]
| 写法 | 结果与原因 |
|---|---|
A · NOT IN | 0 行。 用户 1 命中,条件为假。其余四人都得到未知。 |
B · NOT EXISTS | 4 行。 保留 2、3、4、5。对这四人,内层都找不到等号为真的右行。 |
C · LEFT JOIN 后查 IS NULL | 4 行。 保留 2、3、4、5。他们的右侧城市来自左连接补行。 |
D · NOT IN,先滤右侧缺值 | 3 行。 保留 2、4、5。名单只剩 'BJ',用户 3 的条件仍为未知。 |
- 用 C 时检查哪一列。 普通等号为真时,右侧
h.city一定有值。一般应检查右表的非空标识列。若改查可空的备注列,真匹配但备注缺失的行也会通过IS NULL。 - 只统计已知城市。 B 可在外层加
u.city IS NOT NULL。D 的内层条件只滤掉右侧的缺值,外层还需用city IS NOT NULL明确口径。
右侧子查询返回零行时,
IN为假、NOT IN为真,即使左值是NULL。所以 D 过滤完右表之后若得到空结果,仍可能保留用户 3。示例用空子查询演示这一点。SQLite 也接受空的字面量列表IN (),很多数据库要求列表至少有一项。
无论右侧来自字面量还是子查询,都要检查两侧可空性。右侧已知且非空时,左侧缺值仍会使普通 NOT IN 为未知。SQLite 的 IN / NOT IN 结果表 · PostgreSQL 子查询条件
阶段回顾 · 从真值定位漏行
- 刚解决的问题 名单有缺值时,找不到真匹配的行为什么也可能被筛掉。
- 关键规则
WHERE和HAVING只留真,ON只配真,CHECK在假时才拒绝。NOT EXISTS只检查有无匹配行。- 容易混淆 滤掉右侧缺值后仍要决定左侧缺值怎么处理,尤其是右侧被滤空时。
下一节改查报表总量,判断是一整行被筛掉,还是一列的缺值没参与聚合。
🧮 五、那些 NULL 行,到底是在哪一步没的
延续 03 章的聚合。先看三笔付款(20 元、5 元、金额待补)都进入同一个总计。COUNT(*) 数输入行,所以是 3。COUNT(amount) 数金额非 NULL 的行,所以是 2。两数相减 3 − 2 = 1,说明还有一笔金额没录。金额为 0 也会进入 COUNT(amount)。
这三笔的已知金额和是 20 + 5 = 25 元,AVG(amount) 用 2 作分母,得到 25 ÷ 2 = 12.5 元。若报表只剩一笔待补记录,行数是 1,已知金额数是 0。再把它筛掉,两个计数才都为 0。这两种情况下 SUM 和 AVG 都返回 NULL。下面使用无 GROUP BY 的汇总,空输入也返回一行结果。
import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE payments(id INTEGER PRIMARY KEY, amount INTEGER);
INSERT INTO payments VALUES (11, 20), (22, 5), (33, NULL);
""")
summary = "SELECT COUNT(*), COUNT(amount), SUM(amount), AVG(amount) FROM payments"
print("全部:", db.execute(summary).fetchone())
print("仅待补:", db.execute(summary + " WHERE id = 33").fetchone())
print("零输入行:", db.execute(summary + " WHERE id = 99").fetchone())
运行结果(Python 用 None 显示 SQL 的 NULL)。
全部: (3, 2, 25, 12.5)
仅待补: (1, 0, None, None)
零输入行: (0, 0, None, None)
在本例中,SUM、AVG、MIN 和 MAX 都只使用非 NULL 的金额。聚合的返回类型和空输入规则要查具体函数。例如 SQLite 的 AVG 对整数输入仍返回浮点数,SQL Server 的 AVG(int) 返回整数,需小数时可以先转换输入类型。SQLite 的 total 在没有非空值时返回 0.0,PostgreSQL 的 array_agg 则可把输入的 NULL 收进数组。SQLite 聚合函数 · PostgreSQL 聚合函数 · SQL Server AVG
接下来区分另一种情况:通话的金额已录入,但城市缺失。以下金额沿用示例表的 cents,单位为分。先算全表的 1 + 2 + 4 + 8 + 16 = 31,再核对每种查询让哪些行参与求和。
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
这里仅按 city 分组,城市缺值的两笔通话归入同一组,合计 4 + 16 = 20 分。三组的合计是 20 + 9 + 2 = 31 分,与全表一致。第六节会看多列分组怎样处理缺值。
💀 丢在后面三步,而且都不报错:
| 那一步 | 结果 | 为什么 |
|---|---|---|
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 | 对城市缺值的两行,IN ('BJ','SH') 为 UNKNOWN,WHERE 排除它们。 |
b 实际只有 2 分。少掉的两笔是 4 + 16 = 20 分。用全表的 31 分作分母,20 ÷ 31 × 100% ≈ 64.52%,保留一位小数得 64.5%。若报表口径包含城市待补的通话,改用 WHERE city IS NULL OR city <> 'BJ',得 2 + 20 = 22 分。
上表中的三种查询各自从原表开始。GROUP BY 之后未知城市组的两行仍然贡献 4 + 16 = 20 分。左连接后的报表也可以同时保留两个计数:COUNT(*) 数结果行(包括补行),COUNT(右表标识列) 数实际匹配行,前提是该标识在右表中必有值。
🔀 六、⭐ SQL 里其实有两套 NULL 语义
比较条件、分组、去重和排序有各自的规则。下例用两个城市缺值的用户观察分组与去重。输出中没有指定排序的查询,只比较结果内容。
唯一约束(UNIQUE)用于防止重复写入同一个已知键,例如同一个邮箱。各数据库允许多少个缺值有自己的规则。下面在 SQLite 中分别插入重复邮箱和三个缺值。
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)]
唯一约束按数据库自身规则检查。SQLite 的例子拒绝重复的 'a@x.com',接受三个 NULL。如果业务要求邮箱必填且唯一,就同时声明 NOT NULL 和 UNIQUE。PostgreSQL 15 起也可声明 UNIQUE NULLS NOT DISTINCT,让单列唯一约束最多接受一个缺值。PostgreSQL 15 唯一约束
| 操作 | 两个 NULL 的处理 |
|---|---|
普通等号 = | 比较结果是 UNKNOWN,ON 无法建立配对。 |
GROUP BY | 分组时同一键位置的两个 NULL 视作相同。多列分组要求其余分组键也相同。 |
DISTINCT、UNION | 其余列也相同时,去重只保留一行。 |
ORDER BY | 在该排序键上并列,先后由后续排序键决定。 |
用 GROUP BY city, channel 按城市和渠道一起分组时,两个 (NULL, 'web') 同组,(NULL, 'app') 另成一组。
需要让两个缺值也能配对时
需要让两个缺值在连接中匹配时,要显式使用支持空值的相等判断。对普通标量列,SQLite 可写 a IS b,PostgreSQL 可写 a IS NOT DISTINCT FROM b,MySQL 提供 a <=> b。
如果左表有两个缺值、右表有三个,这样匹配会产生 2 × 3 = 6 对。汇总前检查连接有没有把行数放大。SQLite 表达式 · PostgreSQL 比较谓词 · MySQL 比较运算符
ORDER BY 里 NULL 排哪儿
五行的排序键 v 分别是 30、NULL、10、NULL、20。先预测升序的第一行,再从已知数字 30、10、20 中找出最小值。下面的默认排序样例展示 SQLite 的结果,v 相同时需要第二排序键才能指定先后。
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])
order = "CASE WHEN v IS NULL THEN 1 ELSE 0 END, v, id"
print("CASE 指定缺值在末尾:", db.execute("SELECT id, v FROM t ORDER BY " + order).fetchall())
实跑输出(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
CASE 指定缺值在末尾: [(3, 10), (5, 20), (1, 30), (2, None), (4, 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 8.4 | 最前 | 无此语法 | 官方文档,未实跑 |
| SQL Server | 最前 | 无此语法 | 官方文档,未实跑 |
这四种数据库的默认降序位置与升序相反。按更新时间降序取第一行时,SQLite 会先给出已知时间,PostgreSQL 默认可能先给出缺值行。SQLite 3.30 起及 PostgreSQL 可写 ORDER BY v ASC NULLS LAST, id。
跨这四种数据库可以写 ORDER BY CASE WHEN v IS NULL THEN 1 ELSE 0 END, v, id。三个排序键按下列顺序使用:
- 第一个键给已知值 0、缺值 1,把缺值排在末尾。
- 第二个键按
v升序排列已知值。 - 最后用
id处理并列。本例得到编号 3、5、1、2、4。
原示例的 (v IS NULL) 排序键适用于 SQLite、PostgreSQL 和 MySQL。SQLite 排序 · PostgreSQL 排序 · MySQL 8.4 缺值 · SQL Server 排序
阶段回顾 · 核对计数、分组和排序
- 刚解决的问题 同一个缺值在聚合、分组和排序里会怎样处理。
- 关键规则
COUNT(*)数行,COUNT(列)数有值的行。先查哪些行进入聚合,再看哪列缺值。- 容易混淆 两个缺值能分入同一组,普通等号仍得未知。SQLite 的升序首行可以缺值,
MIN则跳过缺值。下一节处理显示口径。昵称是空串或缺值时,报表该怎么选替代文本?
🧰 七、日常工具:COALESCE / NULLIF / 空串与 NULL
在 SQLite 中,空串 '' 是长度为 0 的文本,和 NULL 是两种情况。下面的例子使用 SQLite。Oracle Database 19c 将零长度字符值视为 NULL。Oracle 19c 缺值规则
| 函数 | 做什么 | 典型用途 |
|---|---|---|
COALESCE(a, b, c) |
返回第一个非 NULL 的参数;全为 NULL 时返回 NULL。 |
多级兜底,SQLite、MySQL 和 Oracle 都提供。SQLite 和 MySQL 的 IFNULL、Oracle 的 NVL 各写两个参数;跨库时另查类型和空串规则。 |
NULLIF(a, b) |
两个参数按函数的比较规则相等时返回 NULL,否则返回 a。SQLite 函数规则 |
把分母 0 或约定的哨兵值变为缺值。例如 NULLIF(age, -999)。 |
先预测两次取值。COALESCE(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())
print("SQLite 除法对照 :", db.execute("SELECT 10 / 0, 10 / NULLIF(0,0), 10 / NULLIF(2,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)
SQLite 除法对照 : (None, None, 5)
⚠️ 先看「拼接被吞」那行:id=2 和 id=4 的 nick 是 NULL,'你好, ' || NULL 整体变成 NULL(第二节那条「字符串也被吞」)——你想拼一句问候语,拿到的是一个空值。
而 id=3 的 nick 是空串,拼出来是 '你好, ':不是 NULL,只是少了个名字。⭐ 这一行输出里同时藏着本节要讲的两件事。
从筛选、计数和长度再核对一次。note IS NULL 找到 3、4,note = '' 找到 2。COUNT(note) 是 2,因为空串也被计入。length('') 是 0,length(NULL) 是 NULL。
除法对照中,NULLIF(0,0) 把分母改成 NULL,NULLIF(2,0) 保留 2,所以后者得 10 ÷ 2 = 5。SQLite 的 10 / 0 本来就返回 NULL;显式使用 NULLIF 是在查询中写出分母为 0 时结果缺失的口径。
写入规则取决于业务含义。如果业务区分“明确填了空文本”和“未填写”,就分别保存空串和
NULL,并按需要检查两种情况。如果业务把两者都当作未填写,可以在写入时统一为NULL。单独用IS NULL在 SQLite 中只会找到NULL。
🔗 这一章连到哪里
| 相关的地方 | 为什么 |
|---|---|
| 01-一条查询是怎么跑的.html | 那一章说「WHERE 逐行判断,扔掉不要的行」——本章第三节说明筛选只保留 TRUE。同一条件及其否定会同时排除条件为 UNKNOWN 的行。 |
| 02-JOIN的几种语义.html | 那一章有两处欠着本章一个解释:LEFT JOIN 补行后,哪些 WHERE 条件会保留它。两个缺值键用普通等号比较时,为什么无法配对。 |
| 03-聚合与分组.html | 第 03 章介绍分组和聚合。本章第五节区分 COUNT(*) 与 COUNT(列),并核对是筛掉了输入行,还是列中缺值没有参与计算。 |
| 05-子查询的三种形状.html | 本章用相关子查询寻找真匹配。第 05 章继续说明 IN、EXISTS 和 JOIN 各自适合的查询。 |
| 10-拿SQL审问一份数据.html | ⭐ 那一章把 COUNT(*) - COUNT(city) 当探针用来量一份数据脏在哪;本章解释这个探针为什么成立,以及它那条白名单断言的 NULL 陷阱 |
| 数据这一关 · 05 缺失不是一种东西 | ⚠️ 同名不同物:那一章讲缺失的成因(MCAR/MAR/MNAR)和该不该填;本章讲已经是 NULL 之后 SQL 怎么对待它。见本章第零节 |
| 数据这一关 · 04 脏数据的十种形态 | 比 NULL 更坏的是哨兵值(-999、"N/A")——只查 IS NULL 的缺失率监控会漏掉哨兵值。本章第七节的 NULLIF(age, -999) 就是把它们转回 NULL 的那一步 |
✅ 检查点
- 《数据这一关》05 章问缺失的什么问题?本章问什么?
- 假设
v缺值,v = NULL、v IS NULL、NOT (v > 10)各得什么?FALSE AND UNKNOWN与TRUE OR UNKNOWN又各得什么? - 算出 BJ、SH 和
NULL对NOT IN ('BJ', NULL)的真值。哪一行能通过对应的IN? - 三笔付款为 20 元、5 元和待补。第三笔能通过
WHERE amount > 10吗?条件放到左连接的ON后,账户 3 和付款 33 是否出现在结果中?把原付款按账户分组再用HAVING SUM(amount) > 10会保留哪一组? CHECK (age >= 18)是否接受NULL?要让年龄必填,可以加什么约束?- 第四节的四种写法各返回几行?如果只要城市已知且找不到匹配的人,如何改写 B?右侧子查询为空时,城市缺值能通过
NOT IN吗? - 只剩一笔金额待补的付款时,
COUNT(*)、COUNT(amount)、SUM(amount)和AVG(amount)分别是什么?在通话报表里,城市缺值的 20 分在哪些查询中消失?若三笔通话的状态依次是失败、成功、待补(存为NULL),失败次数除以有状态的笔数和全部笔数,各得多少? - 按城市和渠道一起分组时,
(NULL, 'web')和(NULL, 'app')同组吗?两个缺值城市键在普通等值连接中能配对吗?SQLite 的MIN(v)和ORDER BY v LIMIT 1为什么不同? - 在 SQLite 中,
COALESCE(nick, name, '(匿名)')遇到空串昵称会返回什么?业务若把空串当成未填,应如何改写?
👀 答案
- 《数据这一关》研究缺失的原因以及填补对分析的影响。本章研究值存为
NULL后查询怎样计算。 - 依次是
UNKNOWN、TRUE、UNKNOWN。一项为假足以使AND为假,一项为真足以使OR为真。 - BJ 的
IN为真,取反为假。SH 与NULL的IN都是未知,取反仍是未知。只有 BJ 通过IN。 - 第三笔比较为未知,无法通过
WHERE。在ON中它无法配对,左连接保留账户 3 并把付款编号补为NULL。从原付款分组时,账户 3 的合计为NULL,账户 2 的合计 5 小于门槛,只有账户 1 的 20 通过HAVING。 - 接受。
CHECK在条件为假时拒绝,可以同时用NOT NULL要求该列有值。 - A、B、C、D 依次是 0、4、4、3 行。给 B 加外层
u.city IS NOT NULL。右侧子查询返回零行时,即使左侧城市缺值,NOT IN也为真。 - 依次为 1、0、
NULL、NULL。通话表分组时仍保留NULL城市组,4 + 16 = 20 分。内连接没有找到匹配城市,WHERE city <> 'BJ'和白名单IN的条件对这两行为未知,三种查询都排除了这 20 分。失败次数为 1,有状态 2 笔,全部 3 笔,所以两种分母分别得到1 ÷ 2 = 50%和1 ÷ 3 ≈ 33.3%。 - 两行渠道不同,分到不同组。城市键都缺值时,普通等值连接的配对条件为未知,无法匹配。
MIN(v)在 30、10、20 中取 10,SQLite 默认升序把缺值放在最前。可用CASE WHEN v IS NULL THEN 1 ELSE 0 END作第一个排序键,把缺值排在末尾。 - 在 SQLite 中得到空串。可用
COALESCE(NULLIF(nick, ''), name, '(匿名)')先把空串转为NULL,再找第一个有值的参数。
本节回顾
- 用
IS NULL检查缺值。用普通比较算条件时,先写出 TRUE、FALSE 或 UNKNOWN,再做AND、OR和NOT。 WHERE留真行,HAVING留真组,ON配对需要真。左连接会为没有配对的左行补出空的右侧列,补行通过后续筛选仍要求整个WHERE为真。- 反连接先确定外侧缺值如何处理。
NOT EXISTS检查有没有真匹配。含缺值的单列NOT IN右侧会让未匹配行的条件变为未知。 - 核对
COUNT(*)和COUNT(列),分别检查输入行数与已知值数。取排序首行时,显式指定缺值位置和并列顺序。 - SQLite 把空串当作长度为 0 的文本。
COALESCE找第一个非NULL的值;只有业务把空串视为未填时,才先用NULLIF(列, '')把它转成NULL。
本章能判断缺值是否影响匹配和计数。下一章继续比较同一个存在性问题用 IN、EXISTS 或 JOIN 时,行数为何可能不同。05 · 子查询的三种形状