SQL 实战指南 · MySQL 8.0

NOT IN 遇到 NULL：为什么结果突然为空

用三值逻辑解释 NOT IN 空结果，并为反连接选择可靠的 NOT EXISTS 写法。

问题与判断方法

NOT IN 会把左侧值与集合中的每个值比较。只要子查询返回一个 NULL，且左侧值没有命中任何确定值，组合条件就会变成 UNKNOWN；WHERE 只保留 TRUE，因此看似应该保留的行也会全部消失。

这不是数据库随机行为，而是 SQL 三值逻辑的结果。x NOT IN (1, NULL) 等价于 x <> 1 AND x <> NULL，第二个比较永远不是 TRUE。测试数据没有 NULL、生产数据出现 NULL 时，问题才会突然暴露。

如果业务目标是“找出没有关联记录的对象”，NOT EXISTS 通常表达得更直接。相关条件放在子查询内部，数据库逐个检查左侧记录是否存在匹配，不会被其他无关记录中的 NULL 污染。

若坚持使用 NOT IN，应让子查询字段具有可信的 NOT NULL 约束，或在子查询中显式排除 NULL。应用拼接空列表时也要单独处理，因为 NOT IN () 在 MySQL 中是语法错误，不能把空数组直接展开。

测试至少覆盖：子查询含 NULL、子查询为空、左侧字段为 NULL、存在重复键，以及有匹配和无匹配两类记录。只有把这些边界写成测试，反连接才不会随数据变化失效。

表名：customers

表名：orders

SELECT c.id, c.name FROM customers c WHERE c.id NOT IN (SELECT customer_id FROM orders UNION ALL SELECT NULL) ORDER BY c.id;

result
id	name

SELECT c.id, c.name FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id) ORDER BY c.id;

result
id	name
103	Cindy

为什么这样更好：NOT IN 集合中的一个 NULL 让所有未命中比较变为 UNKNOWN；NOT EXISTS 仍能找出没有订单的 Cindy。