SQL 反连接是什么？NOT EXISTS、LEFT JOIN IS NULL 和 NOT IN 怎么选？

简化版

反连接用于查询“在 A 中存在，但在 B 中不存在”的数据。常见写法有 NOT EXISTS、LEFT JOIN ... IS NULL 和 NOT IN。一般推荐优先使用 NOT EXISTS，语义清晰且对 NULL 更安全；LEFT JOIN IS NULL 也常用；NOT IN 遇到子查询结果包含 NULL 时可能返回意外结果。

详细版

例如要查没有下过单的用户，本质就是用户表反连接订单表。NOT EXISTS 会对每个用户判断是否不存在匹配订单；LEFT JOIN IS NULL 先左连接，再筛选右表为空的行；NOT IN 则判断用户 id 不在一个集合里。

三者在很多数据库里可能被优化成相似执行计划，但语义细节不同。最大坑点是 NOT IN 和 NULL：如果子查询集合中出现 NULL，三值逻辑会让比较结果变成 UNKNOWN，最终可能查不出任何行。

面试要能写出 SQL，也要解释为什么 NULL 会影响 NOT IN。

完整版教学

一、反连接的业务语义

查没有订单的用户。

查没有绑定角色的账号。

查没有库存记录的商品。

查未完成某步骤的流程实例。

这些都属于“主表中找不到匹配记录”。

二、NOT EXISTS 写法

SELECT u.*
FROM users u
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.user_id = u.id
);

这段 SQL 的语义是：不存在任何一条订单属于当前用户。

SELECT 1 只是占位，数据库关注是否存在匹配行。

三、LEFT JOIN IS NULL 写法

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

它先保留所有用户，再把没有匹配订单的用户筛出来。

筛选的列要选择右表中用于匹配且不会无故为 NULL 的列。

四、NOT IN 的 NULL 问题

SELECT *
FROM users
WHERE id NOT IN (SELECT user_id FROM orders);

如果 orders.user_id 中有 NULL，id NOT IN (...) 的判断可能变成 UNKNOWN。

SQL 的 WHERE 只保留 TRUE，不保留 UNKNOWN。

所以结果可能为空或不符合预期。

五、三种写法对比

写法	优点	风险
`NOT EXISTS`	NULL 安全，语义清晰	相关子查询写法稍长
`LEFT JOIN IS NULL`	直观，常见	条件放错会变成错误结果
`NOT IN`	简短	子查询含 NULL 时危险
`EXCEPT`	集合语义清晰	兼容性和去重语义要注意

六、性能和索引

反连接通常需要右表关联列有索引。

例如 orders(user_id)。

没有索引时，数据库可能需要大量扫描。

现代优化器常能把 NOT EXISTS 改写为 anti join。

但复杂条件、函数表达式、类型转换会影响优化。

面试里不要只背写法，最好补一句“右表匹配列要建索引，并检查执行计划”。

七、误区和追问

误区：NOT IN 和 NOT EXISTS 永远等价。 子查询含 NULL 时，NOT IN 的结果可能完全不同。
误区：LEFT JOIN 后筛右表任意列 IS NULL 都可以。 应选择能代表匹配是否存在的非空关联列。
误区：反连接一定很慢。 有合适索引和优化器改写时可以很高效。
追问：为什么 NOT IN 遇到 NULL 危险？ 因为 SQL 三值逻辑中比较可能得到 UNKNOWN，WHERE 不会保留 UNKNOWN。
追问：查没有订单但用户状态正常怎么写？ 用户状态条件放主查询，订单匹配条件放子查询或 ON 中。
追问：EXCEPT 能不能做反连接？ 可以表达集合差，但要注意去重、字段一致和数据库支持。

八、面试收束

回答时优先给 NOT EXISTS。

再说明 LEFT JOIN IS NULL 的可替代性和 NOT IN 的 NULL 风险。

最后补上索引和执行计划，这样答案比较完整。

简化版

详细版