MySQL 的left join里where和on的区别到底在哪这个话题面试被问、开发被坑几乎隔三差五就要见一次。很多人第一反应是“on 是连接条件where 是过滤条件”这么说没错但只要加上left join这几个字事情就没那么简单。同一个条件写在on里和写在where里结果可能天差地别轻则统计数字不对重则把LEFT JOIN悄悄变成INNER JOIN数据直接少一大片。这篇文章就从原理、业务场景和实测结果三个角度把这个问题彻底掰开揉碎讲清楚顺便把我平时排查这类 SQL 问题的方法也交出来。1. 先搞清楚一件事ON 和 WHERE 分别作用在哪个阶段1.1 逻辑执行顺序不是物理执行顺序SQL 本身有个逻辑执行顺序虽然 MySQL 在执行的时候有查询优化器会自己调整物理执行路径但最终结果必须符合逻辑语义。我们分析 SQL 时心里始终要按下图的顺序走FROM t_user u LEFT JOIN t_order o ON u.id o.user_id AND o.status paid WHERE u.level gold GROUP BY u.id HAVING COUNT(o.id) 0 ORDER BY u.id LIMIT 10核心顺序是先执行FROM和JOIN在这一步用ON决定“两边怎么合并、哪些行匹配”合并完生成一个临时结果集之后才轮到WHERE对这个结果集做过滤。GROUP BY、HAVING、ORDER BY、LIMIT都在更后面。很多人把on和where都理解成“条件判断”于是默认它们是同一件事先做什么后做什么无所谓。但对于LEFT JOIN来说顺序就是命根子ON负责“连接阶段”WHERE负责“连接后的过滤阶段”。连接阶段只要发现左表行在右表没有匹配就会把右表列填成NULL同时保留左表行到了WHERE阶段这些NULL是可以被过滤掉的。一旦你WHERE里写的条件是右表某个非空字段那LEFT JOIN补出来的NULL行就会全部被筛掉左表行也就跟着消失了。这里要额外说一句MySQL 的查询优化器确实可能改变执行计划比如把LEFT JOIN转换成INNER JOIN但转换的前提是结果必须等价。如果条件写在WHERE里导致NULL扩展行没机会存活优化器会觉得“反正你最后也不要这些行”干脆按内连接执行。这就是为什么很多LEFT JOIN写出来看执行计划却是INNER JOIN数据也少了。1.2 用一张对比表快速建立直觉对比维度ON 中的条件WHERE 中的条件作用阶段连接阶段决定右表哪些行能关联上连接完成后的结果集过滤是否影响左表行的保留不影响LEFT JOIN 无论如何都保留左表所有行可能影响条件一旦筛掉右表 NULL 行左表行也就没了右表字段过滤如 status、is_deleted只限制右表匹配范围不匹配则左表保留、右表置 NULL会直接过滤掉没有匹配到的 NULL 行LEFT JOIN 可能退化成 INNER JOIN左表字段过滤如 u.levelgold不直接过滤左表只是把右表匹配限定在满足条件的左表行上相当于匹配条件真正对左表做过滤连接条件如 u.id o.user_id标准写法应该放这里放这里容易产生理解混乱一般不这样做典型场景想保留左表所有记录同时只关联右表中符合条件的记录不需要保留不符合过滤条件的行真正要求结果集只包含满足条件的记录这张表里最反直觉的就是“右表字段过滤”那一行。很多开发者习惯把所有过滤条件一股脑丢进WHERE在INNER JOIN里这么做完全没问题但在LEFT JOIN里一个o.status paid就可能让没有支付记录的用户整体消失而程序员还以为自己写的是“保留所有用户只是统计已支付订单数”。1.3 为什么以前写 INNER JOIN 时候没发现这个问题因为INNER JOIN的语义就是“只要交集”不匹配的行本来就该被丢掉。所以同一个条件放ON还是放WHERE最终结果往往一样优化器也经常帮你改写成一样的计划。于是很多人的习惯就养成了条件随手写进WHERE。等到换成LEFT JOIN这套习惯就翻车了。LEFT JOIN有一个非常重要的动作右表没匹配上时会额外生成一行“左表原样 右表全 NULL”。悖论就在这INNER JOIN没有补 NULL 这个动作而WHERE过滤针对的是补完之后的结果集你的WHERE条件一旦涉及右表列比如o.status paid那些补出来的 NULL 行自然不满足条件被丢掉了。你明明只想过滤右表结果左表也被连坐。所以理解这个问题的关键不是背语法而是心里建立“连接后临时结果集”这个概念。ON决定临时结果集长什么样WHERE决定从临时结果集里挑哪些行。2. 用真实业务场景看懂两种写法为什么结果不同2.1 先准备一个最常用的用户订单模型每个聊 SQL 的例子都离不开两个表用户表和订单表。这里我建两个简单的表CREATE TABLE t_user ( id INT PRIMARY KEY, name VARCHAR(32), level VARCHAR(16) ) ENGINEInnoDB; CREATE TABLE t_order ( id INT PRIMARY KEY, user_id INT, amount DECIMAL(10,2), status VARCHAR(16), KEY idx_user_id (user_id), KEY idx_user_status (user_id, status) ) ENGINEInnoDB;初始化数据INSERT INTO t_user VALUES (1, 张三, gold), (2, 李四, normal), (3, 王五, normal); INSERT INTO t_order VALUES (101, 1, 99.00, paid), (102, 1, 50.00, unpaid), (103, 2, 80.00, paid);数据很简单张三有两条订单一条已支付、一条未支付李四有一条已支付订单王五没有订单。下面所有场景都是围绕这个数据集跑的。你可以直接在自己电脑上建表执行几分钟就能验证完。2.2 场景一统计每个用户的已支付订单数这个需求最常见的写法有两种。先看第一种把状态条件放ONSELECT u.id, u.name, COUNT(o.id) AS paid_order_cnt FROM t_user u LEFT JOIN t_order o ON u.id o.user_id AND o.status paid GROUP BY u.id, u.name;结果是这样的idnamepaid_order_cnt1张三12李四13王五0注意王五还在因为LEFT JOIN保证所有用户都保留他只是没有“已支付”的订单COUNT(o.id)统计出来是 0。第二种写法把状态条件放WHERESELECT u.id, u.name, COUNT(o.id) AS paid_order_cnt FROM t_user u LEFT JOIN t_order o ON u.id o.user_id WHERE o.status paid GROUP BY u.id, u.name;结果是这样idnamepaid_order_cnt1张三12李四1王五这一行的o.status是NULL在WHERE阶段被过滤掉了。看起来好像只是少了个 0 统计但如果你后面还要做留存分析、用户总览、报表导出王五这一类“无订单用户”就会从结果里消失数据口径直接错掉。很多人会在这个地方抬杠我就是要统计有订单的用户不要无订单的。那我建议你直接写INNER JOIN更清晰没必要故意留一个LEFT JOIN再靠WHERE把它打成内连接既绕又容易让后来维护的人看晕。2.3 场景二找出从未下单的用户既然刚才知道WHERE能过滤掉没有匹配的用户那反过来想要找“从未下单的人”就是在WHERE里用“右表字段是 NULL”来筛SELECT u.id, u.name FROM t_user u LEFT JOIN t_order o ON u.id o.user_id WHERE o.id IS NULL;结果只有王五。这个写法很经典也几乎只能放在WHERE放ON里没用。比如有人会想“我把o.id IS NULL写进ON不是一样吗”完全不一样。ON的作用是约束右表“哪些订单能匹配上”它读的是右表原始字段o.id IS NULL对真实存在的订单行来说永远是 FALSE所以右表找不到任何匹配行这时候LEFT JOIN的结果是每个用户都保留一条“右表全 NULL”的行。也就是说张三、李四、王五都会出现而且每个人的订单列都是 NULL你根本分不清谁是有订单的、谁是没有订单的。这个场景最能说明ON和WHERE的本质区别ON是在连接之前对右表做约束WHERE是在连接之后对结果做检查。2.4 场景三把左表字段条件放错位置的奇葩效果再看一个更容易迷惑人的坑。假设我现在只想看黄金用户的订单情况第一反应可能写SELECT u.id, u.name, u.level, o.id AS order_id, o.status FROM t_user u LEFT JOIN t_order o ON u.id o.user_id AND u.level gold;结果是什么所有用户都会保留包括李四和王五只是李四、王五的订单列变成 NULL。因为u.level gold这个条件放在ON里对左表行本身没有过滤作用它只影响右表匹配范围相当于告诉 MySQL“只有黄金用户去匹配订单非黄金用户的订单不需要关联”。如果业务要的是“只输出黄金用户的订单信息”那这个 SQL 就是错的。正确做法是把u.level gold放到WHERE里SELECT u.id, u.name, u.level, o.id AS order_id, o.status FROM t_user u LEFT JOIN t_order o ON u.id o.user_id WHERE u.level gold;所以这里有个容易忽略的经验如果条件字段来自左表想过滤左表行一定放WHERE如果只是想让右表匹配范围变窄同时左表行一个都不能少那才放ON。3. 在 MySQL 里实测结果、执行计划与索引视角3.1 先跑一遍结果验证语义使用上面 2.1 的表和数据把三种典型写法跑一遍-- 写法 A状态条件在 ON保留无订单用户 SELECT u.id, u.name, o.id AS order_id, o.status FROM t_user u LEFT JOIN t_order o ON u.id o.user_id AND o.status paid; -- 写法 B状态条件在 WHERE无已支付订单用户被过滤 SELECT u.id, u.name, o.id AS order_id, o.status FROM t_user u LEFT JOIN t_order o ON u.id o.user_id WHERE o.status paid; -- 写法 C用 IS NULL 找无订单用户 SELECT u.id, u.name, o.id AS order_id, o.status FROM t_user u LEFT JOIN t_order o ON u.id o.user_id WHERE o.id IS NULL;实际结果里面写法 A 返回前三行写法 B 只返回已经匹配到已支付订单的行函数 C 只返回王五。这种“肉眼可见”的验证方式比背任何理论都管用。遇到同事争论 on 和 where 的区别别吵直接跑一把结果自然说话了。3.2 用 EXPLAIN 看一眼执行计划的差别执行计划也是理解这个问题的关键材料。同样用上面的三句话在表结构和数据一致的环境下执行EXPLAIN观察到的典型差异是写法典型执行计划特征说明写法 Auser 表作为驱动表order 表作为被驱动表走 idx_user_statustype 为 refExtra 里可能有 Using where符合 LEFT JOIN 语义先把左表所有用户扫出来再去订单表匹配已支付订单写法 B优化器可能把 LEFT JOIN 改成 INNER JOIN两张表的 join 顺序也可能重新调整因为 WHERE 强制o.statuspaidNULL 扩展行不会留存LEFT JOIN 语义已经没有意义写法 Cuser 表驱动order 表匹配时先走索引再用 WHERE 判断右表主键 IS NULL经典的“查某表不在另一表”写法执行计划表现为先 JOIN 后过滤注意MySQL 的优化器非常聪明同一句 SQL 在不同版本、不同统计信息下计划可能不完全一样。我上面说的是比较常见的形态。重要的是你看到计划里 join 类型或连接顺序变化时要联想到是不是条件放错了位置。建议各位在自己的环境里多跑几遍EXPLAIN把三句话的type、key、rows、Extra都记下来。尤其是写法 B很多情况下type不再是Left Join那套流程而是直接变成内连接扫描这就是WHERE条件的“隐形杀伤力”在执行计划层面的体现。3.3 条件放哪对索引命中有什么影响索引选型方面ON和WHERE里的条件对索引的利用都有影响但主要不是“放哪里”决定索引好不好用而是“条件字段来自哪张表”决定。还是拿上面的例子来说-- 在 ON 里过滤右表 status LEFT JOIN t_order o ON u.id o.user_id AND o.status paid -- 在 WHERE 里过滤右表 status LEFT JOIN t_order o ON u.id o.user_id WHERE o.status paid前一种写法的扫描过程是先拿用户表的每一行去t_order的idx_user_status索引里查(user_id, status)都匹配的记录索引本身就能挡住 status 不满足的订单省去回表。后一种写法如果被优化器改成 INNER JOIN本质上也是拿连接条件去查索引status 条件也能利用索引过滤。所以在这两种场景下索引收益往往差不多。但有一种情况差别很大当业务确实需要保留左表所有行时只有 ON 能实现WHERE 会把 NULL 行干掉。这种场景你不能只考虑性能必须优先保住语义。再好的索引也不能让错误的结果变正确。还有一个常见的性能误区是有人为了“减少返回行数”故意把右表条件写到 WHERE让 LEFT JOIN 变得像 INNER JOIN以为能更快。这不一定优化器如果识别出 INNER JOIN执行计划和你直接写 INNER JOIN 是差不多的并没有额外收益。如果你的目标本来就是保留左表行却把条件写进 WHERE结果的错误会比性能问题严重得多。4. 现场排查方法一个稳定高效的 SQL 排障流程4.1 怀疑 LEFT JOIN 数据少了先检查 WHERE 引用了哪张表的列我在实际开发里遇到过很多次这种情况报表某个数字对不上或导出的名单少了一批人查来查去最后发现就是WHERE条件把 LEFT JOIN 的 NULL 行过滤掉了。遇到这类问题按这个顺序排查最快把 SQL 原样复制出来在纸上或编辑器里把所有条件按位置分成两堆ON里一堆WHERE里一堆。看WHERE里有没有出现“右表字段”的条件尤其是状态、时间、删除标记这类字段比如o.status paid、o.create_time 2024-01-01、o.is_deleted 0。一旦发现有右表字段在 WHERE 里过滤立刻做一次快速判断业务是否要求左表行全部保留如果要求保留把这些右表字段条件改到ON里并且用AND连接不要放在 WHERE。如果不要求保留其实建议直接改用INNER JOIN把语义写清楚省得以后维护的人又踩坑。这套流程我管它叫“条件归属排查法”。80% 的 LEFT JOIN 丢数据问题都能用它定位。4.2 三个快速判断依据直接套用有些同事总是记不住什么时候放 ON、什么时候放 WHERE我后来总结了三句话想保留左表全部行右表有条件条件放 ON。不想保留左表全部行就想过滤出满足条件的人条件放 WHERE。判断是否有匹配时用 WHERE 右表主键 IS NULL。这三句话基本覆盖了日常工作里 90% 的场景。剩下的一小部分是左右表字段交织的复杂过滤那种情况建议先拆解需求到底这个条件是“过滤最终结果”还是“约束关联范围”。前者去 WHERE后者去 ON。4.3 常见问题与症状速查表症状可能原因解决方案LEFT JOIN 结果里某些左表行不见了WHERE 中有右表非空字段条件NULL 扩展行被过滤把右表过滤条件移到 ON或改用 INNER JOIN 明确语义统计用户数时无订单用户没被统计进来条件在 WHERE 里把无匹配用户提前过滤了条件移到 ON再重新聚合同一句 SQL 在 INNER JOIN 下正确换成 LEFT JOIN 就少行没有意识到 LEFT JOIN 的 NULL 行会被 WHERE 过滤按“快速判断依据”重新归类条件查询“无订单用户”却返回一堆有订单的用户把 IS NULL 条件误写在了 ON 里IS NULL 判断放 WHEREON 只写连接条件用了 LEFT JOIN 但执行计划显示是 INNER JOINWHERE 对右表有强制过滤优化器重写计划确认是否符合真实业务需求尽量显式写 INNER JOIN多表 LEFT JOIN 后续 JOIN 还有条件数据越来越少后面每一层 JOIN 的 WHERE 都可能过滤上一层 NULL 行逐层检查每个 WHERE 条件的表归属和过滤意图这张表是我平时帮同事 review SQL 时最常用的对照表基本都是真实踩过的坑。4.4 一个非常管用的三行验证法最后分享我自己写报表 SQL 时必做的一个小验证动作特别是涉及多表 LEFT JOIN 的时候第一行先查左表基础数据量SELECT COUNT(*) FROM t_user WHERE 左表自己的过滤条件;第二行用 LEFT JOIN 查最终明细并统计左表主键的去重数量SELECT COUNT(DISTINCT u.id) FROM t_user u LEFT JOIN t_order o ON u.id o.user_id AND o.status paid;第三行如果第二步的统计数字明显小于第一步那几乎可以断定条件放错了位置第三步就赶紧回头看 WHERE 里有没有右表字段条件。这个习惯帮我挡掉了无数次线上数据事故。很多时候我们写完 SQL 只看“结果格式对不对”根本注意不到有一批用户被 WHERE 静默吃掉了。每次把两个数字对不上号当成重要信号来对待比用任何高端工具排查都管用。尤其是在做用户留存、漏斗分析、会员画像这类场景时左表是核心用户池少一个人整个运营决策都可能受影响值得每次多花十秒验证一遍。