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

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。

演示输入:用户城市
用户编号城市
1BJ
2SH
3NULL
4SH
5GZ

IN 判断左侧值是否在名单中,NOT IN 取反。先用城市已知的用户 2 做一次存在性检查,再看缺值怎样影响两个判断。

在 FROM users u 和 FROM hq h 中,u、h 是表的别名(alias),用来指明 city 来自哪张表。括号里的查询叫子查询(subquery)。这里的内层用 h.city = u.city 引用外层当前行,叫相关子查询(correlated subquery)。

1. 取当前用户用户 2 的 u.city 是 SH。
2. 与名单逐条比较'BJ' = 'SH' 为假,NULL = 'SH' 为未知。内层 WHERE 留下 0 行。
3. 检查有没有留下行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。

SQL 三值逻辑真值表
ABA AND BA OR B
TTTT
TFFT
TUUT
FTFT
FFFF
FUFU
UTUT
UFFU
UUUU

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 → T
NOT IN → F,排除
2 · SH等于 BJ → F
等于 NULL → U
IN → U
NOT IN → U,排除
3 · NULL等于 BJ → U
等于 NULL → U
IN → U
NOT IN → U,排除
4 · SH等于 BJ → F
等于 NULL → U
IN → U
NOT IN → U,排除
5 · GZ等于 BJ → F
等于 NULL → U
IN → U
NOT IN → U,排除

用户 1 的 TRUE OR UNKNOWN 为真,取反为假。用户 2 的 FALSE OR UNKNOWN 为未知,取反仍为未知。五行的 NOT IN 都没得到真,单独作为 WHERE 条件时返回零行。

混合类型时,能照样改写吗?

SQLite 的列亲和性(type affinity)是列倾向采用的数据类型,例如文本或数字。两列用等号比较时,亲和性可以影响比较前的转换。IN 右侧写成括号里的列表时,SQLite 视列表项为没有亲和性,即使该项引用了一列。

设 x 是 TEXT 列中的文本 '01',y 是 NUMERIC 列中的数字 1。对照两种计算:

  1. x NOT IN (y, NULL)。列表项 y 没有亲和性,第一项按文本比较。'01' 与 '1' 不同,第二项与 NULL 比较为未知,取反后仍未知。
  2. 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 → F
NOT (v > 10) → T
v = NULL → U
v IS NULL → F
2 · NULLv > 10 → U
NOT (v > 10) → U
v = NULL → U
v IS NULL → T
3 · 20 元v > 10 → T
NOT (v > 10) → F
v = NULL → U
v 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 仍检查这些补行,只保留整个条件为真的行。

把金额门槛放到 ON 与 HAVING 后
账户与金额左连接的配对与输出原付款表分组
1 · 20 元ON → T
付款编号 11
合计 20 元
HAVING → T,保留
2 · 5 元ON → F
补出 NULL
合计 5 元
HAVING → F,排除
3 · NULLON → U
补出 NULL
合计 NULL
HAVING → 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。先算括号里的相关子查询,再决定外层是否留人。

1. 固定外层一行用户 3 的 u.city 是 NULL。
2. 筛右表'BJ' = NULL 和 NULL = NULL 都为未知。没有右行通过 WHERE,SELECT 1 返回零行。
3. 决定是否留人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 IN0 行。 用户 1 命中,条件为假。其余四人都得到未知。
B · NOT EXISTS4 行。 保留 2、3、4、5。对这四人,内层都找不到等号为真的右行。
C · LEFT JOIN 后查 IS NULL4 行。 保留 2、3、4、5。他们的右侧城市来自左连接补行。
D · NOT IN,先滤右侧缺值3 行。 保留 2、4、5。名单只剩 'BJ',用户 3 的条件仍为未知。

右侧子查询返回零行时,IN 为假、NOT IN 为真,即使左值是 NULL。所以 D 过滤完右表之后若得到空结果,仍可能保留用户 3。示例用空子查询演示这一点。SQLite 也接受空的字面量列表 IN (),很多数据库要求列表至少有一项。

无论右侧来自字面量还是子查询,都要检查两侧可空性。右侧已知且非空时,左侧缺值仍会使普通 NOT IN 为未知。SQLite 的 IN / NOT IN 结果表 · PostgreSQL 子查询条件


阶段回顾 · 从真值定位漏行

下一节改查报表总量,判断是一整行被筛掉,还是一列的缺值没参与聚合。

🧮 五、那些 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。三个排序键按下列顺序使用:

  1. 第一个键给已知值 0、缺值 1,把缺值排在末尾。
  2. 第二个键按 v 升序排列已知值。
  3. 最后用 id 处理并列。本例得到编号 3、5、1、2、4。

原示例的 (v IS NULL) 排序键适用于 SQLite、PostgreSQL 和 MySQL。SQLite 排序 · PostgreSQL 排序 · MySQL 8.4 缺值 · SQL Server 排序


阶段回顾 · 核对计数、分组和排序

下一节处理显示口径。昵称是空串或缺值时,报表该怎么选替代文本?

🧰 七、日常工具: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 的那一步

✅ 检查点

  1. 《数据这一关》05 章问缺失的什么问题?本章问什么?
  2. 假设 v 缺值,v = NULL、v IS NULL、NOT (v > 10) 各得什么?FALSE AND UNKNOWN 与 TRUE OR UNKNOWN 又各得什么?
  3. 算出 BJ、SH 和 NULL 对 NOT IN ('BJ', NULL) 的真值。哪一行能通过对应的 IN?
  4. 三笔付款为 20 元、5 元和待补。第三笔能通过 WHERE amount > 10 吗?条件放到左连接的 ON 后,账户 3 和付款 33 是否出现在结果中?把原付款按账户分组再用 HAVING SUM(amount) > 10 会保留哪一组?
  5. CHECK (age >= 18) 是否接受 NULL?要让年龄必填,可以加什么约束?
  6. 第四节的四种写法各返回几行?如果只要城市已知且找不到匹配的人,如何改写 B?右侧子查询为空时,城市缺值能通过 NOT IN 吗?
  7. 只剩一笔金额待补的付款时,COUNT(*)、COUNT(amount)、SUM(amount) 和 AVG(amount) 分别是什么?在通话报表里,城市缺值的 20 分在哪些查询中消失?若三笔通话的状态依次是失败、成功、待补(存为 NULL),失败次数除以有状态的笔数和全部笔数,各得多少?
  8. 按城市和渠道一起分组时,(NULL, 'web') 和 (NULL, 'app') 同组吗?两个缺值城市键在普通等值连接中能配对吗?SQLite 的 MIN(v) 和 ORDER BY v LIMIT 1 为什么不同?
  9. 在 SQLite 中,COALESCE(nick, name, '(匿名)') 遇到空串昵称会返回什么?业务若把空串当成未填,应如何改写?
👀 答案
  1. 《数据这一关》研究缺失的原因以及填补对分析的影响。本章研究值存为 NULL 后查询怎样计算。
  2. 依次是 UNKNOWN、TRUE、UNKNOWN。一项为假足以使 AND 为假,一项为真足以使 OR 为真。
  3. BJ 的 IN 为真,取反为假。SH 与 NULL 的 IN 都是未知,取反仍是未知。只有 BJ 通过 IN。
  4. 第三笔比较为未知,无法通过 WHERE。在 ON 中它无法配对,左连接保留账户 3 并把付款编号补为 NULL。从原付款分组时,账户 3 的合计为 NULL,账户 2 的合计 5 小于门槛,只有账户 1 的 20 通过 HAVING。
  5. 接受。CHECK 在条件为假时拒绝,可以同时用 NOT NULL 要求该列有值。
  6. A、B、C、D 依次是 0、4、4、3 行。给 B 加外层 u.city IS NOT NULL。右侧子查询返回零行时,即使左侧城市缺值,NOT IN 也为真。
  7. 依次为 1、0、NULL、NULL。通话表分组时仍保留 NULL 城市组,4 + 16 = 20 分。内连接没有找到匹配城市,WHERE city <> 'BJ' 和白名单 IN 的条件对这两行为未知,三种查询都排除了这 20 分。失败次数为 1,有状态 2 笔,全部 3 笔,所以两种分母分别得到 1 ÷ 2 = 50% 和 1 ÷ 3 ≈ 33.3%。
  8. 两行渠道不同,分到不同组。城市键都缺值时,普通等值连接的配对条件为未知,无法匹配。MIN(v) 在 30、10、20 中取 10,SQLite 默认升序把缺值放在最前。可用 CASE WHEN v IS NULL THEN 1 ELSE 0 END 作第一个排序键,把缺值排在末尾。
  9. 在 SQLite 中得到空串。可用 COALESCE(NULLIF(nick, ''), name, '(匿名)') 先把空串转为 NULL,再找第一个有值的参数。

本节回顾

本章能判断缺值是否影响匹配和计数。下一章继续比较同一个存在性问题用 IN、EXISTS 或 JOIN 时,行数为何可能不同。05 · 子查询的三种形状

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