前阵子做代码评审，同事指着一条 SQL 问得相当直白："这个 LEFT JOIN 明明把订单表关联上来了，后面却写 IS NULL 去过滤，那不是等于白 JOIN 吗？是不是有毛病？" 我看了一眼，这条查询长这样：
SELECT u.* FROM users u LEFT JOIN orders o ON o.user_id = u.id WHERE o.id IS NULL;它确实反直觉——正常 JOIN 的目的都是"拿右边数据"，这个却反过来把关联上的数据全部扔掉。但这就是今天要聊的"奇怪思路"：LEFT JOIN 之后用 IS NULL 当过滤条件，本质是在查"A 表里有、B 表里没有"的差集，也就是俗话说的孤儿数据。这篇文章就把这个写法的原理、和 NOT EXISTS / NOT IN 的纠葛、实际业务场景、以及高频翻车点全部拆一遍。
如果你刚接触 SQL，在团队代码里看到这种写法觉得莫名其妙，这篇文章能帮你把"先连接再丢弃"的底层逻辑捋顺；如果你已经写了几年 SQL，建议直接跳到第五节看坑位清单，重复行和 NULL 陷阱才是真正让人掉头发的地方。
1. 第一眼以为写错了：为什么要"先连接再丢弃"
先给结论：LEFT JOIN ... IS NULL 整套组合的语义，就是集合运算里的差集——取左表中在右表找不到匹配的行。它看起来绕，是因为 JOIN 给大多数人的心智模型是"把两边的数据拼起来展示"，而这个写法拼完之后立刻把拼成功的行丢了。
1.1 一个最基础的小例子
用最简单的用户下单场景。用户表 users：
| id | name |
|---|---|
| 1 | 小A |
| 2 | 小B |
| 3 | 小C |
订单表 orders：
| id | user_id | amount |
|---|---|---|
| 101 | 1 | 99 |
| 102 | 1 | 199 |
| 103 | 3 | 59 |
需求是查出"从没下过单的用户"。常规思路可能是 NOT IN，或者 NOT EXISTS，但代码里出现的是开头那条 LEFT JOIN ... IS NULL。我把表结构套进去：
SELECT u.* FROM users u LEFT JOIN orders o ON o.user_id = u.id WHERE o.id IS NULL;最终输出的只有小B。为什么？因为 LEFT JOIN 会以 users 为基准，把 orders 能匹配上的都拼上去，匹配不上的保留 users 字段、orders 字段全部置 NULL；最后一个 WHERE o.id IS NULL 把"orders 字段不为 NULL"的行过滤掉，剩下的自然是"没有订单关联"的用户。
1.2 它"奇怪"在哪：反着走的否定式思维
这个写法让人觉得别扭，核心原因是它的达成路径是反的：不是直接找"没有关联"的行，而是先把所有组合摊开，再通过否定"有关联"来筛出目标。这种"先扩大范围、再从结果里剔除"的路径，和人类直觉里的"直接找缺失"是两条完全不同的路线，所以第一眼大概率会觉得多余。
但换个角度想，这个写法恰恰是检验一个人对 JOIN 机制理解程度的试金石。只要你脑子里有"LEFT JOIN 会对没匹配上的右表列填 NULL"这个画面，IS NULL 过滤就一点也不奇怪，它只是在问：哪些行在右表里根本没有家？理解了这一点，后面几节的进阶内容才有意义。
2. 拆开引擎盖子看 LEFT JOIN + IS NULL 到底在做什么
光知道"能用"还不够，要把这个写法用得稳，必须知道引擎内部到底跑了个什么流程。
2.1 LEFT JOIN 的执行过程还原
LEFT JOIN 的官方语义是：以左表为基准，逐行拿左表的关联键去右表找匹配；找到了就拼接右表字段，找不到就保留左表这一整行，同时右表所有列填 NULL。把上面例子展开，LEFT JOIN 之后的中间结果是这样的：
| u.id | u.name | o.id | o.user_id | o.amount |
|---|---|---|---|---|
| 1 | 小A | 101 | 1 | 99 |
| 1 | 小A | 102 | 1 | 199 |
| 2 | 小B | NULL | NULL | NULL |
| 3 | 小C | 103 | 3 | 59 |
注意小A因为有两笔订单，在中间结果里出现了两行。这个细节我先按下不表，第五节专门讲它翻车的方式。现在只看 WHERE o.id IS NULL 这一步：它会保留 o.id 为 NULL 的那一行，也就是小B。也就是说，这个写法的本质是利用 LEFT JOIN"保左缺右"的特性，把"没匹配上"这件抽象的事，转化成"右表字段全是 NULL"这件肉眼可见的事，再用 NULL 过滤把匹配上的行剔除。
2.2 为什么判断条件通常选主键而不是随便一列
我刚学这个写法时犯过一个典型错误：把过滤条件写成 o.user_id IS NULL，甚至 o.amount IS NULL，结果在这个例子里碰巧能跑对，但完全经不起推敲。原因很简单：一旦 orders 表里存在 user_id 本身为 NULL 的脏数据，或者某个字段的可空性发生变化，判断依据就失效了。
最稳的做法，是拿右表的主键或者非空唯一键作为过滤条件。理由如下：
- 主键在表结构上就不允许为 NULL，所以只要该列为 NULL，唯一可能的解释就是"LEFT JOIN 没匹配上"；
- 主键唯一，不会因为选了存在重复值的字段产生额外歧义。
| 过滤列选择 | 安全性 | 说明 |
|---|---|---|
| 右表主键 | 最稳 | 不可能为 NULL，未匹配是唯一解释 |
| 右表非空唯一键 | 很稳 | 同上，前提是确实声明了 NOT NULL |
| 右表普通非空字段 | 看情况 | 字段本身允许 NULL 时就会误判 |
| 右表可空字段 | 危险 | 业务空值和"未匹配"完全混在一起 |
2.3 一个容易记住的类比
我后来跟同事解释这个写法，喜欢用"连连看贴标签"来打比方：LEFT JOIN 就像每张左表卡片到右表池子里找配对，成功配对的卡片被贴上一张"右表主键贴纸"，没配上的什么都不贴。IS NULL 过滤干的事，就是只挑出那些没贴到贴纸的卡片。判断一张卡片有没有配对成功，当然要看"主键贴纸"贴没贴上，而不是盯着卡片角落某个可有可无的备注字段——这就是上一小节那个主键原则的直观版本。
3. 和 NOT EXISTS、NOT IN 放在一起比，差距比想象中大
同一个差集需求，市面上最常见的是三种写法。把它们放在一起看，才能明白这个"奇怪思路"到底有什么存在价值。
3.1 三兄弟的对比表
| 写法 | NULL 处理 | 重复行影响 | 可读性 | 优化器常见表现 |
|---|---|---|---|---|
| NOT IN | 子查询结果含 NULL 时，整个结果集变空 | 无 | 表面直观，实际容易误用 | 部分版本优化一般 |
| NOT EXISTS | 天然安全 | 无 | 最直观 | 多数版本可优化为反连接 |
| LEFT JOIN ... IS NULL | 过滤列选主键时安全 | 右表关联键重复会放大中间结果 | 需要先连接再丢弃，脑内反转 | 经常被优化为反连接 |
3.2 NOT IN 的 NULL 陷阱，翻车重灾区
NOT IN 是三兄弟里最阴险的。它的逻辑是"左表字段不在子查询返回的列表里"，但如果子查询返回的列表里混进了一个 NULL，整个查询的结果会变成空集。原因是 SQL 的三值逻辑：NULL 参与比较时结果是 UNKNOWN，WHERE 只认 TRUE，于是一连串条件全部落到 UNKNOWN，一行都出不来。
举个极端例子：如果 orders.user_id 允许为 NULL，并且恰好存在一条 user_id 为 NULL 的订单，那么前面那句 NOT IN 写法会直接返回空表。很多人线上发现数据莫名其妙消失，排查半天，根子就在这个 NULL 上。相比之下，LEFT JOIN ... IS NULL 天然绕开了这个坑——前提是过滤列选主键，这一点上一节已经强调了。
3.3 性能之争：NOT EXISTS 和 LEFT JOIN 到底谁快
网上关于"NOT EXISTS 快还是 LEFT JOIN 快"的争论能吵几百楼，我的结论是：别信结论，看执行计划。在 MySQL 5.6+、PostgreSQL、SQL Server 这些常见数据库的较新版本里，优化器很可能把这两种写法改写成同一种反连接（anti join）执行计划，跑出来的路径基本一致。我实际拿两张百万级表测过：一个版本里 NOT EXISTS 走了子查询逐行关联，LEFT JOIN ... IS NULL 反而被优化成哈希反连接更快；换一个版本，两者计划几乎一样。所以在自己的数据环境里 EXPLAIN 一下，比记口诀管用得多。
3.4 可读性其实是最现实的差距
抛开性能玄学，可读性这个差距是实打实的。NOT EXISTS 的写法是"不存在满足条件的记录"，读代码的人不用做任何脑内反转，三秒看懂。LEFT JOIN ... IS NULL 则需要知道"右表主键为 NULL = 没匹配上"这个隐含知识。但如果一个查询模板里要同时判断多个表的"缺失"关系，JOIN 的写法可以一套句式走到底，反而更整齐。可读性的胜负取决于团队背景，没有绝对答案。
4. 我会主动用它的三个业务场景
这个写法能在生产代码里活这么多年，不是因为炫技，而是有些场景它就是顺手。我梳理了自己主动用它而不是 NOT EXISTS 的三个场景。
4.1 场景一：查"尚未被授权"的资源列表
权限系统常见的三张表模型：用户、资源、用户资源关联表。要查"某个用户还没被授权哪些资源"，用 LEFT JOIN ... IS NULL 非常自然：
SELECT r.* FROM resource r LEFT JOIN user_resource ur ON ur.resource_id = r.id AND ur.user_id = 42 WHERE ur.id IS NULL;这里有个关键细节：关联条件里除了 resource_id 相等，一定要把 ur.user_id = 42 写进 ON，而不是写在 WHERE。如果漏掉这个限定，JOIN 会把其他用户的授权记录也匹配上来，那查出来的"未授权"清单就完全错了。这个写法把"目标用户"圈定在 JOIN 阶段，WHERE 只管输出，语义非常干净。
4.2 场景二：ETL 之后排查孤儿数据
数据迁移、ETL 跑完，经常要校验子表里有没有"父表主键已经不存在"的孤儿记录。这种需求方向是从子表找父表，用应用层脚本做要先把父表主键全量加载进内存再逐条比对，数据量大时非常浪费。一条 SQL 就解决：
SELECT child.* FROM child_table c LEFT JOIN parent_table p ON p.id = c.parent_id WHERE p.id IS NULL;这条查询我一般会先 SELECT COUNT(*) 数一遍影响范围，确认无误再决定是补数据还是 DELETE。调数据的时候配合 LIMIT 分批处理，不容易出事故。
4.3 场景三：两套数据源的一致性校准
业务系统之间经常要做数据核对，比如 A 系统维护用户档案，B 系统应该有对应的影子记录。把所有"A 有 B 没有"的挑出来，本质就是差集。这个场景下我偏好 LEFT JOIN ... IS NULL，原因是两边关联键往往不一致——比如一边用业务编号，一边用内部主键——JOIN 的 ON 条件可以很容易地扩展多个等值关系，写起来比 EXISTS 里层层嵌套子查询要直白。
5. 坑位清单：重复行、空值键和优化器的小脾气
再顺手的写法也有坑，这节的几条是我和身边同事真实踩过的，每一条都对应过一次线上排查。
5.1 重复行问题：一对多会让中间结果膨胀
回到开头的小例子，小A因为有两笔订单，在 LEFT JOIN 的中间结果里占了两行。如果此时用 SELECT u.name，会发现小A的名字出现两次。WHERE o.id IS NULL 会把这两行都过滤掉，所以"查差集"这个场景通常不敏感；但如果你把需求改成"统计有订单用户的订单数"，或者把 IS NULL 误写成 IS NOT NULL，行数膨胀就会直接改变结果。
更隐蔽的情况是右表关联键本身存在重复值。比如 orders 里存在两条 user_id = 2 的记录，按常规理解小B其实下过单，但如果你在查"users 里有哪些 id 没被任何订单引用"，结果仍然是对的，因为 WHERE 的过滤发生在 JOIN 之后，只要有匹配行，小B就会被剔除。真正危险的是，当你 SELECT 了左表字段又想要 DISTINCT 去重时，很容易掩盖问题而不是理解问题。
提示：用 LEFT JOIN ... IS NULL 查差集，如果发现结果行数明显多于左表，优先怀疑右表关联键有重复，或者需求其实需要先在子查询里对右表做 GROUP BY。
5.2 ON 里过滤和 WHERE 里过滤，差一个字差一层天
这是最经典、也最容易被新手搞混的坑。LEFT JOIN 的 ON 条件决定右表怎么匹配，WHERE 决定最终输出哪些行，两个阶段分开执行。如果把 IS NULL 判断写进 ON，比如：
SELECT u.* FROM users u LEFT JOIN orders o ON o.user_id = u.id AND o.id IS NULL;由于 o.id 作为订单主键永远不为 NULL，这个 ON 里的 AND o.id IS NULL 导致没有任何订单行能匹配上，于是所有用户都变成了"未匹配"，整张表全量输出——这就完全错了。一句话记住：ON 是"匹配规则"，WHERE 是"输出规则"，想实现差集，IS NULL 必须站在 WHERE 这一侧。
5.3 左表关联键本身为 NULL 时的误伤
还有一个容易被忽略的方向：左表的关联键如果本身就有 NULL 值，这些行在 JOIN 阶段会因为 NULL 无法参与等值比较而直接落入"未匹配"阵营，最终被 IS NULL 当成功目标抓出来。但业务语义上，"这个字段还没赋值"和"右表确实查无此记录"可能是两件事。所以使用这个写法前，最好确认左表的关联键非空；如果确实有 NULL，先考虑用 COALESCE 给个默认值，或者单独做一版处理。
5.4 优化器不背锅：索引和数据分布才是关键
前面 3.3 提到执行计划，这里补充一句：LEFT JOIN ... IS NULL 能不能走高效的反连接，很大程度上取决于右表关联键上有没有索引，以及数据分布长什么样。右表几百万行但命中率很低，索引探测就非常关键；两边都是小表，那纠结性能不如纠结业务。我见过有人在两万行的小表上强行改成 EXISTS 追求"性能"，多此一举。
6. 用顺手之后的几条个人铁律
最后分享几条我这些年沉淀下来的使用习惯，每条都是真金白银换来的。
6.1 先 EXPLAIN，再谈优劣
不管在哪篇文章看到"XX 写法一定更快"，都先打个问号。我的标准动作是：写完成型查询之后跑一次 EXPLAIN，重点看执行计划里有没有出现反连接、关联键是否走索引、扫描行数是否符合预期。有了这三个数字，再来判断要不要换写法，才有依据。
6.2 默认选 NOT EXISTS，特殊场景切 JOIN
如果让我给团队定一个默认规范，我会写 NOT EXISTS——它最容易读。但当关联条件长、或者需要在同一个模板里拼多个差集判断时，我就切到 LEFT JOIN ... IS NULL，让整条 SQL 风格统一。写代码是给人看的，优化器不认你的个人偏好，维护的人能三秒看懂才是硬道理。
6.3 每次使用前，强制确认三件事
我给自己定了个检查清单，每次写这个写法前过一遍：
- 过滤列是不是右表主键或非空唯一键？
- 左表关联键有没有 NULL 的可能性？
- 右表关联键有没有重复数据导致中间结果膨胀的风险？
这三条确认完，基本不会翻车。尤其是第一条，逢写必查，因为它是整个写法的安全底线。
6.4 一个复合场景的完整示例
放一个我实际处理过的报表模板：查"最近 30 天活跃、但没领取过某活动优惠券"的用户。活跃用户列表先用派生表固定下来，再做差集：
SELECT u.id, u.name FROM ( SELECT id, name FROM users WHERE last_active_at >= CURRENT_DATE - INTERVAL 30 DAY ) u LEFT JOIN coupon_receives cr ON cr.user_id = u.id AND cr.campaign_id = 2025 WHERE cr.id IS NULL ORDER BY u.last_active_at DESC;注意 coupon_receives 的关联条件里同样带了 campaign_id 限定，和 4.1 权限场景的道理一致：先在 ON 里把右表圈定到"这个活动"的范围内，再做差集。用这种写法跑出来的"未参与活动用户"报表，再也没有出现过由于关联范围没圈住而多算或少算的情况，评审的时候把 ON 和 WHERE 的关系一讲，大家也都能接受这个"奇怪思路"。
