在SQL里写LEFT JOIN ... IS NULL，我第一次看到这个写法时也有点懵：LEFT JOIN不是要把左边表的数据全保留下来吗？后面再加个WHERE 右表字段 IS NULL，这不等于是把刚保留下来的数据又筛掉一部分吗？绕这么大一圈，图什么呢？后来在实际需求里真正用上它，才明白这其实是SQL世界里最经典的一种“反连接”（anti join）写法，专门用来回答“哪些记录在另一张表里不存在”这类问题。
这篇文章写给所有写过SQL的人，不管你是后端开发、数据分析师还是刚学会连表查询的初学者。我会把这个写法背后的逻辑、执行计划里的门道、以及我踩过的坑全部摊开讲清楚。
1. 这个写法解决的到底是什么问题
1.1 需求场景：从“查不到”到“查没有”
先想一个最常见的业务：查“下单后从未付款的订单”、查“没有关联任何商品的分类”、查“从未登录过的用户”。这些需求本质上都在问同一个问题——哪些记录在另一张表里没有对应的记录？
新手第一反应往往是NOT IN，比如：
SELECT * FROM orders WHERE user_id NOT IN (SELECT user_id FROM payments);写起来确实直白。但NOT IN有一个著名的坑：只要子查询结果里出现任何一个NULL，整个查询就会返回空表。原因后面讲三值逻辑时会细说，这里只需要记住结论：NOT IN在业务数据不干净时非常不可靠。
而LEFT JOIN ... IS NULL的写法长这样：
SELECT o.* FROM orders o LEFT JOIN payments p ON o.order_id = p.order_id WHERE p.order_id IS NULL;它的思路和前一种完全不同：先把两张表连接起来，凡是能匹配上的订单，右表字段都是有值的；凡是没匹配上的订单，右表字段因为LEFT JOIN的规则全部是NULL。最后用WHERE p.order_id IS NULL把这些“孤儿记录”筛出来。
1.2 为什么直觉上会觉得“奇怪”
正常情况下，我们写LEFT JOIN是为了拿右表的数据，比如LEFT JOIN payments p ON ...之后，SELECT里会带出p.pay_time、p.amount这些字段。但这个写法反其道而行之——连接右表的目的不是“取它的数据”，而是“借它来当过滤器”。
这就好比你去参加一个聚会，为了找出“没来的人”，你先假设所有人都来了，然后在签到表上勾掉那些真正到场的人，剩下的就是你想要的答案。LEFT JOIN干了“签到表”的活儿，IS NULL干的就是“剩下的人”这个筛选动作。
理解了这层关系，再看这个写法的优势就清楚多了：
- 它不依赖子查询，逻辑上是一条线性SQL，可读性更好。
- 它不受
NULL值干扰，因为NULL在这里不是脏数据，而是“无匹配”的标记。 - 它让优化器有机会把连接转换成更高效的“反连接”算子，这一点后面在执行计划部分详细展开。
1.3 三种“查不存在”的写法对比
先给个结论性的对比表，后面实操部分再验证：
| 写法 | 语义表达 | NULL风险 | 优化器友好度 | 适用场景 |
|---|---|---|---|---|
LEFT JOIN ... IS NULL | 连接后过滤空值 | 无 | 高，常转为Anti Join | 需要返回左表完整列时优先考虑 |
NOT IN | 不在集合中 | 高，子查询含NULL则全表空 | 一般 | 数据干净、子查询确定无NULL时可用 |
NOT EXISTS | 不存在匹配记录 | 无 | 高，常转为Anti Join | 语义最清晰，判断存在性时首选 |
看到这个表你会发现，LEFT JOIN ... IS NULL其实是在NOT IN不靠谱、NOT EXISTS不好直观理解左表字段时的一个折中方案。它和NOT EXISTS在多数数据库里执行计划几乎完全一样，区别主要在写代码的风格和部分极端场景的优化表现。
2. 核心细节解析：为什么这个SQL能正确工作
2.1 一条SQL的执行顺序，决定了命运的三步
要理解LEFT JOIN ... IS NULL，必须知道SQL的逻辑执行顺序，不是按你写的顺序执行。逻辑上，数据库先做连接，再做过滤：
- FROM阶段：确定左表
orders和右表payments。 - ON阶段：根据连接条件
o.order_id = p.order_id判断哪些行能配对，同时生成连接后的中间结果集。此时LEFT JOIN的语义是：左表所有行都保留，右表没有匹配就补NULL。 - JOIN阶段：把中间结果集交给下一步。
- WHERE阶段：应用过滤条件
p.order_id IS NULL，只保留右表字段为空的行。
关键在于第2步和第4步是分开的。LEFT JOIN在第2步已经完成了“保留左表全部行”的任务，所以哪怕最终的查询结果看起来像INNER JOIN，那也是在WHERE阶段被“人工”过滤出来的，而不是LEFT JOIN本身失效了。
很多人第一次写这个SQL时会问：既然最终只要不匹配的行，为什么不直接写INNER JOIN然后加个!=？因为INNER JOIN在第2步就把没匹配上的行丢掉了，你根本没有机会在WHERE阶段再把它捞回来。
2.2 三值逻辑（NULL与UNKNOWN）在判断中的角色
SQL的判断逻辑和普通编程语言不一样，它有三个布尔值：TRUE、FALSE、UNKNOWN。任何涉及NULL的比较，结果都不是TRUE也不是FALSE，而是UNKNOWN。
NULL = NULL是UNKNOWN，NULL != NULL也是UNKNOWN，NULL > 5还是UNKNOWN。这就是NOT IN会翻车的根因：
SELECT * FROM orders WHERE user_id NOT IN (SELECT user_id FROM payments);等价于：
SELECT * FROM orders WHERE user_id != 1 AND user_id != 2 AND user_id != NULL ...最后那个user_id != NULL的结果永远是UNKNOWN，整个AND链的结果就变成了UNKNOWN或FALSE，最终一行都查不出来。
而IS NULL是专门用来判断“是不是NULL”的谓词，它不是比较操作，不会进入三值逻辑的陷阱。p.order_id IS NULL在匹配失败时返回TRUE，在匹配成功时返回FALSE，干干净净，没有歧义。
注意：这就是为什么判断某个值是否为空必须用
IS NULL，而不能用= NULL或!= NULL。前者是查询条件，后者是永假表达式。
2.3 多表连接时容易踩的隐藏陷阱
LEFT JOIN ... IS NULL单个连接时很好理解，但一旦链条变长，问题就来了。比如要查“分类下有商品但商品从未被购买的分类”：
SELECT c.* FROM categories c LEFT JOIN products p ON c.id = p.category_id LEFT JOIN order_items oi ON p.id = oi.product_id WHERE oi.product_id IS NULL;这个SQL的意图是：分类连接商品，商品连接订单明细，只要订单明细里没有对应的商品，就说明该分类下的商品没被买过。
但它有个隐患——如果某个分类下根本没有商品呢？products表匹配不上，那p.id就已经是NULL了，再往下连接order_items也是NULL，WHERE oi.product_id IS NULL依然成立，这个分类会被误判为“商品从未被购买”。严格来说，这两种情况在业务语义上完全不同，返回结果却混在一起。
更稳妥的做法是拆成两步思考：先确定你要找的“孤儿”是在哪个连接层级产生的，再用括号或子查询控制连接粒度，比如先找到“从未被购买的商品”，再用这些商品去关联分类，或者给WHERE条件加上p.id IS NOT NULL来排除空分类的情况。
这种多层级连接的业务在真实系统里非常常见，写之前最好自己在纸上把连接顺序画一遍，想想每一层匹配失败时各表字段会变成什么状态。
3. 实操复现：从建表到执行计划，一步步验证
3.1 模拟场景与数据准备
我拿一个典型的“订单未支付”场景来演示。假设某电商系统有两张表：订单表orders和支付表payments，要查所有“下了单但没有任何支付记录”的订单。
建表语句：
CREATE TABLE orders ( order_id INT PRIMARY KEY, user_id INT NOT NULL, amount DECIMAL(10,2) NOT NULL, created_at DATETIME NOT NULL ); CREATE TABLE payments ( payment_id INT PRIMARY KEY, order_id INT NOT NULL, pay_time DATETIME NOT NULL, channel VARCHAR(20), INDEX idx_order_id (order_id) );插入模拟数据，这里故意造3条付费订单和2条未付费订单：
INSERT INTO orders VALUES (1, 101, 199.00, '2024-01-01 10:00:00'), (2, 102, 99.50, '2024-01-02 11:30:00'), (3, 103, 299.00, '2024-01-03 09:20:00'), (4, 104, 150.00, '2024-01-04 14:00:00'), (5, 105, 89.00, '2024-01-05 16:45:00'); INSERT INTO payments VALUES (1, 1, '2024-01-01 10:05:00', 'wechat'), (2, 2, '2024-01-02 11:35:00', 'alipay'), (3, 3, '2024-01-03 09:25:00', 'card');预期结果应该是订单4和订单5。
3.2 核心SQL与三种写法实测
先写LEFT JOIN ... IS NULL版本：
SELECT o.order_id, o.user_id, o.amount FROM orders o LEFT JOIN payments p ON o.order_id = p.order_id WHERE p.order_id IS NULL;执行结果：
order_id user_id amount 4 104 150.00 5 105 89.00结果正确。再对比NOT EXISTS版本：
SELECT o.order_id, o.user_id, o.amount FROM orders o WHERE NOT EXISTS ( SELECT 1 FROM payments p WHERE p.order_id = o.order_id );结果一样。最后看NOT IN版本，这次故意让payments表里出现一条order_id为NULL的脏数据，复现翻车现场：
INSERT INTO payments (payment_id, order_id, pay_time, channel) VALUES (4, NULL, '2024-01-06 10:00:00', 'cash'); SELECT o.order_id, o.user_id, o.amount FROM orders o WHERE o.order_id NOT IN (SELECT order_id FROM payments);结果返回空表。这就是前面说的三值逻辑陷阱。把这条脏数据删掉，NOT IN又能正常返回了。
3.3 执行计划解读：优化器如何把LEFT JOIN变成Anti Join
光看结果还不够，得看执行计划才知道性能差异。在MySQL里用EXPLAIN查看：
EXPLAIN SELECT o.order_id, o.user_id, o.amount FROM orders o LEFT JOIN payments p ON o.order_id = p.order_id WHERE p.order_id IS NULL;重点看Extra列。在MySQL 8.0里通常会看到Not exists字样，这就是优化器识别出“IS NULL过滤LEFT JOIN”模式后，主动将其转换成反连接（Anti Join）的证据。它会逐行扫描orders，对每个订单去payments里查找，一旦找到匹配就停止后续查找，因为只要有一条匹配记录，这行订单最终就会被WHERE过滤掉，没必要再找第二、第三条。
这个“找到就停”的特性非常重要。如果连接条件p.order_id上有索引，数据库可以非常快地判断“有没有”，而不是“有几个”。所以payments表上的idx_order_id索引不是可有可无的，它是这个方案高性能的基石之一。
在PostgreSQL或SQL Server里，执行计划会直接显示Anti Join、Hash Anti Join或Merge Anti Join。我实测过同样的数据，优化器选择Hash Anti Join时，两万行订单对一万行支付的连接，扫描和计算都在毫秒级完成，而如果误写成子查询加NOT IN且没有索引，性能会差出几个数量级。
注意：
EXPLAIN看到Not exists不代表业务上“不存在”，它只是优化器内部的一种算子命名，代表“查不到匹配即返回TRUE”。别被字面意思误导。
4. 常见问题与坑点排查实录
4.1 高频坑点速查表
下面这些坑都是我在实际开发和带新人时反复遇到的，全部整理成速查表：
| 问题现象 | 可能原因 | 解决方案 |
|---|---|---|
| 查询结果为空 | payments表里有脏数据NULL，且写成NOT IN | 改用LEFT JOIN ... IS NULL或NOT EXISTS |
| 左表记录重复返回 | 右表有多条匹配记录，WHERE IS NULL后重复行未被去重 | 加DISTINCT或GROUP BY，但优先检查连接字段是否真是一对一 |
| 多表LEFT JOIN串链条时结果比预期多 | 中间层连接失败产生的NULL被误判为“目标NULL” | 逐层排查，必要时给中间层加IS NOT NULL限定 |
| 查询特别慢 | 右表连接列没有索引，优化器只能全表扫描做Nested Loop | 给连接列加索引，或检查统计信息是否过期 |
| 查出来的数据里右表字段不是NULL | 把过滤条件写进了ON而不是WHERE | 记住：ON管连接，WHERE管过滤，语义完全不同 |
4.2 三个真实踩坑案例
第一个坑来自某次数据迁移的数据核对。当时要找出“源系统有、目标系统没有”的订单记录，我顺手写了NOT IN，源数据里恰好有一条订单号的值为NULL，结果整个核对结果直接为空，差点让人以为数据全同步成功了。后来换成LEFT JOIN ... IS NULL才暴露出真实缺失的几百条记录。这次让我彻底记住了：生产环境的数据永远不要假设它干净。
第二个坑是加DISTINCT引发的大坑。有次查“从未发过帖子的用户”，用户表users和帖子表posts是一对多关系，理论上一个用户对应多个帖子或者零个帖子。但有个老系统在posts表里给同一个发帖记录存了两条数据，导致WHERE p.user_id IS NULL之后同一个用户竟然出现两遍。我在外层加了DISTINCT把结果修正了，但后来发现这个修正是给脏数据擦屁股，真正的根因是posts表里重复记录。加DISTINCT只是掩盖问题，长期来看还是要清理数据源头。
第三个坑是关于ON条件里加过滤的。有人为了“提高性能”，在ON后面加了个AND p.channel = 'wechat'，本意是想只统计微信支付不存在的订单。结果发现查出来的数据里，有些订单其实是支付宝支付成功的。原因很简单：ON条件里的过滤是连接时用的，它决定了哪些行能配对成功，但LEFT JOIN仍然会保留左表所有行，没配上的行照样全是NULL。所以WHERE p.order_id IS NULL会把“支付宝成功”的订单也当成“无支付”捞出来。这种把ON当WHERE用的问题，在多表连接时最容易造成逻辑混乱。
提示：判断某个字段是否要放进
ON还是WHERE，唯一标准是“如果左表行没匹配上，我希望它保留还是过滤掉”。想保留就用ON，想过滤就用WHERE。
4.3 什么时候该放弃LEFT JOIN ... IS NULL
这个写法虽好，但不是万能药。以下几种情况我会主动换方案：
- 只需要判断存在性，不需要返回左表完整列：直接用
NOT EXISTS，语义更清晰。优化器对NOT EXISTS的反连接识别通常更积极，尤其是在子查询带有额外过滤条件时，写起来也更自然。 - 右表非常大（千万级以上），且连接列没有可用索引：
LEFT JOIN ... IS NULL会先做连接再做过滤，连接过程本身的代价很高。此时先想办法优化索引，或者改成WHERE NOT EXISTS配合驱动顺序，让优化器从更小的表开始扫描。 - 需要和其他表继续做JOIN，且NULL值有业务含义：如果右表字段本身就可能存NULL（比如“订单备注”这种可空字段），
IS NULL就无法区分“无匹配”和“匹配到NULL值”。这种情况要么换NOT EXISTS，要么在ON条件里改成p.some_not_null_column IS NULL来判断，比如用主键列作为判断依据。 - SQL写成多表连环LEFT JOIN：链太深时优化器不一定还能把整条链路转换成高效的反连接。我建议把这种查询拆成临时表分组处理，或者用
NOT EXISTS写在最外层，让优化器每一步都走最轻量判断。
写在最后的几个实操心得
我个人现在写“查不存在”类需求时，默认会写NOT EXISTS，因为语义最直观、坑最少。但遇到需要返回左表大量列、且业务方要求最终SQL以一种“线性连接”风格呈现时，我会切回LEFT JOIN ... IS NULL——它确实更符合一条主查询从头到尾贯穿的阅读习惯。两种写法最终执行计划常常是一样的，选哪种主要看你团队维护代码的习惯。
还有一个小经验：写完LEFT JOIN ... IS NULL一定要记得用EXPLAIN看一眼执行计划，确认Extra列里有预期中的反连接提示，同时检查连接列是否走了索引。如果发现优化器选择了全表扫描，先把索引补上，再考虑改写SQL。这个习惯帮我避掉过好几次线上慢查询。
最后再提醒一句，任何涉及到NULL判断的SQL，都建议附带造几条脏数据做测试，包括NULL值、重复值、空字符串。SQL查询最怕的不是逻辑写错，而是数据里藏着你以为不存在的边界情况。LEFT JOIN ... IS NULL这个写法，本质上就是在和NULL打交道，所以先把NULL的脾气摸透，再用它就会顺手很多。
