1. 这一题为什么会成为“面试必答”：一段真实报表开发经历
先说一个我自己踩过的坑。有一年做经营分析报表，需求很朴素：统计每个用户的已完成订单金额，并且要列出那些没有任何已完成订单的用户。我当时的直觉是，用 LEFT JOIN 把用户表和订单表关联起来，再在 WHERE 里加一个o.status = 'completed'条件不就完事了吗？结果一跑，没订单的客户全部消失了。检查半天，最后才发现问题出在我把过滤条件放在了 WHERE，而不是 ON。
这件事之后我认真梳理了一遍 SQL 里 ON 与 WHERE 过滤的差异，发现它不只是个面试题，而是很多人写关联查询时最容易踩的语义陷阱。
这道题之所以经典，是因为从内连接的角度看，这两种写法结果往往一样；一旦换到外连接，行为立刻分道扬镳。很多人记不住结论，其实是因为没有真正理解 JOIN 的底层语义。这篇文章我会从数据模型、SQL 执行顺序、优化器行为三个层面拆解，再结合几个真实项目里的常见坑，给出一个可以直接照着用的判断准则。
适合谁看？如果你已经能写出基本的 JOIN 查询，但遇到“为什么 LEFT JOIN 的数据少了”“为什么过滤条件写在 ON 里和 WHERE 里结果不一样”这类问题仍然要翻文档，那这篇文章就是给你准备的。
2. 先建一套能复现差异的数据模型
很多人讲这个题目喜欢空对空地讲概念，我觉得不如先摆一套可以跑的数据。下面我用两张非常简单的业务表：用户表 users 和订单表 orders，配套几条测试数据，后面所有实验都在这套数据上做。
2.1 表结构和测试数据
以 PostgreSQL 语法为例，你也可以直接在 MySQL、SQL Server 上执行，基础思路完全一致。
CREATE TABLE users ( id INT PRIMARY KEY, name TEXT, status TEXT ); CREATE TABLE orders ( id INT PRIMARY KEY, user_id INT REFERENCES users(id), amount NUMERIC(10,2), status TEXT ); INSERT INTO users VALUES (1, '张三', 'active'), (2, '李四', 'active'), (3, '王五', 'inactive'); INSERT INTO orders VALUES (101, 1, 500.00, 'completed'), (102, 1, 200.00, 'pending'), (103, 3, 100.00, 'completed'), (104, 3, 50.00, 'cancelled');这组数据刻意设计了几个特点：
- 张三有两条订单，一条 completed，一条 pending，用来观察过滤条件对多行匹配的影响。
- 李四一条订单都没有，是观察 LEFT JOIN 空值扩展的关键角色。
- 王五的用户状态是 inactive，但确实有订单，用来测试“过滤用户表自身字段”时 ON 和 WHERE 的差异。
- 王五有一条 cancelled 订单，方便观察多状态过滤时的行为。
2.2 从 SQL 执行顺序推导两个过滤位置的意义
要理解 ON 和 WHERE 的区别，先要把 SQL 语句的执行顺序拉出来。逻辑上，大多数数据库的查询处理顺序大致是：
FROM→ON→JOIN→WHERE→GROUP BY→HAVING→SELECT→ORDER BY
这里最关键的是 ON 和 JOIN 发生在 WHERE 之前。
光看这个顺序还不够，因为 JOIN 类型会改变“ON 决定什么”。内连接（INNER JOIN）中，ON 既决定连接键，也决定哪些行能进入结果；而 ON 里写的额外条件本质上只是连接匹配条件的一部分。外连接（LEFT JOIN、RIGHT JOIN、FULL JOIN）则引入了“保留表”的概念：LEFT JOIN 会保留左表的全部行，右表没有匹配上的地方补 NULL。这时候 ON 里写的条件决定了“右表哪些行能和左表匹配”，而 WHERE 里写的条件决定了“JOIN 完成之后，最终结果集里要过滤掉哪些行”。
你可以把 JOIN 想象成一道流水线：ON 是在“组装工位”上决定两个零件能不能拼在一起；WHERE 是组装完成后，质检员根据最终成品的外观决定要不要扔掉。组装时没拼上的零件，会在 LEFT JOIN 里用 NULL 填充继续送往下游；但到了质检环节，如果 WHERE 条件要求某个字段不能为 NULL，这些“空壳成品”就会被直接淘汰。
这套比喻虽然简化，但能把“为什么 LEFT JOIN 遇到 WHERE 右表过滤会丢行”这个现象讲得很直观。后面几个实验会把这个过程逐步验证出来。
3. 实验一：INNER JOIN 下，ON 过滤和 WHERE 过滤“殊途同归”
先处理最简单、也最容易让初学者困惑的情况：内连接。
3.1 同一需求，两种写法
需求：找出所有有已完成订单的用户及其订单信息。
写法一，把状态过滤写在 ON 后：
SELECT u.id, u.name, o.id AS order_id, o.amount, o.status FROM users u JOIN orders o ON u.id = o.user_id AND o.status = 'completed';写法二，把状态过滤写在 WHERE 里：
SELECT u.id, u.name, o.id AS order_id, o.amount, o.status FROM users u JOIN orders o ON u.id = o.user_id WHERE o.status = 'completed';两条 SQL 的结果完全一致：
| id | name | order_id | amount | status |
|---|---|---|---|---|
| 1 | 张三 | 101 | 500.00 | completed |
| 3 | 王五 | 103 | 100.00 | completed |
为什么一致？因为内连接只有匹配成功的行会进入结果集，不存在“保留行补 NULL”的环节。第一种写法里，ON 条件被拆成“连接键 + 状态过滤”，不满足状态的行不会参与连接；第二种写法里，所有能匹配上 user_id 的订单先进入中间结果，再由 WHERE 把状态不等于 completed 的行筛掉。由于内连接没有保留行，这两种过程最终得到的结果集合是完全相同的。
这也正是“写在哪都一样”这句谣言的来源。很多初学者只在内连接场景下验证过，就以为全场景通用，结果一到 LEFT JOIN 就翻车。
3.2 为什么数据库允许这种等价
从关系代数角度看，内连接的 ON 条件本身就是一个二元谓词，它作用于两个表的笛卡尔积，选出满足条件的行。WHERE 过滤同样是对中间结果集合施加谓词。对内连接而言，ON 里多出来的条件完全可以合并进 WHERE，反之亦然，因为它们都在做同一件事：限制最终输出行。
从优化器角度看，绝大多数现代数据库（PostgreSQL、MySQL、SQL Server、Oracle）都会做谓词下推和连接顺序优化。也就是说，哪怕你把过滤条件写在 WHERE 里，优化器也极有可能把它“下推”到表扫描阶段，在真正执行 JOIN 之前先把订单表里 status = 'completed' 的行筛出来；哪怕你写在 ON 里，优化器也可能在语义不变的前提下重新调整连接顺序。因此，对于内连接，两者的执行计划最终往往非常接近，甚至一模一样。
不过要注意：JOIN和INNER JOIN是同一个意思，但有些人会写FROM users u, orders o WHERE u.id = o.user_id AND o.status = 'completed'，这种旧式逗号连接在语义上也等价于内连接，只是可读性和可维护性更差。不属于本文重点，但看到时要知道它和内连接是一回事。
4. 实验二：LEFT JOIN 下，ON 和 WHERE 的结果开始分道扬镳
现在进入真正的重点。LEFT JOIN 引入了“保留左表全部行”的语义，ON 和 WHERE 的差异开始变得致命。
4.1 过滤右表：经典陷阱，LEFT JOIN 被改成了 INNER JOIN
需求：查询所有用户，并列出他们的已完成订单；如果某个用户没有已完成订单，也仍然要出现在结果里。
正确写法是把状态过滤放在 ON 中：
SELECT u.id, u.name, o.id AS order_id, o.amount, o.status FROM users u LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'completed' ORDER BY u.id, o.id;结果是：
| id | name | order_id | amount | status |
|---|---|---|---|---|
| 1 | 张三 | 101 | 500.00 | completed |
| 2 | 李四 | NULL | NULL | NULL |
| 3 | 王五 | 103 | 100.00 | completed |
注意看李四这一行：他没有已完成订单，也没有任何订单，所以右表字段全部是 NULL。这正是 LEFT JOIN 该有的效果——左表的人必须都在。
如果把这个需求误写成 WHERE 过滤：
SELECT u.id, u.name, o.id AS order_id, o.amount, o.status FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE o.status = 'completed' ORDER BY u.id, o.id;结果是：
| id | name | order_id | amount | status |
|---|---|---|---|---|
| 1 | 张三 | 101 | 500.00 | completed |
| 3 | 王五 | 103 | 100.00 | completed |
李四不见了。
为什么会这样？LEFT JOIN 先把所有用户和所有能匹配上 user_id 的订单拼在一起，李四拼出来的那行，o.status 是 NULL。随后 WHERE 要求o.status = 'completed'，而 NULL 与任何值比较的结果都是“未知”，最终该行被过滤掉。换句话说，WHERE 条件把 LEFT JOIN 强行变成了 INNER JOIN 的效果，而且是在你毫无察觉的情况下发生的。
这一点我要特别强调：很多数据分析师写报表时经常犯这个错误。表面上 SQL 里写着 LEFT JOIN，逻辑上却变成了 INNER JOIN，导致“没有数据的用户”被默默丢掉，报表上的数字怎么对都对不上。
4.2 过滤左表：ON 子句影响匹配规则，WHERE 子句影响保留行
上面测的是过滤右表字段，下面换个角度：过滤 USER 表自身的字段。
需求：查询所有用户及其订单，但希望“不活跃用户”即使有订单，在结果里也显示为无订单；同时不活跃用户本身仍要保留在结果里。
听起来有点绕，但实际场景是：你不想让某些用户的订单参与报表统计，但又不能把这些人从名单里抹掉。
写成 ON 过滤：
SELECT u.id, u.name, o.id AS order_id, o.amount FROM users u LEFT JOIN orders o ON u.id = o.user_id AND u.status = 'active' ORDER BY u.id, o.id;结果是：
| id | name | order_id | amount |
|---|---|---|---|
| 1 | 张三 | 101 | 500.00 |
| 1 | 张三 | 102 | 200.00 |
| 2 | 李四 | NULL | NULL |
| 3 | 王五 | NULL | NULL |
王五虽然存在，但他的订单没有关联上，因为 ON 条件里的u.status = 'active'对王五这一行不成立。李四依然保留，因为他本来就没有订单。
如果改成 WHERE 过滤：
SELECT u.id, u.name, o.id AS order_id, o.amount FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE u.status = 'active' ORDER BY u.id, o.id;结果是：
| id | name | order_id | amount |
|---|---|---|---|
| 1 | 张三 | 101 | 500.00 |
| 1 | 张三 | 102 | 200.00 |
| 2 | 李四 | NULL | NULL |
王五直接消失。这就是我前面说的“ON 决定匹配规则，WHERE 决定最终结果集包含哪些行”的具体体现。同样一个条件，写在 ON 里影响的是“王五的订单能不能匹配上”，写在 WHERE 里影响的是“王五这行用户数据还在不在”。
这个细节很多人没意识到，因为它不如“LEFT JOIN 变 INNER JOIN”那么常见。但在做用户维度的留存分析、生命周期分析时，很容易踩中。
4.3 多表链式 JOIN：一个 WHERE 条件引发连锁反应
再进一步，三张表关联时，问题会变得更隐蔽。假设我们再加一张支付表 payments：
CREATE TABLE payments ( id INT PRIMARY KEY, order_id INT REFERENCES orders(id), amount NUMERIC(10,2) ); INSERT INTO payments VALUES (1001, 101, 500.00), (1002, 102, 150.00);现在需求是：列出所有用户，以及他们已完成订单的支付记录；没有已完成订单的用户也要保留。
如果把状态过滤放在第一个 JOIN 的 ON 里：
SELECT u.id, u.name, o.id AS order_id, o.status, p.id AS payment_id FROM users u LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'completed' LEFT JOIN payments p ON p.order_id = o.id ORDER BY u.id, o.id, p.id;结果是三行：
| id | name | order_id | status | payment_id |
|---|---|---|---|---|
| 1 | 张三 | 101 | completed | 1001 |
| 2 | 李四 | NULL | NULL | NULL |
| 3 | 王五 | 103 | completed | NULL |
张三只关联了已完成订单 101，订单 102 虽然是 pending 但被 ON 条件挡在连接之外；王五的订单 103 是 completed，但没有对应支付，所以 payment_id 是 NULL。
如果把过滤条件误放到 WHERE：
SELECT u.id, u.name, o.id AS order_id, o.status, p.id AS payment_id FROM users u LEFT JOIN orders o ON u.id = o.user_id LEFT JOIN payments p ON p.order_id = o.id WHERE o.status = 'completed' ORDER BY u.id, o.id, p.id;结果是两行：
| id | name | order_id | status | payment_id |
|---|---|---|---|---|
| 1 | 张三 | 101 | completed | 1001 |
| 3 | 王五 | 103 | completed | NULL |
李四又没了。而且注意，这个 WHERE 条件还顺带把张三的 pending 订单 102 过滤掉了。虽然第一步 LEFT JOIN 把 102 关联进来了，但 WHERE 在最后统一清理，导致你既丢了李四，也看不到张三的 pending 订单。
多表 JOIN 场景下，WHERE 的过滤是“全局的”，它作用于整条链路产生的所有中间行；而 ON 的过滤只影响当前 JOIN 这一步的匹配行为。理解这一点，很多报表数据对不上的问题都能迎刃而解。
5. 再从优化器角度审视：为什么“执行顺序”没那么简单
讲完语义，很多人会问：那性能呢？把过滤条件写在 ON 里是不是一定比写在 WHERE 里快？答案没那么简单。
5.1 优化器重写：很多数据库会把 WHERE 右表过滤的 LEFT JOIN 重写成 INNER JOIN
PostgreSQL、MySQL 等主流数据库都有一个优化手段：如果 WHERE 条件对右表字段施加了“非空”限制，优化器会认为 NULL 扩展行不可能通过过滤条件，于是把 LEFT JOIN 等价转换为 INNER JOIN，再去生成执行计划。
举个例子，之前那句“错误”的 LEFT JOIN + WHERE 写法：
SELECT ... FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE o.status = 'completed';在 PostgreSQL 中查看执行计划时，你会发现它实际执行的很可能已经不是 Left Join，而是 Inner Join。为什么？因为o.status = 'completed'这个条件排除了所有 NULL 行，保留的只有真正能匹配上的订单，这与 INNER JOIN 的结果完全一致。优化器天生就会做这种等价变换。
这里有个讽刺的后果：执行计划上，那个“语法上写错”的 LEFT JOIN 可能和内连接一样快，甚至更快。但慢不是问题，语义错才是问题。你本来想保留李四，结果优化器也认为“你不想保留李四”，于是帮你把李四删了。这恰恰说明，靠执行计划去判断“对不对”是没有意义的，得先看业务语义要求什么。
5.2 执行计划里的 Filter 位置变化
把 ON 过滤和 WHERE 过滤的执行计划放在一起看，能发现明显的结构差异。
ON 过滤的 LEFT JOIN，状态条件会出现在连接条件中，例如：
- Nested Loop Left Join
- Seq Scan on users
- Index Scan using orders_user_id_idx on orders
- Index Cond: user_id = u.id
- Filter: status = 'completed'
WHERE 过滤的 LEFT JOIN，优化器如果没做等价改写，状态条件会出现在连接之后的 Filter 上：
- Nested Loop Left Join
- Seq Scan on users
- Index Scan using orders_user_id_idx on orders
- Index Cond: user_id = u.id
- Filter: status = 'completed'
Filter 在 Join 节点外部，意味着它要处理的是连接后产生的行，包括 NULL 扩展出来的那些。虽然大多数情况下优化器会把这个条件进一步下推或改写，但在某些复杂查询、视图嵌套、窗口函数叠加的场景里，优化器不一定能做出最优选择。这时，多一个连接后 Filter 就意味着多一层行处理，代价也会上去。
5.3 何时 ON 过滤会显著影响性能
不过，真正让性能产生巨大差异的，是下面这类情况：
过滤条件非常强，能把右表从 1000 万行缩到 100 行；并且这个条件是外连接的一部分。如果你把条件写在 ON 里，连接时右表只需要扫描极小一部分数据；如果写在 WHERE 里，理论上优化器也可能做下推，但它必须先证明这个下推不改变外连接语义，而“右表字段非空”就是它的证明依据，不一定总能成立，尤其是条件里还带 OR、函数或子查询时。
举个具体例子：
SELECT u.id, u.name, o.id FROM users u LEFT JOIN orders o ON u.id = o.user_id AND o.status IN ('completed', 'pending');这个 ON 条件里没有IS NULL，优化器无法把 LEFT JOIN 简化成 INNER JOIN，但可以在连接阶段用索引只读这两类状态的订单。如果把同样的条件放到 WHERE：
SELECT u.id, u.name, o.id FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE o.status IN ('completed', 'pending');结果里所有没有订单的用户全被过滤了，这与上一个查询的语义完全不同。如果右表还有大量不满足条件的行，WHERE 版本就可能在连接后产生海量中间行，再统一过滤，性能可能差出几个数量级。
所以性能上的结论是：对于内连接，ON 和 WHERE 通常可以互换，优化器会帮你兜底；对于外连接，写在哪直接决定了查询语义和潜在成本，唯一稳妥的做法是先想清楚你到底需要“保留左表全部行”还是“只要右表中满足条件的行”。
6. 日常项目中踩过的坑和我的编码约定
理论讲完了，最后落到工程实践。下面几个坑是我在实际代码评审和数据核对中反复遇到的，每一个都对应着真实故障。
6.1 坑一：报表汇总时 LEFT JOIN 悄悄变 INNER JOIN
这是最常见的。某人写“每个用户的已完成订单总金额”，为了把没有订单的用户也显示出来，用了 LEFT JOIN，但过滤条件写在了 WHERE 里：
SELECT u.id, u.name, SUM(o.amount) AS total_amount FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE o.status = 'completed' GROUP BY u.id, u.name;结果表现为：没有已完成订单的用户根本不出现，而不是以 0 金额出现。报表上用户数对不上，复盘时才发现问题。
正确处理是：
SELECT u.id, u.name, SUM(CASE WHEN o.status = 'completed' THEN o.amount ELSE 0 END) AS total_amount FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.id, u.name;或者把状态条件放进 ON，再配合 GROUP BY 统计：
SELECT u.id, u.name, COALESCE(SUM(o.amount), 0) AS total_amount FROM users u LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'completed' GROUP BY u.id, u.name;这种方式更干净，因为 ON 已经限定“只关联已完成订单”，之后 SUM 拿到的就是已完成金额汇总；没有订单的用户则是 NULL，再用 COALESCE 兜底成 0。
6.2 坑二：把过滤条件都放 ON 里，导致错误保留历史无效数据
有人学会“ON 里过滤”这一招后，容易用力过猛，把所有业务过滤都塞进 ON。比如查询“本月活跃用户及其订单”，明明想过滤掉超过 30 天没登录的用户，却把用户表的过滤条件写进 ON：
SELECT u.id, u.name, o.id AS order_id FROM users u LEFT JOIN orders o ON u.id = o.user_id AND u.last_login_at >= NOW() - INTERVAL '30 days';这样写会产生什么？超过 30 天未登录的用户仍在结果里，但他们的订单全部变成 NULL。如果业务方关心的是“只看活跃用户”，这种写法就是错的；它把需要过滤的用户“留住”了，只让他们的订单消失，这通常不是想要的结果。
所以判断原则不是“一律写 ON”，而是分清条件作用对象：如果是右表维度过滤，且你要保留左表全部行，写 ON；如果是左表维度过滤，且你要从维度表中剔除某些人，应该写在 WHERE 里；如果想保留左表全部行又希望左表条件参与匹配，那才考虑 ON。
