NOT IN 的 NULL 陷阱是什么

NOT IN 对包含 NULL 的集合几乎总是返回空结果：因为 x <> NULL 求值为 UNKNOWN，而 WHERE 只保留 TRUE 的行。这个陷阱在子查询里更隐蔽——只要子查询返回的列里存在任意一个 NULL，整条语句的结果就为空，且不报任何错。推荐的反连接写法是 NOT EXISTS，它与 NULL 无关，语义也更直白。如果必须用 NOT IN，先在子查询里加 WHERE col IS NOT NULL 把空值排除掉。

基本信息

典型使用场景

• 排查查询莫名无结果：条件里出现 NOT IN 时它是第一个怀疑对象
• 求差集：找出 A 表有而 B 表没有的记录，改用 NOT EXISTS
• 黑名单过滤：判断用户不在黑名单里，注意黑名单列可能为空
• 白名单校验：白名单列可空时必须先排除 NULL 再比较
• SQL 评审清单：把 NOT IN 加子查询列为重点复查写法
• 迁移验证：从其他数据库迁到 PostgreSQL 后核对行为一致性

复现与改写

SELECT * FROM t WHERE id NOT IN (1, 2, NULL);   # 结果为空

SELECT * FROM t
WHERE id NOT IN (SELECT user_id FROM blacklist);
-- blacklist.user_id 只要有一个 NULL，整条语句结果就为空

-- 改写一：NOT EXISTS，不受 NULL 影响
SELECT * FROM t
WHERE NOT EXISTS (
    SELECT 1 FROM blacklist b WHERE b.user_id = t.id
);

-- 改写二：先用 IS NOT NULL 排除空值
SELECT * FROM t
WHERE id NOT IN (SELECT user_id FROM blacklist WHERE user_id IS NOT NULL);

反连接写法对照

排查与改写要点

• 看到 NOT IN 先看内层列是否可空，这是判断风险的第一步
• 内层结果可能很大时用 NOT EXISTS，语义更稳且优化器处理更好
• 改写后对比行数：改前为空、改后有数据，基本可以确认是 NULL 引起的
• LEFT JOIN 加 IS NULL 写法要判断右表非空列，用主键最保险
• 把这条规则写进团队 SQL 规范，评审时统一拦截
• 给可空列补 NOT NULL 约束或默认值，从建模阶段减少隐患

常见误用

• NOT IN 的子查询列可空，结果整体为空且不报错
• 用 NOT IN 求差集，数据量一变结果就变，很晚才发现语义问题
• 把 NOT IN 改成 LEFT JOIN 加 IS NULL 时判断列选错，结果与预期正好相反
• 不看执行计划就改写，内表没有索引时新写法反而更慢
• 对可空列判空时用 <> 而不是 IS NULL，条件永远不成立
• 把空结果当成业务本来就没有数据，长期未发现查询逻辑错误