如何用LEFT JOIN和IS NULL筛选出仅存在于A表且不存在于B表的数据？

本文共计1043个文字，预计阅读时间需要5分钟。

直接使用 `LEFT JOIN 连接 A 表和 B 表，再用 `WHERE B.id IS NULL` 筛选 A 表中独有的记录。这是 SQL 标准做法，兼容 MySQL、PostgreSQL、SQL Server、Oracle 等主流数据库。它不依赖于子查询优化器，执行计划稳定，尤其在 B 表有合适索引时性能优越。

常见错误是写成 WHERE B.id = NULL —— 这永远不成立，因为 NULL = NULL 返回 UNKNOWN，不是 TRUE，必须用 IS NULL。

• JOIN 条件要基于业务主键或唯一关联字段（如 ON a.user_id = b.user_id），别用模糊字段（如 name）
• 如果 B 表连接字段允许 NULL，IS NULL 会把 B 表本就为 NULL 的行也误判为“不存在”，需提前确认 B 表该字段是否定义为 NOT NULL
• 若 A 表有重复数据，结果也会重复；需要去重可加 DISTINCT，但更推荐先厘清业务语义是否允许重复

为什么不用 NOT IN？它在 NULL 场景下会失效

NOT IN 看似简洁，但只要 B 表的对比字段（如 b.id）中存在任意一个 NULL，整个查询就会返回空结果集。这是因为 a.id NOT IN (1, 2, NULL) 等价于 a.id != 1 AND a.id != 2 AND a.id != NULL，而最后一项恒为 UNKNOWN，导致整行被过滤掉。

• 即使你确定 B 表没 NULL，也建议避免 NOT IN：它无法利用索引做哈希/合并连接，通常走嵌套循环，大数据量时明显变慢
• MySQL 8.0+ 对 NOT IN 有部分优化，但行为仍不如 LEFT JOIN 可预测
• 如果非要用子查询，改用 NOT EXISTS 更安全，但语法稍冗长，且某些旧版 SQLite 不支持

实际示例：查订单表里用户不存在于用户表的记录

假设 orders 表有 user_id 字段，users 表主键是 id：

SELECT o.* FROM orders o LEFT JOIN users u ON o.user_id = u.id WHERE u.id IS NULL;

注意点：

• 别漏掉 u.id IS NULL 中的表别名 u，否则可能误用 o.user_id IS NULL，那查的是订单本身 user_id 为空的脏数据
• 如果 users.id 是自增主键，确保它建了索引（通常默认已有）；若 orders.user_id 没索引，LEFT JOIN 会很慢，建议补上
• 测试时可用 EXPLAIN 看是否走了 type: ref 或 index，避免全表扫描 B 表

LEFT JOIN 后 WHERE 和 ON 的区别常被忽略

把过滤条件写在 ON 还是 WHERE 会影响结果。例如：LEFT JOIN users u ON o.user_id = u.id AND u.status = 'active'，这时即使用户存在但 status 不是 active，u.* 字段仍为 NULL，WHERE u.id IS NULL 会把这类订单也包含进来——这往往不是你想要的“用户完全不存在”的语义。

• 真正想表达“只取 A 有、B 完全没有匹配行”的逻辑，所有对 B 表的过滤都必须放在 WHERE 子句（如 u.id IS NULL）
• ON 里只放纯粹的关联条件，不要掺杂业务状态判断
• 如果确实需要按 B 表状态过滤后再找“缺失”，应先用子查询或 CTE 把 B 表限定好，再 LEFT JOIN

LEFT JOIN + IS NULL 这条路径看着简单，但 ON 和 WHERE 的分寸、NULL 的陷阱、索引的存在与否，三者任一出问题都会让结果静默出错——它不会报错，只是悄悄少返回几条数据。