SQL中NULL的陷阱与处理：从三值逻辑到Oracle优化实践

1. 先把三值逻辑这层窗户纸捅破：NULL为什么连自己都不认识自己

接手过任何一个老库，大概率会碰到这种“业务bug”：页面明明提交了数据，列表却查不到；记录明明字段没填，搜索框填个空串就是不命中。最后翻代码，十有八九是WHERE 列 = NULL或者把''当成了空字符串处理。在 Oracle 里，NULL 表示的是“未知”“缺失”“没有值”，它既不是空字符串，也不是 0，更不是空格。数据库在存储层面只标记了这个位置“没有内容”，并没有保存一个具体数值。

SQL 里的判断结果不是非真即假，而是有三种状态：真、假、未知（UNKNOWN）。1 = 1返回真，1 = 2返回假，而NULL = NULL返回未知。两个未知的东西到底相不相等，数据库没法判断，所以它不会给你一个肯定的“真”。WHERE 子句只保留结果为真的行，未知和假一样，都会把行丢掉。这就是为什么写WHERE column = NULL永远查不出任何行——每一行的比较结果都是 UNKNOWN，没有一行能走进结果集。

更有意思的是，取反一个 UNKNOWN，结果还是 UNKNOWN。也就是说WHERE age > 30 OR NOT (age > 30)这种“怎么说都该成立”的条件，遇到age IS NULL的行时，前半段是 UNKNOWN，后半段也是 UNKNOWN，OR 完还是 UNKNOWN，这一行照样不出现。业务同事经常跟我抱怨“我明明写的是排除不满足条件的，怎么连没有值的也一起没了”，其实本质就是三值逻辑在起作用。

Oracle 和部分其他数据库还有一个让新人措手不及的行为：空字符串''会被自动转成 NULL。你写WHERE col = ''，等价于拿 NULL 去做比较，结果自然是查不到任何行；往表里插INSERT INTO t(a) VALUES ('')，之后再读出来会发现a IS NULL是真的。这个坑从 MySQL、PostgreSQL 迁过来时特别明显，如果老系统里确实需要“空串”这种语义，建议在 Oracle 里用' '占位或者干脆铺一个默认值，别指望''能保留原样。

1.1 一组小实验，彻底理解“未知”

与其背文档，不如直接在测试库跑几条 SQL：

SELECT * FROM dual WHERE NULL = NULL; -- 0 行 SELECT * FROM dual WHERE NULL IS NULL; -- 1 行 SELECT * FROM dual WHERE '' = ''; -- 0 行，'' 已被当作 NULL SELECT * FROM dual WHERE NVL(NULL, 1) = 1; -- 1 行

这几条跑完，基本就能记住：普通比较运算符永远匹配不了 NULL，唯一的正确姿势是IS NULL和IS NOT NULL，还有后面会提到的几个专门函数。反过来说，如果你在代码里发现某个过滤条件写着“等于某个空值”，它十有八九是个静默错误——不报异常，只是结果集里少了一大片数据，线上跑几天都不一定能发现。

2. NULL 在 WHERE、NOT IN 里造成的“隐性截断”

如果说三值逻辑是理论课，那下面这些就是实战课。第一个常见现象是 LEFT JOIN 之后，前缀条件把左表数据悄悄砍掉。很多人以为写了 LEFT JOIN 就能保住左表的全部行，结果习惯性在 WHERE 里加右表字段的条件：

SELECT * FROM orders o LEFT JOIN payment p ON o.pay_id = p.pay_id WHERE p.status = 'PAID';

这条 SQL 实际执行时，会先把两表连接起来，再对连接结果做 WHERE 过滤。那些没有匹配到支付记录的订单，p.status是 NULL，NULL = 'PAID'返回 UNKNOWN，于是这些订单全部被滤掉。LEFT JOIN 在逻辑上已经退化成了普通连接。正确的写法是把过滤条件挪到 ON 子句里：

SELECT * FROM orders o LEFT JOIN payment p ON o.pay_id = p.pay_id AND p.status = 'PAID';

这样未匹配的订单依然会保留，只是右侧字段全部显示为 NULL。这个改法不需要动索引，不需要动表结构，纯粹是 SQL 语义的问题，但在我经手的团队里，出现频率高得惊人。

第二个常见坑是 NOT IN。如果子查询返回的结果列表里带一个 NULL，结果往往不是“排除掉那行”，而是整个查询一行都查不出来。看这个很自然的写法：

SELECT * FROM employee e WHERE e.department_id NOT IN ( SELECT department_id FROM resigned_employee );

只要resigned_employee.department_id有任何一行是 NULL，数据库就会把条件展开成：

e.department_id <> 1 AND e.department_id <> 2 AND ... AND e.department_id <> NULL

最后那个<> NULL的结果永远是 UNKNOWN，而多个 AND 条件只要有一个是 UNKNOWN，整体结果就是 UNKNOWN，于是所有行都会被过滤。有一次我在项目里查“未离职员工”，上线后列表直接空白，排查半天才发现离职表里几个历史部门的字段是 NULL，导致整条 SQL 全军覆没。

2.1 NOT IN 与 NOT EXISTS 的差异现场

绕开这个坑，最简单的办法是把 NOT IN 改成 NOT EXISTS：

SELECT * FROM employee e WHERE NOT EXISTS ( SELECT 1 FROM resigned_employee r WHERE r.department_id = e.department_id );

NOT EXISTS 是逐行去查内层有没有匹配记录，内层返回 NULL 或者不匹配都不会反过来否定外层行。换句话说，NOT IN 的逻辑是“先凑出一个值列表，再用集合关系判断”，NOT EXISTS 则是“一行一行看是否存在”。前者遇到 NULL 会翻车，后者不会。所以我在代码评审里通常直接要求：凡是做“排除”语义，优先写 NOT EXISTS，除非你能百分之百保证子查询的列无 NULL。

同样的逻辑也出现在 CASE 表达式里。CASE WHEN col = NULL THEN '缺失' ELSE '存在' END永远不会进入‘缺失’分支，因为col = NULL的结果是 UNKNOWN，会直接落到 ELSE。正确的写法是CASE WHEN col IS NULL THEN '缺失' ELSE '存在' END。另外，页面搜索时用户没输入条件，后端很可能传一个 NULL 参数，如果直接拼WHERE name = :inputName，当参数是 NULL 时结果集同样是空。这种场景建议根据传入值动态拼接，或者用这种安全写法：

WHERE (name = :inputName OR (:inputName IS NULL AND name IS NULL))

不过要注意，这个写法会把表中本来就为 NULL 的行也筛出来，所以具体业务里“用户没输入到底要不要筛 NULL 行”，一定要和产品确认，不要自己拍脑袋。

3. 空串、聚合、分组：涉及 NULL 的统计口径要提前定

数据库在底层的处理上，也不止“查不出来”这一个影响。Oracle 里 NULL 占用的实际存储非常小，基本就是一个标记位，不像定长字符串那样铺满空间，这也是它在存储层和空字符串的一个区别。但真正的麻烦在于，应用层拿到 NULL 后，很多框架会把字段映射成 Java/Kotlin 的 null，而不是空字符串，序列化、非空校验、接口返回时都会冒出一堆意料之外的问题。

聚合函数对 NULL 的态度也经常被人读错。COUNT(*)和COUNT(1)统计的是行数，不管这一行是不是全空；COUNT(列)统计的是该列非 NULL 的个数。报表里“参与人数”和“总人数”的差异就是这么来的。有一回同事统计上传单据数量，直接COUNT(单据编号)，可历史数据里不少单据没有编号，结果月度报表数量少了一截，定位到后来才发现问题出在口径上。

SUM、AVG同样会忽略 NULL。更麻烦的是，如果某一组的值全部是 NULL，SUM 返回 NULL，AVG 返回 NULL，而不是 0。这个 NULL 一旦被后面的表达式引用，就会一路传染：比如“增长率 = 本月销售额 / 上月销售额”，上月不存在的组除数是 NULL，计算结果是 NULL，报表里就莫名其妙多出一片空格。我个人的习惯是，统计类查询在最终展示层统一包一层NVL或COALESCE，先定好“没数据到底显示 0 还是显示空”，别让 NULL 在多个指标之间串来串去。

3.1 聚合函数眼中的 NULL 与 COUNT 口径

做个简单归类：

GROUP BY把 NULL 归为一组这个行为，在分页、看板、按地区统计时特别容易被忽略。某个地域字段没填的记录，会汇总成一行 label 为 NULL 的数据。建议统计前用NVL(region, '未知地区')做一次转换，至少展示层不会出现一个空分类名。

3.2 设计阶段的 NULL 取舍

这里要聊一个看起来和查询无关、其实影响最大的决策：建表时到底允不允许 NULL。有些团队嫌麻烦，所有列一律不加 NOT NULL，觉得以后加数据方便。结果一旦数据里混进大量 NULL，过滤、统计、索引、关联全都要围着它转，代价比建表时多写几个约束大得多。我在新项目里的习惯是三层判断：业务上必须有值的列，直接 NOT NULL；代码里其实总会给默认值 0 或空字符串的列，也不用 NULL，直接用默认值；只有语义上真正是“缺失、未知、尚未发生”的才保留 NULL。例如“优惠券使用时间”适合 NULL，“订单状态”必须 NOT NULL。这个决策最好在表结构评审时就定下来，等报表对不上数再回头改，成本完全不是一个量级。

4. NVL、NVL2、COALESCE、NULLIF 到底该用哪个

NULL 处理函数是 Oracle 里最常用的八个函数之一，但不少人只认 NVL，导致某些场景写得很别扭。先说结论，函数全家福如下：

NVL 是 Oracle 专属函数，如果项目有跨数据库的预期，优先写 COALESCE 更稳。COALESCE 可以传多个参数，逻辑上更像 CASE 的简化版，一旦找到第一个非 NULL 就不再继续往下评估，这在候选值是子查询时能省不少成本。地址展示就是一个典型场景：省份、城市、区县三个字段，哪个有值展示哪个，直接COALESCE(province, city, district)一行写完。

4.1 函数对比速查表

NULLIF 最常见的用途是除零保护。amount / NULLIF(quantity, 0)里，quantity 为 0 时 NULLIF 返回 NULL，整个除法结果变成 NULL，避免数据库直接报 ORA-01476，业务层再套一层 NVL 就能得到 0。这个组合写法我在计算费率、分摊金额时用了很多年，稳定可靠。

这里还要提醒一个容易踩的差异：DECODE 对 NULL 的匹配逻辑和 CASE 不同。DECODE(col, NULL, '未知', col)在 col 为 NULL 时能命中‘未知’分支，因为 DECODE 内部把两个 NULL 当成同值处理；而 CASE 必须写CASE WHEN col IS NULL THEN '未知' ELSE col END。我改过不少存储过程，很多老代码用 DECODE 用得挺顺手，后来加需求改成 CASE，一不下心就漏了 IS NULL 判断，结果 NULL 分支老走错。

4.2 嵌套场景示例

举个常见的三层兜底需求：订单展示优先用发货时间，没有就按下单时间，再没有就按更新时间，都没有则显示“无记录”：

SELECT order_id, NVL(COALESCE(ship_time, order_time, update_time), '无记录') AS display_time FROM orders;

内层 COALESCE 反映“多个字段里取第一个有值”，外层 NVL 反映“全部为空给兜底文案”。我的使用建议是：单值兜底首选 NVL；多值轮询首选 COALESCE；需要按是否为 NULL 走向不同分支时选 NVL2 或 DECODE。别小看这几个函数的选择，它们表面上都能完成“空值替换”，但在参数数量、可移植性、短路行为上差异很明显，写错不一定报错，但后续维护会相当费劲。

5. 索引、唯一约束与 NULL 打架：一次排查过程的还原

NULL 对索引的影响，是很多人建索引时完全没考虑过的维度。Oracle 的 B 树索引有一个特点：索引键全部为 NULL 的行，基本不会进入常规索引条目。换句话说，一张表如果某列大部分是 NULL，在这个列上建的普通索引，对 NULL 部分来说几乎是空的。

这带来的直接影响是：WHERE col IS NULL这种查询很难走普通索引。优化器一估算，发现索引里根本没有这些行，走索引反而要回表，干脆选了全表扫描。如果表只有几万行，问题不大；但表到了千万级，一个status IS NULL加上其他过滤条件，查询时间会从几十毫秒涨到几分钟，慢查询日志里全是它。

5.1 索引侧的实践现象

想要把“是否为 NULL”也纳入索引，常用办法是函数索引：

CREATE INDEX idx_t_col_null ON t (NVL(col, -1)); SELECT * FROM t WHERE NVL(col, -1) = -1;

这里-1必须选一个业务上不会出现的占位值。需要注意两点：一是查询里必须写同样的 NVL 表达式，否则优化器无法把普通col IS NULL匹配到函数索引；二是占位值一旦冲突，会把真实数据和“缺失”混成同一个索引键，那比不用索引更危险。占位值的选择和注释，建议都写进建表脚本的说明里。

5.2 唯一约束与函数唯一索引

更隐蔽的是唯一约束对 NULL 的容忍。Oracle 唯一索引和唯一约束允许多个 NULL 行，因为数据库认为“未知”与“未知”不相等，自然不违反唯一性。可很多业务要求恰恰相反，比如用户扩展属性表里user_id + attr_code需要唯一，但 attr_code 为空时，代表的就是“同一类未指定属性”，业务上不应该重复插入。普通唯一约束管不住这种场景，数据库会放行多条 attr_code 为 NULL 的记录。

解决方案是函数唯一索引：

CREATE UNIQUE INDEX uk_user_attr ON user_attr( user_id, NVL(attr_code, 'UNKNOWN') );

这样 attr_code 为 NULL 的记录会被统一映射成'UNKNOWN'，再插入第二条同样为 NULL 的记录就会触发 ORA-00001 唯一约束冲突。这里要提醒：函数唯一索引报错时的提示信息里，约束名要提前维护好，否则 DBA 半夜收到告警还得去翻 dba_indexes 才能定位是哪个索引在拦数据。

5.3 一次统计任务的排查链路

有一回处理定时任务，某个分片统计表一天能查询上千万次，某天突然从几百毫秒涨到秒级。我的排查过程是先拿实际 SQL 看执行计划，发现条件长这样：

WHERE reserve_time = :day OR reserve_time IS NULL

普通 B 树索引覆盖了reserve_time的等值条件，但完全没覆盖reserve_time IS NULL的分支。优化器为了处理这个 OR，放弃走索引，选择了全表扫描。当时没有急着建函数索引，先看了下reserve_time为 NULL 的数据量，发现只有几千行，但分布在整个表里。最后的方案是拆分成两个查询：非空部分走普通索引，NULL 部分单独扫一个小结果集，再在应用层合并。这样既保住了索引，也不用引入占位值。这个案例说明，遇到“索引 + NULL”的组合问题，最好先评估数据分布，不要条件反射式地加函数索引，方案要和数据特征匹配。

6. 我要求团队遵守的一组 NULL 写法与评审红线

总结下来，NULL 的问题不是“某个函数不会用”，而是散落在比较、过滤、统计、索引、约束各个层面的系统性坑。结合几次踩坑教训，我现在对新项目的开发规范做着这几条硬要求，大家可以直接抄去用。

6.1 红线清单

• 所有比较 NULL 的场景只许写IS [NOT] NULL，代码里出现= NULL直接打回，不管看起来多像“标准写法”。
• 能写 NOT EXISTS 就不要写 NOT IN。除非子查询列已加非空约束，否则 NOT IN 遇到隐藏 NULL 会把整个结果集带崩。
• LEFT JOIN 的右侧表过滤条件尽量放 ON 子句；如果放在 WHERE，必须明确意图是“过滤掉未匹配行”。
• 统计报表必须写清楚 COUNT 口径：COUNT(*)还是COUNT(列)，SUM/AVG 对 NULL 组的返回值是 NULL 而不是 0，展示层要统一用 NVL/COALESCE 兜底。
• 建表阶段多用 NOT NULL。真正语义为“未知”的列才保留 NULL，能用默认值表达的不要让 NULL 泛滥。
• 索引列上套 NVL/COALESCE 之前，要先想好函数索引和占位值，不让优化器白折腾。

6.2 最后的一点心得

把这套规则执行一段时间后，我最大的体会是：NULL 本身不是 bug，它是数据库给出的“诚实回答”。问题常常出在写代码的人心里带着“这里肯定有值”的假设。所以每次写新查询，我都会先问一遍自己：这个字段要是 NULL，结果会变成什么样？会不会出现行数变少、统计变成空、关联偏移这类静默问题？先想清楚这一层，再写 SQL，比事后靠压测和日志定位要省力得多。把这四条规则贴在你团队的评审清单开头，下一次看到有人写WHERE 列 = NULL，就直接拉着大家一起复习三值逻辑好了。