如何通过SQL的JOIN查找A表中完全不存在于B表的数据？

作者：袖梨 2026-07-07
LEFT JOIN + WHERE B.id IS NULL 是筛选A表独有记录的最可靠写法，原理是保留A表全量行、B表无匹配时字段为NULL，再显式筛选；须用B表主键或NOT NULL字段判空，避免NOT IN（NULL导致结果为空）和EXCEPT（字段约束严、不支持多字段）。

用 LEFT JOIN + IS NULL 筛出A表独有记录

直接结论：不能用 INNER JOIN 或 RIGHT JOIN，必须用 LEFT JOIN 配合 WHERE B.id IS NULL。这是最可靠、可读性最强、数据库优化器也最容易识别的写法。

原理很简单：以 A 表为左表驱动，对每行尝试匹配 B 表；若某行在 B 中找不到对应记录，B 表所有字段就全为 NULL，此时用 IS NULL 抓住它。

  • 别用 NOT IN (SELECT ...) —— 只要 B 表的关联列含任意一个 NULL，整个结果就为空（SQL 三值逻辑坑）
  • 别依赖 NOT EXISTS 的直觉写法——虽然语义正确，但某些旧版 MySQL 或低配执行计划下性能不如 LEFT JOIN
  • 确保 ON 条件里的字段类型和空值处理一致（比如 A.id 是 INT，B.a_id 是 INT UNSIGNED，可能隐式转换失败）

ON 条件写错会导致结果完全失效

ON 不是过滤条件，它是连接逻辑的定义。很多人把本该写在 WHERE 的过滤提前塞进 ON，结果查出错误数据。

例如想查 “A 表中 status=1 且不在 B 表中的记录”，错误写法：

SELECT A.* FROM A LEFT JOIN B ON A.id = B.a_id AND A.status = 1 WHERE B.a_id IS NULL

这会把 A 表中 status ≠ 1 的行也拉进来（因为 ON 中的 A.status = 1 只影响连接匹配，不筛 A 表本身）。正确写法是：

SELECT A.* FROM A LEFT JOIN B ON A.id = B.a_id WHERE A.status = 1 AND B.a_id IS NULL
  • ON 只管“怎么连”，WHERE 才管“要哪些”
  • 如果 B 表有复合主键（如 (a_id, type)），ON 必须完整匹配所有字段，漏一个就变笛卡尔积风险
  • MySQL 8.0+ 支持 LATERAL，但普通场景没必要，反而增加理解成本

NULL 值和索引让 IS NULL 判断变慢？

B.a_id IS NULL 这个判断本身不慢，慢的是没索引时扫描全表。关键不是 IS NULL，而是 B 表的关联字段有没有索引。

  • 必须给 B 表的外键字段（如 a_id）建索引，否则 LEFT JOIN 会触发 B 表全表扫描，A 表每行都扫一遍
  • IS NULL 在有索引时能走索引（B-tree 索引天然包含 NULL 位置），但前提是该字段允许 NULL；如果定义为 NOT NULL，那 IS NULL 永远不成立，查询直接返回空
  • PostgreSQL 对 IS NULL 有专门索引支持（CREATE INDEX idx ON b (a_id) WHERE a_id IS NULL），但绝大多数场景，普通索引已足够

用 EXCEPT / MINUS 会更简单？

部分数据库（PostgreSQL、SQL Server）支持 EXCEPT，Oracle 用 MINUS，写起来确实短：

SELECT id FROM A EXCEPT SELECT a_id FROM B

但它有硬伤：

  • 字段数量、类型、顺序必须严格一致，多一个字段或类型隐式转换失败就报错
  • UNION 类操作默认去重，如果 A 表有重复
id，结果会少行——而 LEFT JOIN 能保留原始重复无法在结果里同时取 A 表其他字段（比如 A.name, A.created_at），除非整行 SELECT * 再 EXCEPT，但这时要求所有字段可比，实际很难满足

所以除非你只查单字段 ID 且确定无重复，否则别为了少打几个字换写法。

相关文章

精彩推荐