为什么 SQL 中的 NULL 不能像零一样比较

用三行数据看清区别

假设三个账户的余额分别是 100、0 和 NULL。账户总数是三个，但只有两个余额已知。第三个账户可能没有余额，也可能有欠款或存款；现有数据无法判断。因此，缺少余额不等于余额为零。

下面使用 VALUES 构造数据，不需要事先创建表。结果是 total_rows = 3、known_balances = 2、mean_known = 50。如果把缺失值替换为零，mean_with_zero 约为 33.33。变化来自平均数的分母，意味着报表指标的定义发生了变化，而不只是空白单元格换了一种显示方式。

WITH balances(balance) AS (
  VALUES (100::numeric), (0::numeric), (NULL::numeric)
)
SELECT COUNT(*) AS total_rows,
       COUNT(balance) AS known_balances,
       AVG(balance) AS mean_known,
       AVG(COALESCE(balance, 0)) AS mean_with_zero
FROM balances;

查找缺失值

未知余额不一定等于零，查询中应保留这一差别。PostgreSQL 的 IS NOT DISTINCT FROM 可以将两个 NULL 视为相等。

SELECT * FROM accounts WHERE balance IS NULL;
SELECT a IS NOT DISTINCT FROM b;

明确计数对象

COUNT(*) 统计行数，COUNT(balance) 只统计非 NULL 的余额。COALESCE(balance, 0) 会把缺失值替换成零，只有当零符合数据含义时才应这样处理。

为什么筛选会漏掉记录

在这组三行数据上，WHERE balance <> 0 只会留下 100。零使条件为假，NULL 使条件未知。如果要同时保留非零余额和缺失余额，应明确写出 balance <> 0 OR balance IS NULL。

取反也不能找回缺失值：当 balance 为 NULL 时，NOT(balance = 0) 仍然未知。先确定业务上怎样处理缺失值，再编写条件。报表总数不符合预期时，不仅要检查留下了哪些记录，也要检查哪些记录被排除。

NOT IN 中的 NULL 陷阱

假设需要排除编号 2，但排除列表意外包含 NULL。此时，即使是 1 NOT IN (2, NULL)，结果也未知：SQL 无法确认 1 与列表中每一个元素都不同。如果将它放入 WHERE，这行记录就不会通过。

用子查询排除记录时，可以考虑采用带有明确匹配条件的 NOT EXISTS。它表达的是不存在符合条件的记录，不会直接继承结果列表中的 NULL。不过，编号本身缺失时应该如何处理，仍要由业务规则决定；换一种 SQL 写法不会自动决定缺失值的含义。

SELECT 1 NOT IN (2, NULL) AS result;

LEFT JOIN 后出现的缺失值

LEFT JOIN 会保留左表记录，并在没有匹配项时将右表字段填为 NULL。随后在 WHERE 中限制右表字段，可能再次排除这些未匹配记录。若希望保留所有账户，而右表仅匹配有效记录，应把右表的限制放到 ON 中。

使用 COALESCE 之前，先问清楚为什么缺失：没有采集、字段不适用，还是连接时没有找到记录？这些情况可能需要不同的说明。建议先统计总行数、已知值数量与缺失数量，再检查连接条件，最后决定报表中是否以及如何替换缺失值。