先讲一次真实的翻车经历。上个月帮朋友公司排查一个线上报表任务SQL 不长逻辑也顺理成章——从用户表里排除黑名单用户把剩下的人导出给运营。核心就这一句where user_id not in (select banned_user_id from blacklist)。这行 SQL 跑了几个月一直风平浪静。直到某天运营同学截图过来报表全空了一列数据都没有。我第一反应是数据源挂了检查了一遍用户表几万行数据都在。再回头去看黑名单表发现新导入的数据里banned_user_id有一行是 NULL。就这一个小小 NULL让整条查询静默地返回了空集。没有报错没有警告连日志都没有。这种逻辑黑洞最可怕的地方就在这里它不是崩溃而是悄悄把结果变空、变少、变错而且你很难第一时间察觉。这个坑几乎每个写过 SQL 的人都踩过只是踩的姿势不同——有的人是 NOT IN 失效有的人是统计口径对不上有的人是 JOIN 之后行数莫名减少。根子都在同一个地方SQL 里的 NULL 根本不是我们直觉中理解的空或者没有它代表未知。一旦未知参与运算结果大概率还是未知而过滤条件只保留真的结果于是所有沾上 NULL 的行全被无声丢弃。这篇文章就围绕这个主题把 NULL 的底层逻辑、NOT IN 失效的完整推演、其他同类黑洞以及一套可复用的排查和防御方法一次讲透。1. 先还原事故现场一条NULL是怎么让整张报表查无此人的1.1 一个最小可复现的翻车案例先把场景压缩到最简方便你直接在本地数据库里验证。两张表一张用户表一张黑名单表黑名单里故意塞了一条 NULL。-- 用户表 create table users ( user_id int primary key, user_name varchar(50) ); -- 黑名单表 create table blacklist ( banned_user_id int ); insert into users values (1, 小明), (2, 小红), (3, 小刚), (4, 小丽); insert into blacklist values (2), (null); -- 这条查询的结果是空集 select user_id, user_name from users where user_id not in (select banned_user_id from blacklist);按直觉用户 1、3、4 都不在黑名单里应该返回三行。但实际返回 0 行。数据没丢是查询的过滤逻辑把每一行都判成了不符合条件。为了理解这件事必须先搞清楚数据库底层那套和我们直觉完全不同的判断机制。1.2 三值逻辑数据库比直觉多了一个不知道我们日常写代码判断真假习惯的是二值逻辑要么 true要么 false。但 SQL 是关系模型的产物它要表达这个数据缺失这个条件还不知道成不成立这类状态所以在 WHERE 条件的求值里引入了一个额外的结果——UNKNOWN未知。也就是说任何一个谓词比较、逻辑运算求值结果不是两种而是三种谓词求值结果1 1TRUE1 2FALSE1 NULLUNKNOWNNULL NULLUNKNOWN1 NULLUNKNOWN而 WHERE 子句的行为是只保留求值结果为 TRUE 的行。UNKNOWN 和 FALSE 一样都被过滤掉。这就是关键NULL和任何值做比较结果不是 FALSE而是 UNKNOWN。这个多出来的第三态就是我们这类逻辑黑洞的总根源。1.3 NULL 不是空是不知道很多初学者会把 NULL 理解成 0、空字符串、或者没有。没有在我们日常语言里是一个明确的答案——没有就是没有嘛。但 SQL 里的 NULL 语义比这更弱它代表的是不知道。举一个直白的例子你问朋友那个人月薪超过一万吗。如果朋友回答没有你得到了一个明确答案但如果朋友回答我不知道那这个问题对你来说就是未知。你不能说未知等于没超过也不能说它等于超过。NULL 在 SQL 里的角色就是那个我不知道。所以它和 0、空字符串有本质区别0是一个值参与四则运算有意义0 1 1是一个值字符串拼接有意义a || aNULL不是值任何运算碰到它都会传染NULL 1结果是 NULLa || NULL结果还是 NULL把 NULL 当成 0 或者空串去写 SQL是所有空值事故的共同起点。后面的 NOT IN 失效、统计口径错乱、JOIN 丢行本质上都是这个未知位在传播。2. NOT IN 翻车的逻辑推演UNKNOWN 如何在过滤条件里吞掉所有行2.1 把 NOT IN 拆开看等价于一串 AND 连接的不等比较很多人只知道 NOT IN 会踩坑却不明白它为什么踩坑。搞清楚的最好方式是把 NOT IN 展开成基础逻辑。x NOT IN (a, b, c)在语义上等价于x a AND x b AND x c回到事故案例user_id NOT IN (select banned_user_id from blacklist)黑名单集合是{2, NULL}所以对每一个用户判断被展开成user_id 2 AND user_id NULL问题就出在user_id NULL这一项。前面说过任何值和 NULL 比较结果都是 UNKNOWN。于是对于用户 1小明1 2→ TRUE1 NULL→ UNKNOWNTRUE AND UNKNOWN→ UNKNOWN对于用户 2小红2 2→ FALSE2 NULL→ UNKNOWNFALSE AND UNKNOWN→ FALSE无论走哪条分支整个表达式都得不到 TRUE。WHERE 只保留 TRUE所以全表清零一张报表就这么空了。2.2 一个反直觉的细节手写 NOT(IN(...)) 也救不了有些同学遇到这个问题第一反应是把表达式反过来写where not (user_id in (select banned_user_id from blacklist))以为换个姿势能绕过。但逻辑上NOT (x IN (...))和x NOT IN (...)是完全等价的而且在三值逻辑里NOT UNKNOWN UNKNOWN所以把未知取反得到的还是未知。改写法不会改变结果照样空集。这恰恰是 NULL 逻辑比普通布尔逻辑更反直觉的地方——在二值逻辑里not false是true但在三值逻辑里对 UNKNOWN 取反依然是 UNKNOWN你永远得不到一个确定答案。2.3 为什么 IN 有部分行而 NOT IN 直接全灭细心的读者可能已经发现问题那 IN 碰到 NULL 会怎样会更好吗我们看user_id IN (2, NULL)它等价于user_id 2 OR user_id NULL以用户 1 为例1 2是 FALSE1 NULL是 UNKNOWNFALSE OR UNKNOWN结果是 UNKNOWN这行被过滤。但对用户 2 来说2 2是 TRUETRUE OR UNKNOWN结果是 TRUE这行保留。所以 IN 遇到 NULL表现是部分正确——能匹配上的具体值行还在但集合里如果只有 NULL比如WHERE user_id IN (SELECT ...)的子查询结果是个单一 NULL那照样查不出任何行。真正的差异在 NOT IN 这侧更极端一旦集合里出现一个 NULL所有行的排除判断都陷入 UNKNOWN整体直接全灭。为什么会不对称因为等于还有机会通过其他 OR 分支碰到 TRUE而不等于的每一支都必须贡献 TRUE其中一支是 UNKNOWN整个 AND 就永远成不了 TRUE。三种写法的行为对比如下查询写法子查询集合含 NULL 时的行为是否推荐x IN (子查询)会保留能匹配上具体值的行但集合只有 NULL 时照样空集行为偏保守有条件使用x NOT IN (子查询)集合含 NULL 时整个查询几乎必然返回空集不推荐NOT EXISTS (相关子查询)逐行判断是否存在一条记录满足匹配NULL 不参与匹配行为完全符合直觉强烈推荐2.4 哪些时候 NOT IN 其实还能用不是说 NOT IN 这个语法本身该死而是它有个隐含前提参与比较的集合里不能有 NULL。同时满足下面任一条件时NOT IN 还是安全的子查询的列在表结构上声明了NOT NULL数据库层面保证不会出现 NULL子查询里显式过滤了空值比如select banned_user_id from blacklist where banned_user_id is not null集合来自字面量你确认里面没有 NULL。但问题在于这些前提在写代码时容易成立运行几个月后却不一定会保持——同事往表里导了个新数据源、上游接口字段变成了空、清洗逻辑改了一行NULL 就溜进来了。所以我个人的习惯是涉及排除某集合的语义一律用 NOT EXISTS 打底不给自己留隐患。3. 连锁反应不止 NOT INWHERE、JOIN、聚合函数里的同类黑洞看明白三值逻辑之后你会发现 NOT IN 只是冰山一角。NULL 的未知传染性会在 SQL 的各个角落里默默改写你的结果。3.1 最经典的三句话 NULL、 张三、CASE WHEN几乎每个新手都写过where name null然后对着空结果发呆。这个错误很好解释name NULL的结果是 UNKNOWN不是 TRUEWHERE 当然不放行。正确写法是where name is null。比这个更隐蔽的是排除某个值时的反直觉行为。比如select user_id, user_name from users where user_name 张三;你想排除叫张三的人结果发现名字没填的那些人user_name 为 NULL也一起消失了。为什么因为NULL 张三的结果是 UNKNOWN不是 TRUE。你的本意是不是张三的都给我但数据库拿到的判断是这行到底是不是张三我不知道——不知道那就别放行。这个 bug 在真实业务里特别常见。比如筛选非注销用户、排除特定渠道来源的订单如果目标列存在空值往往会把不该丢的数据一并丢掉。排查思路和 NOT IN 完全一致先问一句这个比较列里有没有 NULLCASE WHEN 同理case when remark null then 无备注 else remark end这段永远走不到无备注分支因为remark null永远是 UNKNOWN。必须写成remark is null。3.2 JOIN 关联键上的 NULL匹配不上还被静默处理连接操作里 NULL 同样制造困惑。INNER JOIN的关联条件a.user_id b.user_id只要两边任意一边是 NULL比较结果就是 UNKNOWN这一行不会进入结果集。如果你的关联键存在空值JOIN 之后行数会悄悄变少而且没有警告。LEFT JOIN则表现得更微妙左表某行的关联键是 NULL它不会被匹配到任何右表行但因为 LEFT JOIN 会保留左表所有行所以这行还会出现在结果集里只是右表的字段全是 NULL。很多时候你以为关联上了其实并没有给下游造成的错觉甚至比 INNER JOIN 丢行更危险。对账、数据同步场景里还有个经典需求想判断两条记录是否一致NULL和NULL应该视为相同但普通的等值判断NULL NULL结果是 UNKNOWN匹配不上。这时候可以用 NULL-safe 的等值判断-- PostgreSQL、SQL Server 2022 等支持 IS NOT DISTINCT FROM select a.id from table_a a full join table_b b on a.id is not distinct from b.id where a.id is null or b.id is null or a.val is distinct from b.val;如果用的 MySQL对应的是运算符。这类运算符把两个 NULL 当成相等专门用于未知对未知的对账场景。日常开发可能用不上但一旦遇到数据比对的需求你会庆幸知道它。3.3 聚合函数的口径陷阱COUNT、SUM、AVG 各自的理解聚合函数对 NULL 的处理各不相同做报表的人最容易在这里栽跟头。我把最常见的差别整理成一张表函数对 NULL 的处理容易踩的坑COUNT(*)统计所有行不管字段是否 NULL业务想统计非空数量时口径偏大COUNT(col)只统计该列非 NULL 的行空值一多结果远小于预期SUM(col)忽略 NULL 行如果全部是 NULL返回 NULL直接拿去展示被当成 0 或者报错AVG(col)忽略 NULL 行分母是非空行数缺失值没有被计入分母指标虚高举一个真正发生过的业务例子。团队统计客户平均消费额SQL 直接写select avg(amount) from orders;结果人均消费高得离谱。原因很直接很多客户根本没下过单他们不在 orders 表里而在 orders 表里但amount是空值的记录又被 AVG 跳过了。这个平均只在有消费金额的人里面计算分母天然偏小。如果你想让没消费按 0 参与统计必须显式翻译select avg(coalesce(amount, 0)) from orders;COALESCE的作用是在 NULL 出现时替换成你指定的默认值。用什么默认值取决于业务语义——NULL 到底是该为 0还是根本不该参与你要在写 SQL 的时候就想清楚而不是让数据库替你决定。3.4 CASE WHEN 和 ORDER BY 的隐藏行为CASE WHEN 除了前面说的 NULL写错之外还有一个容易忽略的点如果所有分支都没有匹配且没有写 ELSE那么 CASE 表达式的结果就是 NULL。这个 NULL 再传给下游计算又会引发新一轮传染。所以写 CASE 时我通常会习惯性补一个else 默认值把未知兜底这句显式写出来。排序也值得关注。ORDER BY对 NULL 的位置不同数据库默认行为并不一致而且常常和直觉相反数据库ASC 时 NULL 的位置DESC 时 NULL 的位置MySQL最前最后SQL Server最前最后PostgreSQL最后最初Oracle最后最初如果你的报表要求空值永远排在最后直接用默认行为是不可靠的跨数据库还会打架。标准 SQL 提供了显式控制order by create_time asc nulls last;写清楚NULLS FIRST还是NULLS LAST无论在哪个数据库上行为都一致。这也是我在多数据库项目中坚持的写法。3.5 去重与分组所有 NULL 会被归成一类最后说一个跟去重强相关的坑GROUP BY会把所有 NULL 归到同一个组里SELECT DISTINCT对可空字段也只输出一个 NULL。这个特性有好有坏。好的方面按地区分组统计时未填地区的记录会归成未知地区一组你至少看得到数据。坏的方面如果你以为没填和填了不同值是同一层面的事就容易误判。比如用ROW_NUMBER() OVER (PARTITION BY 某可空字段 ORDER BY ...)做去重所有 NULL 会进同一个分区每批 NULL 之间按窗口内规则互相竞争名次可能让本该保留多条的历史数据只留下一条。做数据清洗时这类分组归并行为一定要提前想到。4. 一份完整的排查复盘从报表变空到定位根因的实操链路理论说了一堆回到开头的真实事故我把当时的排查过程完整复盘一遍。以后你碰上结果少了、空了、口径不对这类问题可以直接照着走。4.1 第一步先排除数据本身没了的嫌疑拿到报表全空的反馈先别急着读业务逻辑。第一步永远是用最小代价确认数据源是否正常select count(*) from users; -- 用户表总行数确认源数据还在 select banned_user_id from blacklist; -- 直接看子查询的集合内容 select count(*) from blacklist where banned_user_id is null; -- 专门扫空值我当时就是在第二步看见结果集里除了2还有一行NULL心里基本就有了判断。这个直接看子查询输出的动作成本极低却能把问题锁定到极小范围。4.2 第二步用 NOT EXISTS 做对照实验边缘情况再怎么推理都不如一个对照实验实在。把 NOT IN 等价改写成 NOT EXISTS跑一遍看结果select user_id, user_name from users u where not exists ( select 1 from blacklist b where b.banned_user_id u.user_id );十几秒内返回了三行小明、小刚、小丽。与空集形成鲜明对比说明数据完全没问题就是谓词逻辑在 NULL 面前失效了。到这一步根因已经 90% 锁定。4.3 第三步继续下钻定位是哪一个 NULL为了把原理和现象彻底对上我还做了一组二分实验-- 实验 A子查询过滤掉 NULLNOT IN 恢复 select user_id, user_name from users where user_id not in ( select banned_user_id from blacklist where banned_user_id is not null ); -- 实验 B给集合额外塞一个 NULL观察是否再次翻车 insert into blacklist values (null); -- 重新跑最原始的 NOT IN 查询实验 A 返回三行实验 B 加上 NULL 后又变回空集。到这里NULL 是触发条件这个结论已经没有任何疑问了。这个二分法以后可以一直用把可疑元素从集合中移除再放回去看结果是否在两个状态之间切换很快就能锁定真凶。4.4 第四步把教训固化到日常防线排查完之后真正值钱的是如何防止它再次发生。我在团队里做了三件事代码审查清单凡是出现NOT IN、 NULL、 常量这类写法审查时必须确认目标列无 NULL或者显式做了空值处理。数据质量监控对参与排除、连接、统计口径的可空字段建一个空值率监控任务。比如每天统计blacklist.banned_user_id is null的行数和占比超过阈值告警。回归测试 SQL把这次事故的查询固化成一个测试用例。以后改表结构、改数据导入逻辑时先跑这个用例确保 NOT EXISTS 版本和 NOT IN 版本的结果一致。这三条看着笨但数据问题本来就是防大于修。NULL 不会报错它只会安静地污染结果你必须有机制在污染发生前就叫停。5. 防御性写法的优先级排序让 NULL 出现在它该在的地方前面讲了很多为什么最后落回到怎么写。我按推荐优先级给出一套实践顺序你写排除类查询时直接照着选。5.1 第一优先NOT EXISTS涉及排除某集合的语义我会默认写 NOT EXISTSselect user_id, user_name from users u where not exists ( select 1 from blacklist b where b.banned_user_id u.user_id );它逐行检查是否存在一条黑名单记录与当前用户匹配NULL 永远无法等于某个具体用户 ID所以 NULL 行不会参与匹配结果完全符合业务直觉。另外select 1只是标记存在性写select *也可以优化器通常会把这类子查询转换成 anti-join 或半连接配合索引性能不一定比 NOT IN 差。5.2 第二优先LEFT JOIN ... IS NULL第二种常用写法是 LEFT JOIN 后找没匹配上的行select u.user_id, u.user_name from users u left join blacklist b on u.user_id b.banned_user_id where b.banned_user_id is null;注意一个额外陷阱如果黑名单表在banned_user_id上存在重复数据LEFT JOIN 会按重复行把用户表行复制多份最后结果行数虚高。所以这种写法最好配合去重select u.user_id, u.user_name from users u left join ( select distinct banned_user_id from blacklist ) b on u.user_id b.banned_user_id where b.banned_user_id is null;这也是为什么我在团队里会更倾向 NOT EXISTS——它天然对重复数据不敏感少一个隐患。5.3 第三优先显式翻译 NULL 的业务语义有些场景没法绕开 NULL必须在语义层面把它翻译成业务认可的值。常用工具是COALESCE——它接受多个参数从左到右返回第一个非 NULL 值以及 MySQL 的IFNULL、SQL Server 的ISNULL。-- 统计口径NULL 记为 0 select user_id, sum(coalesce(amount, 0)) as total_amount from orders group by user_id; -- 展示层NULL 翻译成阅读友好的文本 select user_name, coalesce(remark, 无备注) as remark from users;用哨兵值替代的时候要格外小心。比如有人会写coalesce(banned_user_id, -1)配合 NOT IN思路是把 NULL 换成不可能出现的 -1但如果哪天业务里真的出现了 -1 或者负数 ID碰撞就会制造新的错误。我的建议是能过滤 NULL 就过滤能换写法就换写法哨兵翻译只在聚合和展示层使用不要在排除逻辑里赌一个永远不会碰撞的值。5.4 建表阶段消灭野生 NULL最根治的办法是让 NULL 根本没机会进入关键字段。建表时如果某个字段的业务语义是必须有值就直接加约束create table orders ( order_id bigint primary key, user_id bigint not null, amount decimal(12,2) not null default 0, check (amount 0) );NOT NULL加DEFAULT的组合能在源头挡住很多空值事故。对于确实允许未知值的字段我更建议用显式的状态枚举来表达未知而不是留给 NULL。比如客户地区未知与其让字段是 NULL不如用一个业务字段region_code UNKNOWN配合注释。这样做的好处是所有下游 SQL 对未知的处理有一个明确的业务语义而不是依赖数据库三值逻辑去猜。5.5 别忘了索引和性能的隐性影响最后补充一个性能角度的提醒。有些开发者为了规避 NULL习惯写where coalesce(col, ) 来筛选空值但函数包一层字段之后普通 B-tree 索引通常就失效了大表查询会退化成全表扫描。更合理的写法是把它拆成等价的显式条件-- 不推荐函数包裹导致索引失效 where coalesce(status, ) -- 推荐等价语义尽量保持字段裸用 where status is null or status 如果某个列绝大部分是 NULL只有少量行有值你真正关心的往往是那些有值的行。这时候可以在 PostgreSQL 里建部分索引create index idx_orders_amount_not_null on orders(amount) where amount is not null;MySQL 5.7 则可以用函数索引解决类问题。总之NULL 不仅影响逻辑正确性还会影响执行计划。写法上对 NULL 的处理越原生优化器越容易帮你生成好的执行计划。做 SQL 多年我越来越觉得和 NULL 打交道拼的不是技巧而是纪律。把这些细节定成团队规范写进 checklist比临时抱佛脚查文档靠谱得多。最后分享一个我常用的土办法凡是想不通结果为什么少了的查询先把所有可疑字段的空值率扫一遍十有八九黑洞就藏在那里。