1. 一条“看起来没问题”的SQL结果却少了三行1.1 同事拿过来的SQL到底写了什么事情发生在某次周五下午邻座同事突然凑过来语气很急“帮我看看这条SQLleft join之后筛个状态怎么就少了三行”我点开他发来的语句大致是这样的SELECT u.user_name, o.order_no, o.status FROM users u LEFT JOIN orders o ON u.uid o.uid WHERE o.status 1;他想要的语义很简单把用户全部列出来如果用户有有效订单status1就把订单信息带出来没有订单的就留空。这需求一听就是典型的left join场景但他写完发现没有有效订单的用户整行都消失了而不是像预期那样保留用户、订单字段为NULL。我第一反应就是老问题——left join的on和where位置放错了。这不是语法错误而是绝大多数从inner join转到left join的人都会踩的语义坑。MySQL的语法上两类条件都能写但执行阶段和生效时机完全不同。这篇文章就围绕“left join中where和on的区别”这一个点把执行顺序、四类写法的差异、版本带来的行为变化以及实际排查方法一次讲透。不管你是刚写CRUD的新手还是被这类问题坑过好几次的熟手应该都能从里面找到对应的答案。1.2 从“怀疑数据”到“怀疑SQL”的排查路径先说下当时的排查过程这个思路比答案本身更值钱。同事一开始怀疑是订单表数据有问题于是单独跑了一条查询SELECT COUNT(*) FROM orders WHERE status 1;结果订单数据正常status1的订单确实存在。接着他又试了把left join改成inner join发现行数跟出问题的SQL一致这就直接戳中了要害——如果left join被where条件“吃掉”了空值行它的行为就会退化得和inner join一模一样。到这一步基本可以断定问题不出在数据出在条件写的位置。后来我把他的where条件挪到on后面变成SELECT u.user_name, o.order_no, o.status FROM users u LEFT JOIN orders o ON u.uid o.uid AND o.status 1;结果行数立刻恢复正常没有有效订单的用户也在结果里只是订单字段为NULL。同事当时很惊讶“同一个条件换个位置怎么就不一样了”这就是本文要解决的核心疑问。1.3 先记住最核心的一句话我不喜欢绕弯子先把结论放在这里on决定的是“怎么拼”where决定的是“拼完之后留哪些”。在left join中on里的右表条件只影响右表的匹配过程不影响左表行的保留where里的右表条件是在join完成之后对结果集做过滤一旦右表字段不满足条件整行都会被删掉包括原本应该保留的左表记录。换句话说left join where右表条件最终效果约等于inner join。很多线上Bug就是这么来的查出来的数据“好像没什么问题”但总感觉少了几条偏偏大多数时候业务只看有数据的行这个坑就一直埋在SQL里直到某天统计全量数据时突然爆发。2. left join“先on后where”的执行顺序我手工推演了一遍2.1 MySQL里join大体上是这么跑的要真正理解上面的结论得先知道MySQL执行join时内部大概做了些什么。虽然优化器会有各种改写和优化但基础执行模型可以理解为嵌套循环连接拿第一张表的每一行去第二张表里找满足on条件的行找到就拼接输出找不到就补NULL。在left join场景下左侧的表是“驱动表”它会被完整地扫描并保留所有行。右侧的表是“被驱动表”只有当左侧行满足on条件时才去右侧匹配。匹配成功就正常拼接匹配不到就把右侧字段填成NULL。这个过程发生在on阶段也是left join区别于inner join的根本所在。这个模型虽然简化了很多东西比如优化器可能选择倒过来驱动、可能走索引、可能用hash join但语义上还是这个逻辑。理解它再去分析各种SQL写法就像手里有了一张地图不会迷路。2.2 on里的右表条件为什么“拦不住”左表很多人的困惑在于on条件里写了o.status 1为什么左表里那些没有满足条件的行还是会被输出这是因为在嵌套循环里on条件是“匹配规则”。对于驱动表的某一行如果匹配规则不满足结果不是“这一行消失”而是“右侧没有找到匹配行于是补NULL”。这跟where完全不同。where是“最终结果集过滤器”它不关心某一行是怎么拼出来的只看这一行的字段值是否满足条件。如果一行中右表字段是NULL并且where写成o.status 1那么NULL 1的结果是UNKNOWN这一行在where阶段就被扔掉了。所以“拦不住”这个词其实不准确on并不是拦不住而是它的职责里根本没有“拦掉左表行”这一项。它只负责告诉join这一行右表数据该不该被拼上来。2.3 手工推演一遍结果就清清楚楚了光讲理论有点干我们来手工模拟一次。假设有下面两张表users表uiduser_name1张三2李四3王五orders表oiduidstatus101111021010321先看第一种写法LEFT JOIN orders o ON u.uid o.uid AND o.status 1。执行过程是这样的uid1的张三拿uid1去orders里找匹配101status1拼上102status0不满足on条件不拼最终输出一行1 张三 101 1。uid2的李四匹配103status1输出2 李四 103 1。uid3的王五没有任何订单匹配右侧补NULL输出3 王五 NULL NULL。再看第二种写法LEFT JOIN orders o ON u.uid o.uid WHERE o.status 1。这里on阶段只有u.uid o.uid一个匹配规则所以张三除了匹配到101还会匹配到102。join完成后的中间结果集是uiduser_nameoidstatus1张三10111张三10202李四10313王五NULLNULL然后where阶段执行status 1102和王五那一行直接被过滤留下的只有两行。这就是为什么同事的SQL少了三行里的两行——不对他说的少了三行具体少几行取决于他的真实数据量但机制就是这个机制。3. 四类写法逐一验证on左、on右、where左、where右3.1 先准备一份能复现问题的测试数据理论推演毕竟不如动手验证来得踏实。我在本地建了一张用户表和一张订单表数据尽量覆盖“有订单无有效订单”“有多个订单”“完全无订单”三种情况方便观察所有分支。CREATE TABLE users ( uid INT PRIMARY KEY, user_name VARCHAR(50) ); CREATE TABLE orders ( oid INT PRIMARY KEY, uid INT, status TINYINT, amount DECIMAL(10,2) ); INSERT INTO users VALUES (1, 张三), (2, 李四), (3, 王五); INSERT INTO orders VALUES (101, 1, 1, 100.00), (102, 1, 0, 50.00), (103, 2, 1, 200.00);就这么三条用户、三条订单已经能把所有门类覆盖到。下面四个验证都以这两张表为基础查询字段统一是u.uid, u.user_name, o.oid, o.status方便对比。3.2 验证一on后面跟右表条件第一组测试把订单状态的过滤写在on里SELECT u.uid, u.user_name, o.oid, o.status FROM users u LEFT JOIN orders o ON u.uid o.uid AND o.status 1;执行结果uiduser_nameoidstatus1张三10112李四10313王五NULLNULL注意张三有两个订单但status0的那条没有出现在结果里。on条件的过滤作用体现在这里它让无效订单不参与拼接。而王五因为没有匹配到任何订单仍然保留在结果中右侧为NULL。这个结果是完全符合left join语义的——用户全部列出有有效订单的带上有效订单没有的就是NULL。3.3 验证二where后面跟右表条件第二组测试同一个条件挪到where里去SELECT u.uid, u.user_name, o.oid, o.status FROM users u LEFT JOIN orders o ON u.uid o.uid WHERE o.status 1;执行结果uiduser_nameoidstatus1张三10112李四1031王五整行消失了因为他在join之后o.status是NULLNULL 1不成立被where过滤。张三的102订单也消失了。这个结果和inner join完全一致不信可以自己跑一下。所以我说left join where右表条件的语义就是“只保留有匹配行的记录”跟left join的初衷相去甚远。3.4 验证三on/where后面跟左表条件第三组测试有点意思很多人没意识到左表条件写在on里也有坑。比如想查“uid大于1的用户及其订单”先看写在on里的写法SELECT u.uid, u.user_name, o.oid, o.status FROM users u LEFT JOIN orders o ON u.uid o.uid AND u.uid 1;执行结果uiduser_nameoidstatus1张三NULLNULL2李四10313王五NULLNULLuid1的张三还在只是订单不显示了因为on里要求u.uid 1张三不满足所以匹配不到任何订单。也就是说on里写左表条件不会过滤左表行但会悄悄影响右表匹配——这往往比where右表条件更隐蔽。再看正确的where写法SELECT u.uid, u.user_name, o.oid, o.status FROM users u LEFT JOIN orders o ON u.uid o.uid WHERE u.uid 1;执行结果uiduser_nameoidstatus2李四10313王五NULLNULL这次张三被过滤了符合“查uid大于1的用户”的预期。所以当你需要过滤左表行时条件应该放在where当你需要控制右表匹配范围时条件放在on。这个边界一定要清晰。3.5 顺手验证一下inner join里on和where没有区别既然说了left join里on和where差异显著那顺手也验证一下inner join。在inner join里无论条件写在on还是where语义都是一样的因为inner join本来就不要求保留左侧的全部行join之后被过滤掉的行本来就是最终结果的一部分。-- 这两种写法结果完全一致 SELECT u.uid, u.user_name, o.oid, o.status FROM users u JOIN orders o ON u.uid o.uid AND o.status 1; SELECT u.uid, u.user_name, o.oid, o.status FROM users u JOIN orders o ON u.uid o.uid WHERE o.status 1;所以很多人形成“on和where差不多”的惯性都是从inner join来的一换到left join就翻车。这也是为什么遇到left join语义问题时先确认是不是从inner join改过来的新手写的往往能少走很多弯路。4. on里面写子查询MySQL版本不同结果真的会变4.1 一个被“优化器好心办坏事”的案例前阵子帮朋友排查一个慢查询SQL长这样目标是想找出每个用户最近一笔有效订单SELECT u.user_name, o.oid, o.amount FROM users u LEFT JOIN orders o ON u.uid o.uid AND o.oid ( SELECT MAX(oid) FROM orders WHERE uid u.uid AND status 1 );从语义上讲这串SQL没什么毛病先按关联条件把用户和订单拼上再通过on里的子查询限制只取每个用户最大的有效订单。但实际跑出来没有有效订单的用户也被过滤掉了效果跟inner join一模一样。当时第一反应是子查询返回值有问题单独执行子查询一切正常。后来查看执行计划发现优化器在旧版本MySQL上把on子句里的关联子查询做了上拉处理把原本在join阶段执行的逻辑挪到了where阶段于是left join的保留语义被破坏。这就是典型的“优化器好心办坏事”它认为这样改写能提升性能但语义上偷换了概念。4.2 MySQL 8.0.22前后的行为差异这里要说到版本问题了。MySQL在8.0.22之前对outer join的on子句里包含关联子查询这种情况处理上一直存在行为不一致的隐患。某些场景下子查询会被上拉到where阶段执行导致left join退化8.0.22之后优化器对on子句中的子查询做了更严格的处理不再随意上拉上述SQL在语义上才稳定下来。但这绝不意味着你可以无脑在on里写子查询。8.0.22修复的是“部分场景”实际业务中还有大量物化、派生表、半连接相关的优化分支。我自己在MySQL 5.7和8.0.16上都遇到过类似问题排查成本不低。遇到数据量大的表一个优化器改写就能让执行计划面目全非索引选错、驱动顺序颠倒都是常事。建议是如果要在on里写子查询至少在目标版本上做一次explain验证并且对比“子查询写法”和“先查出子查询结果再join”两种方案的返回行数。如果返回行数不一致不用怀疑就是优化器改写改变了语义。4.3 什么时候能放心在on里写子查询结合踩坑经历我总结了三类相对安全的场景子查询不引用外层驱动表的任何字段是一个完全独立的标量子查询比如o.oid (SELECT MAX(oid) FROM orders WHERE status 1)这种形式优化器改写空间小行为稳定。子查询作为派生表先于join执行也就是LEFT JOIN (SELECT ...) tmp ON ...此时子查询已经物化成临时表不会跨阶段上拉。当前MySQL版本确认在8.0.22及以上并且explain结果中能看到Using join buffer、Using where等预期信息同时返回行数与业务预期一致。其余情况尤其是长年在旧版本上跑业务的团队我建议保守一点把子查询改写成临时表或者先获取结果集再关联。性能上的差距往往没有语义正确性重要一个“数据看起来对但实际不对”的SQL比慢查询危险得多。5. 遇到这类问题我的完整排查链路与三个避坑习惯5.1 一次完整的排查过程从“行数不对”到“定位根因”回到同事那个问题后来我把整个排查链路走了一遍步骤可以复现先确认差异行数。分别跑SELECT COUNT(*)统计left join结果、inner join结果以及只有左表的记录数。如果left join的count小于左表count说明有空值行被过滤了问题极可能出在where。把过滤条件逐个挪位。将where里所有右表字段的条件全部搬到on里重新执行对比行数变化。一次只能动一个条件避免多个条件叠加看不清。用explain看执行计划。重点看两张表的join顺序、type字段和Extra里的Using where。如果第二张表出现了Using where而第一张表仍是全表驱动基本印证了where阶段过滤。EXPLAIN SELECT u.uid, u.user_name, o.oid, o.status FROM users u LEFT JOIN orders o ON u.uid o.uid WHERE o.status 1;在MySQL 8.0上优化器甚至可能直接显示第二张表的type不是ALL而是ref并且Extra中出现Using where说明整条语句已经被改写成inner join语义了。临时改写SQL验证。把left join改成inner join跑一遍如果结果集完全一致那绝对是where把NULL行过滤掉了。这一步排查速度最快推荐优先做。5.2 三个最容易踩的坑每个我都见过线上事故第一个坑就是本文主角左表全量保留的需求结果把右表过滤条件写在where里。这种问题往往在数据量小的时候看不出来因为业务查询通常只关心有数据的行等做统计报表、全量导出时才发现不对。第二个坑是用右表字段做分页统计。比如先查出总页数再分页取数SQL写成SELECT COUNT(*) FROM users u LEFT JOIN orders o ON u.uid o.uid WHERE o.oid IS NULL;这里的o.oid IS NULL是合理用法找的是没有订单的用户很多人分不清。但如果写成WHERE o.status 1分页的条数会变少且列表里缺失的用户根本无迹可寻排查起来特别费劲。第三个坑是on里写左表条件但没有意识到它会影响匹配。前面验证三已经展示了ON u.uid o.uid AND u.uid 1并不会过滤掉张三但会让张三的订单全部变NULL。这种SQL特别容易让业务方以为“这个用户没订单”实际他只是没被on条件命中而已。5.3 我平时写left join的三个习惯分享给你第一写left join先想清楚“要保留谁”。需要保留左表全部行所有对右表的过滤条件都写在on里需要在join之后对整体结果集做筛选才考虑写where。如果一次查询里既要保留左表全量、又要过滤某些左表行那就把左表过滤写where右表过滤写on分开处理。第二用explain当作安全检查。尤其遇到多表join、子查询、复杂条件组合时explain可以看到优化器最终把SQL改写成什么样。如果发现left join的驱动顺序变了或者第二张表的Extra出现异常就要警惕语义被改写。第三临时把left join换成inner join做对比。这条技巧成本最低却最容易发现问题。如果两种写法返回的行数一样说明left join的“保留左侧”特性已经被某个条件破坏此时就该一层层检查条件位置了。这个习惯帮我在代码评审时抓出过好几个潜在Bug屡试不爽。回到我个人的体会SQL的语法学起来容易但语义边界往往藏在执行细节里。left join中on和where的区别本质上是“数据如何在两表之间流动”的问题。理解了执行顺序很多看似玄学的结果其实都是必然。以后再遇到类似“少了三行”的问题不用急着怀疑数据先低头看看自己的条件写在哪里。
