SQL 为什么 NULL 不能直接比较？

NULL比较返回UNKNOWN而非TRUE/FALSE，因NULL表示未知值，无法确定与任何值（包括自身）的相等性；SQL采用三值逻辑，仅IS NULL/IS NOT NULL能正确判断NULL。

NULL

比较为什么会返回 UNKNOWN 而不是 TRUE/FALSE

SQL 中 NULL 表示“缺失值”或“未知值”，它不是一个具体的数据，而是一种状态。因此，NULL = 5、NULL != 'abc' 或 NULL = NULL 都无法判断真假——你没法断言一个未知值是否等于某个已知值，也没法断言两个未知值是否相等。SQL 标准为此引入了三值逻辑（TRUE/FALSE/UNKNOWN），所有涉及 NULL 的常规比较运算符（=、!=、、>、IS NULL 除外）都会返回 UNKNOWN。

这直接影响 WHERE 过滤：SQL 只保留判定为 TRUE 的行，UNKNOWN 和 FALSE 都被排除。所以写 WHERE col = NULL 实际上永远不匹配任何行。

如何正确判断字段是否为 NULL

必须使用专用谓词 IS NULL 或 IS NOT NULL，这是唯一语义明确、跨数据库兼容的方式。

• WHERE col IS NULL ✅ 正确
• WHERE col = NULL ❌ 总是不生效（返回空结果集）
• WHERE col 'x' OR col IS NULL ✅ 若想包含 NULL 和非 'x' 的值
• 某些数据库（如 PostgreSQL）支持 IS DISTINCT FROM，可安全比较含 NULL 的两列：WHERE a IS DISTINCT FROM b，它把 NULL = NULL 视为 TRUE

常见踩坑场景和修复方式

很多逻辑错误源于把 NULL 当作普通值处理：

• 聚合函数中忽略 NULL：比如 COUNT(col) 不统计 NULL 行，但 COUNT(*) 统计所有行——若误用前者可能导致计数偏低
• 连接条件里用 = 匹配可能为 NULL 的字段：例如 ON a.id = b.ref_id 会自动跳过 b.ref_id 为 NULL 的记录，即使业务上希望保留（此时应考虑 LEFT JOIN + IS NULL 显式判断）
• ORDER BY 中 NULL 排序行为不一致：MySQL 默认 NULL 排最前，PostgreSQL 默认排最后，显式写 ORDER BY col NULLS FIRST 或 NULLS LAST 更可靠（注意不是所有数据库都支持该语法）
• 应用层拼接 SQL 时硬编码 col = ?，却没对 NULL 参数做分支处理——后端应检测参数是否为 null，并动态改用 IS NULL

COALESCE 和 NULLIF 怎么帮我们绕开比较陷阱

当确实需要把 NULL 转成可比较的值时，这两个函数比 CASE WHEN 更简洁：

• COALESCE(col, 0) 返回第一个非 NULL 值，常用于补缺：WHERE COALESCE(price, 0) > 100
• NULLIF(a, b) 在 a = b 时返回 NULL，否则返回 a；适合做“相等即抹除”的逻辑，比如避免除零：SELECT dividend / NULLIF(divisor, 0)
• 注意：这些函数本身不解决“判断是否为 NULL”的问题，而是把 NULL 消解成确定值后再比较——性能上可能略逊于直接 IS NULL，尤其在有索引的字段上

真正容易被忽略的是：哪怕你记得用 IS NULL，在复杂表达式中（比如嵌套子查询、窗口函数、JSON 提取结果）仍可能意外产出 NULL，而它的传播性极强——一个 NULL 参与加减乘除或字符串拼接，整个结果就变成 NULL。查数据不对时，先盯住每一步有没有隐式产生 NULL，比反复检查等号更有效。