MySQL NOT IN 空值排障：与 NOT EXISTS、反连接逐项对照

一份排除名单里混进一个 NULL，NOT IN 查询就可能突然一条结果也没有。把 NOT 改来改去通常无济于事，因为问题不是排除条件少写了一层括号，而是“与未知值不相等”无法被判定为 TRUE。

先别急着把 SQL 统一换成 NOT EXISTS。要先说明外层字段为空时算不算候选、排除名单中的空值是否合法，以及两个空值是否有业务上的同一性。没有这份约定，三种看起来等价的反连接写法，实际会返回不同对象。

一条空值怎样让排除查询失去确定性

下面使用 MySQL 8.4、InnoDB 和独立实验库。email 仅作为可空的标识样本，暂不引入唯一约束和大小写归一化。

CREATE DATABASE anti_null_lab;
USE anti_null_lab;
CREATE TABLE candidate (
    id BIGINT NOT NULL PRIMARY KEY,
    email VARCHAR(100) NULL,
    KEY idx_email (email)
) ENGINE=InnoDB;
CREATE TABLE blocked_address (
    id BIGINT NOT NULL PRIMARY KEY,
    email VARCHAR(100) NULL,
    KEY idx_blocked_email (email)
) ENGINE=InnoDB;

INSERT INTO candidate VALUES
    (1, 'alice@example.test'),
    (2, 'bob@example.test'),
    (3, NULL);
INSERT INTO blocked_address VALUES
    (10, 'bob@example.test'), (11, NULL);

SELECT id, email FROM candidate
WHERE email NOT IN (SELECT email FROM blocked_address);

对 Alice，条件相当于“不是 Bob，并且也不等于那个未知地址”。前一项可以判断，后一项是 UNKNOWN，组合后不能满足 WHERE 所要求的 TRUE。对 Bob，已知命中名单；对空地址，普通比较同样无法形成预期判断。因此结果为空并不表示所有候选都真的在排除名单中。

这个例子不应该被描述成 NULL 是某个隐藏字符串。NULL 表示未知或缺失，它与普通值的比较不会自动产生正常真假。需要判断空值本身时，应使用 IS NULL 或明确的空值安全比较。

第一种修正：名单只接受已知标识

若业务约定空地址不能参与排除，先从子查询中剔除：

SELECT id, email
FROM candidate
WHERE email IS NOT NULL
  AND email NOT IN (
      SELECT email FROM blocked_address WHERE email IS NOT NULL
  );

这里显式排除了候选空地址，表示“可处理候选必须有已知地址”。在这份样本中，应只保留 Alice。外层 IS NOT NULL 是业务条件，不是为了讨好优化器。

为什么内外两层都写？因为过滤名单中的 NULL，解决的是未知比较污染；过滤外层 NULL，解决的是候选本身是否具备有效标识。它们承担不同职责，不能删掉其中一个后仍声称语义完全相同。

长期来看，如果 blocked_address 的空地址没有任何合法含义，最好在写入入口与字段约束上拒绝它。历史空值如何处理仍需根据来源决定，不能为了让 ALTER 成功就随意填成空字符串；空字符串也是一个具体值，可能制造另一种错误匹配。

第二种修正：表达不存在相等行

SELECT c.id, c.email
FROM candidate AS c
WHERE c.email IS NOT NULL
  AND NOT EXISTS (
      SELECT 1
      FROM blocked_address AS b
      WHERE b.email = c.email
  );

这条查询直接表达“找不到与候选地址相等的排除记录”。名单里的 NULL 不会与 Alice 形成相等行，因此不会像 NOT IN 那样让她被未知判断挡住。

但如果去掉外层 IS NOT NULL，空地址候选也会留下：没有任何 b.email 通过普通等号与 NULL 匹配。它与前一种带业务非空条件的查询，结果当然不同。NOT EXISTS 并不自动知道“空候选不允许进入后续流程”。

同样，不要以为写 SELECT 1 就是在数一行。EXISTS 关心是否存在满足条件的记录，子查询返回列的展示值不是这里的业务数据；优化器可据此安排访问路径。

第三种修正：LEFT JOIN 需要用可靠的匹配标记

SELECT c.id, c.email
FROM candidate AS c
LEFT JOIN blocked_address AS b ON b.email = c.email
WHERE c.email IS NOT NULL AND b.id IS NULL;

这里用 b.id 为空判断没有匹配，因为它是非空主键。不要看到任何一个右侧字段为空就认定没有匹配；如果那个字段本身可以为空，真实匹配行也可能满足这条判断。

在本例普通等值连接里，email 的空值不匹配，因此用 email 似乎也能工作。危险在于以后连接条件改成另一列、或改为允许空值相等，原判断就可能失效。非空主键作为存在性标记，含义更稳定。

名单重复记录也值得测试。普通 LEFT JOIN 会生成多个匹配行，最终反连接过滤将它们排除；但若把相同连接结构改成普通展示或计数，重复就可能放大结果。只做存在性判断时，NOT EXISTS 通常更直接表达意图。

名单为空时，外层空值也要有固定规则

可以在事务里暂时清空名单，比较不带外层过滤的 NOT IN：

START TRANSACTION;
DELETE FROM blocked_address;
SELECT id, email FROM candidate
WHERE email NOT IN (SELECT email FROM blocked_address);
ROLLBACK;

空子查询与“包含一个 NULL 的子查询”完全不同。前者没有任何排除对象，后者包含无法与普通值确定比较的元素。若业务要求候选地址始终非空，就应把外层条件明确写出来，避免结果随名单从空变非空而出现不符合产品直觉的变化。

测试不能只有“命中与未命中”两类。至少保留候选空值、名单空值、空名单和名单重复，这几种边界才能检查语义有没有被偶然数据掩盖。

对多列行值 NOT IN，空值组合还会更加难以凭直觉判断。多个比较分量一起决定结果，不应把单列规则机械复制后就称已验证；更容易维护的方式通常是按业务键写明确相关条件，并给每个可空分量定义比较语义。

如果业务真的要求把两个空值当成同一类

MySQL 的空值安全相等操作符 <=> 可以明确表达这种比较：

SELECT c.id, c.email
FROM candidate AS c
WHERE NOT EXISTS (
    SELECT 1 FROM blocked_address AS b
    WHERE b.email <=> c.email
);

这时名单里的空值可以匹配空候选，因此它会被排除。这个选择不表示两个未知邮箱实际上相同，只表示业务主动把“缺失地址”当成同一个分类处理。只有规则确实如此时才使用，不应为了让一条测试通过就替换所有等号。

如果只是清理脏数据，最直接的是将异常记录隔离并修正来源；用空值安全比较改变整个查询含义，可能把一个数据质量问题扩散成新的业务规则。

应用传来的空数组，不等于数据库里的空子查询

前面的空名单实验使用合法子查询，它可以返回零行。应用若根据一个空数组直接拼出 NOT IN ()，面对的则是语法问题，不是相同的集合语义。某些工具为避免语法错误把它改成 NOT IN (NULL)，又会得到完全不同的过滤结果。

因此，动态条件生成必须明确定义空数组：是没有排除项、拒绝请求，还是不启用这段筛选。不能让 ORM 的占位符补偿策略替产品作决定。用于包含查询的空数组通常还需要另一种语义，别把 IN 与 NOT IN 的空集合处理复制成同一段分支。

测试应直接核对最终生成 SQL 和绑定参数。只在内存中断言数组为空，无法发现工具把空数组改成一个 NULL 占位符的情况。这个边界一旦固定，前面关于空名单的数据库推导才能准确对应到接口行为。

正确性确认后，再比较访问成本

NOT EXISTS、NOT IN 与 LEFT JOIN 在某些条件下可以由优化器转成相近的反连接执行方式，但可空表达式与具体查询结构会影响可用优化。不能仅看 SQL 关键字，就宣称某种写法永远快于另外两种。

在当前样本上先核对三条明确非空版本返回同一个 id 集合，再将代表性数据量、重复比例和索引放入对照。idx_blocked_email 为相等查找提供入口；若实际 SQL 对列包了函数、两侧类型不同，仍要重新看计划。

EXPLAIN 反映估计与选择，EXPLAIN ANALYZE 会执行并给出实际访问证据。拿一个几十行的名单测到的毫秒差距，通常不能代表大型生产数据的稳定差异，更不应该用虚构倍数为重写背书。

最终保留的 SQL 应让后来维护者一眼知道：外层空地址怎么处理，名单空值怎么处理，是否允许缺失值相等。把这些条件写清楚，比靠约定“这里永远不会有 NULL”更经得起导入和迁移。