not exists 是最可靠、最易被优化器转为高效反连接的写法，因其语义明确、不依赖右表字段是否允许 null，能稳定触发 hash join anti 或 nested loops anti，而 left join + is null 易因条件位置错误或 null 问题失效，not in 则存在语义陷阱和性能风险。
NOT EXISTS 是最可靠、最易被优化器转为高效反连接（Anti Join）的写法，尤其在 MySQL 8.0+、PostgreSQL 和 Oracle 中。它不依赖右表字段是否允许 NULL，语义明确，且能稳定触发 HASH JOIN ANTI 或 NESTED LOOPS ANTI 执行计划。
为什么 NOT EXISTS 比 LEFT JOIN + IS NULL 更稳？
关键在 NULL 处理和条件位置：
-
LEFT JOIN ... ON a.id = b.a_id WHERE b.a_id IS NULL要求b.a_id是外键且定义为NOT NULL；若该字段允许 NULL（比如没加约束或历史数据脏），IS NULL会误筛掉本该匹配却因右表 NULL 值失败的左行 -
NOT EXISTS子查询里只判断“是否存在满足b.a_id = a.id的记录”，完全绕过 NULL 传播问题 - 业务过滤（如
b.status = 'cancelled'）必须写进子查询WHERE，否则会被当成左表过滤条件，逻辑错误
怎么写才能让优化器真正用上 Anti Join？
三个硬性前提缺一不可：
- 子查询必须含相关列引用，例如
WHERE o.user_id = u.id，且o.user_id上有索引（最好是联合索引，如orders(user_id, status)） - 主查询不能包着聚合、
DISTINCT、ORDER BY或嵌套外连接——这些结构会阻止子查询展开（subquery unnesting） - 统计信息要准；如果右表行数被低估，优化器可能放弃反连接，退化为逐行
FILTER扫描
验证方式：执行 EXPLAIN FORMAT=TREE，看到 ANTI JOIN 或 NESTED LOOPS ANTI 节点才算成功。
LEFT JOIN + IS NULL 怎么避免翻车？
它调试直观，但极易因条件放错位置而失效：
- 右表的业务条件（如时间范围、状态）必须写在
ON子句，不是WHERE—— 错放会导致左表行被提前过滤掉 - 判空字段必须是右表的关联外键（如
o.user_id），而不是主键（如o.id），因为主键不可能为 NULL - 确保
o.user_id字段有索引；否则即使语法对，也会退化成嵌套循环全表扫描
示例错误写法：SELECT u.* FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE o.user_id IS NULL AND o.created_at >= '2026-05-26' —— 这里 o.created_at 放 WHERE 直接让所有左表行失效，正确位置应在 ON 后追加。
绝对要绕开 NOT IN
它在语义和性能上双重危险：
- 只要子查询返回任意一个
NULL（比如外键字段允许为空、或用了SELECT DISTINCT col FROM t WHERE ...未加IS NOT NULL），整条查询结果就是空集 - 优化器几乎无法为其生成反连接计划，通常走哈希/排序合并，大数据量时性能断崖下跌
- 即使手动加
WHERE col IS NOT NULL，执行计划仍大概率无法利用索引，不如直接换NOT EXISTS
真正需要排除固定 ID 列表（如 (1,2,3)）时，NOT IN 可用；但凡涉及子查询，一律换成 NOT EXISTS。
实际项目中，NOT EXISTS 的写法看似多几行，但省去了排查 NULL、调索引、看执行计划是否真用了 Anti Join 的大量隐性成本。最容易被忽略的是：右表索引必须以关联字段为前导列，且统计信息需定期更新——否则再标准的写法也跑不快。
