写 SQL 写了快十年带过的实习生几乎都在同一个地方栽过跟头——LEFT JOIN 里的过滤条件到底应该写在 ON 后面还是 WHERE 后面问十个人九个能背出写在 ON 里这个结论但再追问一句为什么就支支吾吾说不清了。直到某天报表数据对不上LEFT JOIN 出来的行数比左表少了一大截才发现自己其实根本没搞懂这两个关键字的执行逻辑。这个问题的本质是 SQL 中 JOIN 的执行时机与 WHERE 的过滤时机不同。搞清楚它不只为了应付面试题更多是避免在真实业务里写出看似正确、结果全错的查询。这篇文章会从 JOIN 类型讲起把 ON 和 WHERE 的执行顺序、典型场景、多表关联写法、常见坑位都过一遍希望能帮你彻底理顺这个知识点。1. JOIN 类型速览先分清 INNER、LEFT、RIGHT、FULL1.1 四种 JOIN 的语义对比在讨论 ON 和 WHERE 之前得先把 JOIN 家族的家底摸清。很多困惑都源于对 JOIN 类型本身的语义只有模糊印象遇到问题时自然就乱了。INNER JOIN内连接只返回两张表中都能匹配上的行。左表有、右表没有的右表有、左表没有的统统丢弃。这是最严格的连接方式两边必须门当户对。LEFT JOIN左连接以左表为基准左表的每一行都会出现在结果集里。右表能匹配上就带出右表字段匹配不上就用 NULL 填充。RIGHT JOIN右连接和 LEFT JOIN 方向相反以右表为基准右表所有行保留左表匹配不上则补 NULL。FULL JOIN全连接两边的数据都完整保留匹配不上的行对应另一侧的字段就是 NULL。这个在 MySQL 里需要 UNION 模拟其他主流数据库基本原生支持。拿一个直观的集合图来理解把两表想象成两个圆圈INNER JOIN 取交集LEFT JOIN 取左圈全部RIGHT JOIN 取右圈全部FULL JOIN 取并集。这个图很简单但多想想保留哪些行这个问题后面理解 ON 和 WHERE 就不会跑偏。JOIN 类型左表未匹配的行右表未匹配的行两边匹配的行INNER JOIN丢弃丢弃保留LEFT JOIN保留以 NULL 填充保留保留RIGHT JOIN以 NULL 填充保留保留保留FULL JOIN以 NULL 填充保留以 NULL 填充保留保留1.2 为什么 ON 和 WHERE 容易让人懵理解了 JOIN 类型接下来得说说困惑的根源在哪。我个人觉得有三点第一INNER JOIN 场景下ON 和 WHERE 的结果几乎完全等价。比如FROM A INNER JOIN B ON A.id B.aid AND B.status 1和FROM A INNER JOIN B ON A.id B.aid WHERE B.status 1跑出来的结果集是一样的。很多教程和课程都只讲了这一种场景学习者背下了两者都能用却不知道这只是 INNER JOIN 下的特例。第二定义没说透。教材会告诉你ON 是连接条件WHERE 是过滤条件但没说清楚这两个条件分别在 SQL 执行的哪个环节起作用。一旦换成 LEFT JOIN连接条件和过滤条件的差异就被放大了没有执行顺序的概念自然只能靠死记硬背。第三日常业务里 LEFT JOIN 太常用了。订单、用户、商品、支付流水稍微复杂点的业务表都要靠 JOIN 串起来。只要有一次把过滤条件放错位置数据就悄悄变少而且这种错误往往不是报错只是结果不对排查起来非常费劲。拿一个生活化的类比来说JOIN 的过程像相亲配对ON 是介绍人手里的配对规则——什么条件的人才拉在一起相见WHERE 则是见面之后的最终筛选——不符合要求的一律pass。INNER JOIN 相当于只把配对成功且通过筛选的人留在屋里LEFT JOIN 则是左表的人一个都不能走配对成功的右表嘉宾可以留下没配对成功的右表位置空着。这时如果你在 WHERE 里多加一条右表嘉宾必须带礼物那左表那些空着位置的人就因为右表字段是 NULL 被清走了屋里瞬间少了一大批人。2. ON 与 WHERE 的本质差异执行顺序决定结果2.1 SQL 的逻辑执行顺序要真正理解 ON 和 WHERE绕不开 SQL 的执行顺序。虽然数据库优化器会根据自己的规则调整实际执行计划但最终返回的结果必须和下面这个逻辑顺序等价FROM → JOIN / ON → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT这个顺序里最关键的是前两步。FROM 负责确定数据源JOIN 阶段根据 ON 条件把表拼接起来此时 LEFT JOIN 之类的保留语义就会生效。JOIN 完成之后才轮到 WHERE 对拼接完成后的结果集做整体过滤。再往后 GROUP BY 分组、HAVING 过滤分组、SELECT 投影、ORDER BY 排序、LIMIT 截断。很多人把 WHERE 当成JOIN 之前就过滤的步骤这是错的。对于 INNER JOIN优化器确实可能先做过滤再连接因为结果等价但对于 LEFT JOIN优化器不能随意调整这个顺序否则会破坏左表全保留的语义。所以你在逻辑上必须理解WHERE 作用于 JOIN 之后的中间结果。2.2 为什么这个顺序对 LEFT JOIN 至关重要LEFT JOIN 的保留左表全部行这个承诺是在 JOIN 阶段兑现的。一旦过了 JOIN 阶段进入 WHERE这个承诺就失效了。WHERE 会对 JOIN 之后的整个结果集做过滤包括左表的行。这里还牵扯到 SQL 的三值逻辑。SQL 里的比较运算结果不只是 TRUE 和 FALSE还有第三个状态——UNKNOWN。任何和 NULL 做比较的结果都是 UNKNOWN。WHERE c.status unused遇到某个客户压根没有优惠券、JOIN 之后 c.status 是 NULL 时这个比较结果是 UNKNOWN不会被保留。WHERE 子句只保留结果为 TRUE 的行UNKNOWN 和 FALSE 一样被丢弃。这就是LEFT JOIN 结果行数比左表少的最常见原因你把右表的过滤条件写进了 WHERE那些右表匹配不上、字段为 NULL 的行全被 WHERE 给过滤掉了。看起来你写的是 LEFT JOIN实际跑出来的效果却接近 INNER JOIN左表数据无声无息地减少。提示LEFT JOIN 的保留左表全部行承诺只在 JOIN 阶段有效。一旦进入 WHERE 阶段这个承诺就失效了。2.3 两者本质分工的一句话总结可以把 ON 和 WHERE 的分工压缩成一句口诀ON 决定怎么连WHERE 决定连完之后留下谁。更准确地说ON 在 JOIN 阶段控制左右表如何匹配、哪些右表行有资格参与连接WHERE 在 JOIN 完成后对包括左表和右表在内的整个结果集做最终过滤。搞懂了这一点很多诡异的数据结果就有了合理的解释。接下来我会用一个完整的业务案例把两种写法的差异直接放在桌面上对比。3. 实战拆解LEFT JOIN 中 ON 与 WHERE 的结果差异3.1 案例用户与优惠券理论讲得再多不如上一套可以直接跑的数据。我自己带新人时最常用用户-优惠券这个场景简单又贴近业务。先建两张表一张用户表users一张优惠券表coupons。用户表里有 3 个用户优惠券表里每个用户有几张状态不同的券。CREATE TABLE users ( user_id INT PRIMARY KEY, user_name VARCHAR(50), register_date DATE ); CREATE TABLE coupons ( coupon_id INT PRIMARY KEY, user_id INT, status VARCHAR(20), -- unused / used / expired amount DECIMAL(10,2) ); INSERT INTO users VALUES (1, 张三, 2024-01-15), (2, 李四, 2024-02-10), (3, 王五, 2024-03-05); INSERT INTO coupons VALUES (101, 1, unused, 50.00), (102, 1, used, 30.00), (103, 2, unused, 20.00), (104, 3, expired, 100.00);现在业务需求来了要统计每个用户手里未使用优惠券的情况并且所有用户都要显示出来哪怕他一张未使用券都没有。3.2 条件放 ON保留所有用户按需求未使用优惠券这个过滤条件应该放在 ON 里SQL 这样写SELECT u.user_id, u.user_name, c.coupon_id, c.status, c.amount FROM users u LEFT JOIN coupons c ON u.user_id c.user_id AND c.status unused;执行结果长这样user_iduser_namecoupon_idstatusamount1张三101unused50.002李四103unused20.003王五NULLNULLNULL所有用户都保留了。张三和李四各自匹配到了未使用券王五只有一张已过期的券因为 ON 条件里加了c.status unused所以这张过期券没有资格参与连接右边补了 NULL。这正好满足所有用户都要显示出来的需求。这里的关键是ON 里的条件是 JOIN 匹配规则的一部分它决定了右表哪些行有资格和左表匹配。不满足条件的右表行直接不参与连接但左表的行不会因此消失。3.3 条件放 WHERE用户被悄悄过滤接下来看反面写法。如果把同样的过滤条件放到 WHERE 里SELECT u.user_id, u.user_name, c.coupon_id, c.status, c.amount FROM users u LEFT JOIN coupons c ON u.user_id c.user_id WHERE c.status unused;执行结果user_iduser_namecoupon_idstatusamount1张三101unused50.002李四103unused20.00王五消失了。原因是整个查询分两步走第一步 LEFT JOIN 按u.user_id c.user_id连接王五匹配到了 coupon_id104 那条过期券右侧字段有值第二步 WHERE 判断c.status unused王五的 status 是expired比较结果是 FALSE被过滤掉了。你可能会说那如果王五一张券都没有呢那情况更微妙——LEFT JOIN 之后王五的 c.status 是 NULLc.status unused这个比较的结果是 UNKNOWN同样不会被保留。所以无论右表是有记录但不满足条件还是没记录WHERE 写法都会把左表行过滤掉。3.4 结果对比与到底该怎么选把两种写法放在一起对比差异一目了然业务需求推荐写法原因左表所有行都要保留右表只取满足条件的记录过滤条件放 ON不破坏 LEFT JOIN 保留语义只保留 JOIN 后满足整体条件的行过滤条件放 WHERE相当于对结果集做全局筛选不关心左表是否全保留只要匹配数据INNER JOIN WHERE语义清晰可读性更好实际工作中我的选择标准非常朴素先问自己这个查询要保留哪张表的全部行。如果要保留左表全部行凡是针对右表的过滤条件全部进 ON如果不需要保留全部行干脆用 INNER JOIN把连接条件放 ON、过滤条件放 WHERE层次分明。很多人在 LEFT JOIN 里把过滤条件写进 WHERE本质是没用 INNER JOIN 的写法硬套 LEFT JOIN结果把自己绕进去了。4. 多表关联ON 与 WHERE 的组合实战4.1 三表 JOIN 的 ON 条件写法业务里很少只有两张表。订单、订单明细、商品、用户、支付信息多表 JOIN 是家常便饭。这时候 ON 和 WHERE 的配合更要谨慎。假设有三张表订单表orders、订单明细表order_items、商品表products。要查所有订单、订单里的商品明细和商品名称SELECT o.order_id, o.user_id, oi.quantity, p.product_name FROM orders o LEFT JOIN order_items oi ON o.order_id oi.order_id LEFT JOIN products p ON oi.product_id p.product_id;这段 SQL 的 JOIN 是一个接一个进行的orders先和order_items连接生成一个中间结果这个中间结果再作为左表继续和products连接。每一步都是独立的 JOINON 条件只针对当前这一步。多表 JOIN 时一个常见需求是订单总有三种状态pending、paid、cancelled我只想保留 paid 的单子但订单表所有行都要这听起来矛盾其实取决于视角。如果我想统计每个用户的已支付订单但用户全保留那订单表是右表过滤条件应该放 ON。如果把需求反过来——每个已支付订单的详情订单表之外的其他表能带就带那 ORDER 就是主表o.status paid应该放 WHERE。同一张表的同一个条件放 ON 还是 WHERE完全取决于主表是谁。这就是先确定主表的重要性。4.2 条件聚合场景COUNT 里的坑多表 JOIN 经常配合聚合函数做统计这里的 ON 和 WHERE 位置错误后果不只是行数变少而是统计口径彻底变化。举个最常见的需求统计每个用户已使用的优惠券数量所有用户都要出现在结果里没用过券的用户显示 0。正确写法是用 ON 过滤SELECT u.user_id, u.user_name, COUNT(c.coupon_id) AS used_coupon_cnt FROM users u LEFT JOIN coupons c ON u.user_id c.user_id AND c.status used GROUP BY u.user_id, u.user_name;结果里王五会出现used_coupon_cnt为 0。为什么能正确统计因为 LEFT JOIN 时王五没有匹配到已使用券c.coupon_id 是 NULLCOUNT 不会统计 NULL所以结果是 0而不是把他过滤掉。如果条件写进 WHERESELECT u.user_id, u.user_name, COUNT(c.coupon_id) AS used_coupon_cnt FROM users u LEFT JOIN coupons c ON u.user_id c.user_id WHERE c.status used GROUP BY u.user_id, u.user_name;结果里王五直接消失整个统计变成了只有用过券的用户才出现在报表里口径彻底变了。如果这是给老板看的经营分析表这个错误会导致用户基数偏差。还有一种更骚的写法一次性统计好几列。用条件计数代替多个子查询性能更好SELECT u.user_id, COUNT(o.order_id) AS total_orders, COUNT(CASE WHEN o.status paid THEN 1 END) AS paid_orders, COUNT(CASE WHEN o.status cancelled THEN 1 END) AS cancelled_orders FROM users u LEFT JOIN orders o ON u.user_id o.user_id GROUP BY u.user_id;这里的 CASE WHEN 只做条件计数COUNT 不会因此过滤掉用户行未匹配订单的用户这些计数都是 0。这种写法很适合做宽表汇总比绕好几层临时表清晰得多。4.3 什么时候放 WHERE 更合理说完 ON 的优势也得承认 WHERE 在有些场景下更合理不能走到另一个极端。第一种INNER JOIN 时。连接条件和过滤条件分开写代码可读性最好。FROM a INNER JOIN b ON a.id b.aid WHERE b.status 1比把 status 塞进 ON 里更容易让同事看懂。第二种过滤条件针对主表本身。无论放哪LEFT JOIN 都会因为主表行被过滤而导致行数变少这时更符合业务预期的是明确用 WHERE 表达我就是要筛选主表。比如FROM users u LEFT JOIN orders o ON ... WHERE u.register_date 2024-01-01用户表主表被过滤是合理的。第三种数据集很小、性能没有压力。在这个前提下ON 和 WHERE 的细微语义差异可以被忽略优先保证 SQL 直观好懂。4.4 ON 条件的另一种用法限定关联范围多表 JOIN 时 ON 还可以做一件很多新手不知道的事通过 AND 附带条件缩小右表参与连接的范围而不影响主表。比如查所有用户及其已支付的订单可以写SELECT u.user_id, o.order_id, o.amount FROM users u LEFT JOIN orders o ON o.user_id u.user_id AND o.status paid;这就是前面核心观点的延伸ON 的 AND 子句相当于右表筛选器只让满足条件的右表行参与匹配。在复杂报表里这种写法的价值非常大。比如统计每个客户最近一笔已支付订单的时间就可以在这种 ON 限定的 JOIN 基础上配合 MAX 聚合。5. 常见问题速查与排查技巧5.1 症状LEFT JOIN 结果行数比左表少这是我被问到最多的一个问题。损害已经造成关键是快速定位。我自己的排查套路是先把 WHERE 子句里涉及右表的条件全部注释掉重新跑一遍看行数是否恢复到左表的行数。如果恢复了那基本可以断定是 WHERE 条件把右表未匹配的 NULL 行过滤了。把对应条件从 WHERE 挪到 ON 里再次对比行数。如果行数有差异用EXPLAIN看执行计划确认 JOIN 类型和过滤下推的实际情况。这个套路帮我排查过不下二十次同类问题基本 90% 的情况三步以内就定位了。5.2 NULL 三值逻辑的坑SQL 里的 NULL 是个易错点处理 JOIN 后的过滤尤其容易踩坑。c.status ! unused并不是除了 unused 之外都算因为 NULL 不等于 unused 的结果是 UNKNOWN而 WHERE 只保留 TRUE。比如业务上想查有未使用券之外的其他券或者压根没券的用户如果写成WHERE c.status ! unused那么没券的用户c.status 为 NULL会被丢掉因为 NULL ! unused 是 UNKNOWN不是 TRUE。同时有已使用券的用户会被保留。如果要把没券的情况也保留必须显式加 IS NULLWHERE c.coupon_id IS NULL OR c.status ! unused大多数开发者在写!、、时都没有意识到 NULL 会导致行数丢失。我的建议是凡是涉及右表字段的比较条件先想想这个字段会不会是 NULL。如果可能为 NULL要么用 IS NULL 语义要么把条件放到 ON 里让那些行继续保持 NULL。5.3 性能与索引的实操建议除了语义正确性能也是 JOIN 绕不开的话题。分享几个实操层面的经验ON 条件字段必须有索引。JOIN 的关联字段没有索引大表关联时会全表扫描查询能慢到你怀疑人生。user_id这种高频关联字段一般建议建普通索引。避免在 ON 条件上做函数运算。比如ON DATE(o.create_time) DATE(u.register_date)这种写法会导致索引失效。可以提前把日期截断成字段或者用范围条件o.create_time 2024-01-01 00:00:00这类写法。小表驱动大表。优化器通常会自己选择合适的连接顺序但如果你用STRAIGHT_JOIN或控制 FROM 顺序一般原则是让结果集小的表先参与连接缩小中间结果集。WHERE 过滤右表大量数据时直接考虑 INNER JOIN。如果业务上确实不需要保留主表 NULL 行写 INNER JOIN 让优化器有更多调优空间通常查询计划也更好。用 EXPLAIN 验证。类似 Oracle 的EXPLAIN PLANMySQL 里EXPLAIN SELECT ...可以看访问类型、索引使用情况。type 列出现ALL就要警惕全表扫描。5.4 一个小技巧先想清楚再动笔最后分享一个我个人的工作习惯写 JOIN 之前永远先回答三个问题——主表是哪张、主表要保留哪些行、右表的哪些条件只是限定关联数据的。这三个问题想清楚再动笔基本不会写错。我也会在写完 SQL 之后习惯性估算一下结果行数。比如主表有 10000 行LEFT JOIN 之后如果行数超过 10000那多半是有重复关联如果少于 10000那说明某个条件偷偷过滤了主表行。这个简单的行数核对比我对着字段一个个检查效率高得多。最后再啰嗦两句写 SQL 这些年我越来越觉得一个知识点的掌握程度不在于能不能说出定义而在于遇到异常结果时能不能快速定位。ON 和 WHERE 的区别说穿了就是JOIN 阶段控制匹配规则和JOIN 完成后的整体过滤的区别。INNER JOIN 里它们结果相同掩盖了差异LEFT JOIN 里差异立刻显现。下次再写 JOIN先在心里过一遍执行顺序再决定条件放哪这个习惯能帮你避开数据报表里最大的隐形坑。
LEFT JOIN 的 ON 与 WHERE 到底怎么选？执行顺序才是关键
写 SQL 写了快十年带过的实习生几乎都在同一个地方栽过跟头——LEFT JOIN 里的过滤条件到底应该写在 ON 后面还是 WHERE 后面问十个人九个能背出写在 ON 里这个结论但再追问一句为什么就支支吾吾说不清了。直到某天报表数据对不上LEFT JOIN 出来的行数比左表少了一大截才发现自己其实根本没搞懂这两个关键字的执行逻辑。这个问题的本质是 SQL 中 JOIN 的执行时机与 WHERE 的过滤时机不同。搞清楚它不只为了应付面试题更多是避免在真实业务里写出看似正确、结果全错的查询。这篇文章会从 JOIN 类型讲起把 ON 和 WHERE 的执行顺序、典型场景、多表关联写法、常见坑位都过一遍希望能帮你彻底理顺这个知识点。1. JOIN 类型速览先分清 INNER、LEFT、RIGHT、FULL1.1 四种 JOIN 的语义对比在讨论 ON 和 WHERE 之前得先把 JOIN 家族的家底摸清。很多困惑都源于对 JOIN 类型本身的语义只有模糊印象遇到问题时自然就乱了。INNER JOIN内连接只返回两张表中都能匹配上的行。左表有、右表没有的右表有、左表没有的统统丢弃。这是最严格的连接方式两边必须门当户对。LEFT JOIN左连接以左表为基准左表的每一行都会出现在结果集里。右表能匹配上就带出右表字段匹配不上就用 NULL 填充。RIGHT JOIN右连接和 LEFT JOIN 方向相反以右表为基准右表所有行保留左表匹配不上则补 NULL。FULL JOIN全连接两边的数据都完整保留匹配不上的行对应另一侧的字段就是 NULL。这个在 MySQL 里需要 UNION 模拟其他主流数据库基本原生支持。拿一个直观的集合图来理解把两表想象成两个圆圈INNER JOIN 取交集LEFT JOIN 取左圈全部RIGHT JOIN 取右圈全部FULL JOIN 取并集。这个图很简单但多想想保留哪些行这个问题后面理解 ON 和 WHERE 就不会跑偏。JOIN 类型左表未匹配的行右表未匹配的行两边匹配的行INNER JOIN丢弃丢弃保留LEFT JOIN保留以 NULL 填充保留保留RIGHT JOIN以 NULL 填充保留保留保留FULL JOIN以 NULL 填充保留以 NULL 填充保留保留1.2 为什么 ON 和 WHERE 容易让人懵理解了 JOIN 类型接下来得说说困惑的根源在哪。我个人觉得有三点第一INNER JOIN 场景下ON 和 WHERE 的结果几乎完全等价。比如FROM A INNER JOIN B ON A.id B.aid AND B.status 1和FROM A INNER JOIN B ON A.id B.aid WHERE B.status 1跑出来的结果集是一样的。很多教程和课程都只讲了这一种场景学习者背下了两者都能用却不知道这只是 INNER JOIN 下的特例。第二定义没说透。教材会告诉你ON 是连接条件WHERE 是过滤条件但没说清楚这两个条件分别在 SQL 执行的哪个环节起作用。一旦换成 LEFT JOIN连接条件和过滤条件的差异就被放大了没有执行顺序的概念自然只能靠死记硬背。第三日常业务里 LEFT JOIN 太常用了。订单、用户、商品、支付流水稍微复杂点的业务表都要靠 JOIN 串起来。只要有一次把过滤条件放错位置数据就悄悄变少而且这种错误往往不是报错只是结果不对排查起来非常费劲。拿一个生活化的类比来说JOIN 的过程像相亲配对ON 是介绍人手里的配对规则——什么条件的人才拉在一起相见WHERE 则是见面之后的最终筛选——不符合要求的一律pass。INNER JOIN 相当于只把配对成功且通过筛选的人留在屋里LEFT JOIN 则是左表的人一个都不能走配对成功的右表嘉宾可以留下没配对成功的右表位置空着。这时如果你在 WHERE 里多加一条右表嘉宾必须带礼物那左表那些空着位置的人就因为右表字段是 NULL 被清走了屋里瞬间少了一大批人。2. ON 与 WHERE 的本质差异执行顺序决定结果2.1 SQL 的逻辑执行顺序要真正理解 ON 和 WHERE绕不开 SQL 的执行顺序。虽然数据库优化器会根据自己的规则调整实际执行计划但最终返回的结果必须和下面这个逻辑顺序等价FROM → JOIN / ON → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT这个顺序里最关键的是前两步。FROM 负责确定数据源JOIN 阶段根据 ON 条件把表拼接起来此时 LEFT JOIN 之类的保留语义就会生效。JOIN 完成之后才轮到 WHERE 对拼接完成后的结果集做整体过滤。再往后 GROUP BY 分组、HAVING 过滤分组、SELECT 投影、ORDER BY 排序、LIMIT 截断。很多人把 WHERE 当成JOIN 之前就过滤的步骤这是错的。对于 INNER JOIN优化器确实可能先做过滤再连接因为结果等价但对于 LEFT JOIN优化器不能随意调整这个顺序否则会破坏左表全保留的语义。所以你在逻辑上必须理解WHERE 作用于 JOIN 之后的中间结果。2.2 为什么这个顺序对 LEFT JOIN 至关重要LEFT JOIN 的保留左表全部行这个承诺是在 JOIN 阶段兑现的。一旦过了 JOIN 阶段进入 WHERE这个承诺就失效了。WHERE 会对 JOIN 之后的整个结果集做过滤包括左表的行。这里还牵扯到 SQL 的三值逻辑。SQL 里的比较运算结果不只是 TRUE 和 FALSE还有第三个状态——UNKNOWN。任何和 NULL 做比较的结果都是 UNKNOWN。WHERE c.status unused遇到某个客户压根没有优惠券、JOIN 之后 c.status 是 NULL 时这个比较结果是 UNKNOWN不会被保留。WHERE 子句只保留结果为 TRUE 的行UNKNOWN 和 FALSE 一样被丢弃。这就是LEFT JOIN 结果行数比左表少的最常见原因你把右表的过滤条件写进了 WHERE那些右表匹配不上、字段为 NULL 的行全被 WHERE 给过滤掉了。看起来你写的是 LEFT JOIN实际跑出来的效果却接近 INNER JOIN左表数据无声无息地减少。提示LEFT JOIN 的保留左表全部行承诺只在 JOIN 阶段有效。一旦进入 WHERE 阶段这个承诺就失效了。2.3 两者本质分工的一句话总结可以把 ON 和 WHERE 的分工压缩成一句口诀ON 决定怎么连WHERE 决定连完之后留下谁。更准确地说ON 在 JOIN 阶段控制左右表如何匹配、哪些右表行有资格参与连接WHERE 在 JOIN 完成后对包括左表和右表在内的整个结果集做最终过滤。搞懂了这一点很多诡异的数据结果就有了合理的解释。接下来我会用一个完整的业务案例把两种写法的差异直接放在桌面上对比。3. 实战拆解LEFT JOIN 中 ON 与 WHERE 的结果差异3.1 案例用户与优惠券理论讲得再多不如上一套可以直接跑的数据。我自己带新人时最常用用户-优惠券这个场景简单又贴近业务。先建两张表一张用户表users一张优惠券表coupons。用户表里有 3 个用户优惠券表里每个用户有几张状态不同的券。CREATE TABLE users ( user_id INT PRIMARY KEY, user_name VARCHAR(50), register_date DATE ); CREATE TABLE coupons ( coupon_id INT PRIMARY KEY, user_id INT, status VARCHAR(20), -- unused / used / expired amount DECIMAL(10,2) ); INSERT INTO users VALUES (1, 张三, 2024-01-15), (2, 李四, 2024-02-10), (3, 王五, 2024-03-05); INSERT INTO coupons VALUES (101, 1, unused, 50.00), (102, 1, used, 30.00), (103, 2, unused, 20.00), (104, 3, expired, 100.00);现在业务需求来了要统计每个用户手里未使用优惠券的情况并且所有用户都要显示出来哪怕他一张未使用券都没有。3.2 条件放 ON保留所有用户按需求未使用优惠券这个过滤条件应该放在 ON 里SQL 这样写SELECT u.user_id, u.user_name, c.coupon_id, c.status, c.amount FROM users u LEFT JOIN coupons c ON u.user_id c.user_id AND c.status unused;执行结果长这样user_iduser_namecoupon_idstatusamount1张三101unused50.002李四103unused20.003王五NULLNULLNULL所有用户都保留了。张三和李四各自匹配到了未使用券王五只有一张已过期的券因为 ON 条件里加了c.status unused所以这张过期券没有资格参与连接右边补了 NULL。这正好满足所有用户都要显示出来的需求。这里的关键是ON 里的条件是 JOIN 匹配规则的一部分它决定了右表哪些行有资格和左表匹配。不满足条件的右表行直接不参与连接但左表的行不会因此消失。3.3 条件放 WHERE用户被悄悄过滤接下来看反面写法。如果把同样的过滤条件放到 WHERE 里SELECT u.user_id, u.user_name, c.coupon_id, c.status, c.amount FROM users u LEFT JOIN coupons c ON u.user_id c.user_id WHERE c.status unused;执行结果user_iduser_namecoupon_idstatusamount1张三101unused50.002李四103unused20.00王五消失了。原因是整个查询分两步走第一步 LEFT JOIN 按u.user_id c.user_id连接王五匹配到了 coupon_id104 那条过期券右侧字段有值第二步 WHERE 判断c.status unused王五的 status 是expired比较结果是 FALSE被过滤掉了。你可能会说那如果王五一张券都没有呢那情况更微妙——LEFT JOIN 之后王五的 c.status 是 NULLc.status unused这个比较的结果是 UNKNOWN同样不会被保留。所以无论右表是有记录但不满足条件还是没记录WHERE 写法都会把左表行过滤掉。3.4 结果对比与到底该怎么选把两种写法放在一起对比差异一目了然业务需求推荐写法原因左表所有行都要保留右表只取满足条件的记录过滤条件放 ON不破坏 LEFT JOIN 保留语义只保留 JOIN 后满足整体条件的行过滤条件放 WHERE相当于对结果集做全局筛选不关心左表是否全保留只要匹配数据INNER JOIN WHERE语义清晰可读性更好实际工作中我的选择标准非常朴素先问自己这个查询要保留哪张表的全部行。如果要保留左表全部行凡是针对右表的过滤条件全部进 ON如果不需要保留全部行干脆用 INNER JOIN把连接条件放 ON、过滤条件放 WHERE层次分明。很多人在 LEFT JOIN 里把过滤条件写进 WHERE本质是没用 INNER JOIN 的写法硬套 LEFT JOIN结果把自己绕进去了。4. 多表关联ON 与 WHERE 的组合实战4.1 三表 JOIN 的 ON 条件写法业务里很少只有两张表。订单、订单明细、商品、用户、支付信息多表 JOIN 是家常便饭。这时候 ON 和 WHERE 的配合更要谨慎。假设有三张表订单表orders、订单明细表order_items、商品表products。要查所有订单、订单里的商品明细和商品名称SELECT o.order_id, o.user_id, oi.quantity, p.product_name FROM orders o LEFT JOIN order_items oi ON o.order_id oi.order_id LEFT JOIN products p ON oi.product_id p.product_id;这段 SQL 的 JOIN 是一个接一个进行的orders先和order_items连接生成一个中间结果这个中间结果再作为左表继续和products连接。每一步都是独立的 JOINON 条件只针对当前这一步。多表 JOIN 时一个常见需求是订单总有三种状态pending、paid、cancelled我只想保留 paid 的单子但订单表所有行都要这听起来矛盾其实取决于视角。如果我想统计每个用户的已支付订单但用户全保留那订单表是右表过滤条件应该放 ON。如果把需求反过来——每个已支付订单的详情订单表之外的其他表能带就带那 ORDER 就是主表o.status paid应该放 WHERE。同一张表的同一个条件放 ON 还是 WHERE完全取决于主表是谁。这就是先确定主表的重要性。4.2 条件聚合场景COUNT 里的坑多表 JOIN 经常配合聚合函数做统计这里的 ON 和 WHERE 位置错误后果不只是行数变少而是统计口径彻底变化。举个最常见的需求统计每个用户已使用的优惠券数量所有用户都要出现在结果里没用过券的用户显示 0。正确写法是用 ON 过滤SELECT u.user_id, u.user_name, COUNT(c.coupon_id) AS used_coupon_cnt FROM users u LEFT JOIN coupons c ON u.user_id c.user_id AND c.status used GROUP BY u.user_id, u.user_name;结果里王五会出现used_coupon_cnt为 0。为什么能正确统计因为 LEFT JOIN 时王五没有匹配到已使用券c.coupon_id 是 NULLCOUNT 不会统计 NULL所以结果是 0而不是把他过滤掉。如果条件写进 WHERESELECT u.user_id, u.user_name, COUNT(c.coupon_id) AS used_coupon_cnt FROM users u LEFT JOIN coupons c ON u.user_id c.user_id WHERE c.status used GROUP BY u.user_id, u.user_name;结果里王五直接消失整个统计变成了只有用过券的用户才出现在报表里口径彻底变了。如果这是给老板看的经营分析表这个错误会导致用户基数偏差。还有一种更骚的写法一次性统计好几列。用条件计数代替多个子查询性能更好SELECT u.user_id, COUNT(o.order_id) AS total_orders, COUNT(CASE WHEN o.status paid THEN 1 END) AS paid_orders, COUNT(CASE WHEN o.status cancelled THEN 1 END) AS cancelled_orders FROM users u LEFT JOIN orders o ON u.user_id o.user_id GROUP BY u.user_id;这里的 CASE WHEN 只做条件计数COUNT 不会因此过滤掉用户行未匹配订单的用户这些计数都是 0。这种写法很适合做宽表汇总比绕好几层临时表清晰得多。4.3 什么时候放 WHERE 更合理说完 ON 的优势也得承认 WHERE 在有些场景下更合理不能走到另一个极端。第一种INNER JOIN 时。连接条件和过滤条件分开写代码可读性最好。FROM a INNER JOIN b ON a.id b.aid WHERE b.status 1比把 status 塞进 ON 里更容易让同事看懂。第二种过滤条件针对主表本身。无论放哪LEFT JOIN 都会因为主表行被过滤而导致行数变少这时更符合业务预期的是明确用 WHERE 表达我就是要筛选主表。比如FROM users u LEFT JOIN orders o ON ... WHERE u.register_date 2024-01-01用户表主表被过滤是合理的。第三种数据集很小、性能没有压力。在这个前提下ON 和 WHERE 的细微语义差异可以被忽略优先保证 SQL 直观好懂。4.4 ON 条件的另一种用法限定关联范围多表 JOIN 时 ON 还可以做一件很多新手不知道的事通过 AND 附带条件缩小右表参与连接的范围而不影响主表。比如查所有用户及其已支付的订单可以写SELECT u.user_id, o.order_id, o.amount FROM users u LEFT JOIN orders o ON o.user_id u.user_id AND o.status paid;这就是前面核心观点的延伸ON 的 AND 子句相当于右表筛选器只让满足条件的右表行参与匹配。在复杂报表里这种写法的价值非常大。比如统计每个客户最近一笔已支付订单的时间就可以在这种 ON 限定的 JOIN 基础上配合 MAX 聚合。5. 常见问题速查与排查技巧5.1 症状LEFT JOIN 结果行数比左表少这是我被问到最多的一个问题。损害已经造成关键是快速定位。我自己的排查套路是先把 WHERE 子句里涉及右表的条件全部注释掉重新跑一遍看行数是否恢复到左表的行数。如果恢复了那基本可以断定是 WHERE 条件把右表未匹配的 NULL 行过滤了。把对应条件从 WHERE 挪到 ON 里再次对比行数。如果行数有差异用EXPLAIN看执行计划确认 JOIN 类型和过滤下推的实际情况。这个套路帮我排查过不下二十次同类问题基本 90% 的情况三步以内就定位了。5.2 NULL 三值逻辑的坑SQL 里的 NULL 是个易错点处理 JOIN 后的过滤尤其容易踩坑。c.status ! unused并不是除了 unused 之外都算因为 NULL 不等于 unused 的结果是 UNKNOWN而 WHERE 只保留 TRUE。比如业务上想查有未使用券之外的其他券或者压根没券的用户如果写成WHERE c.status ! unused那么没券的用户c.status 为 NULL会被丢掉因为 NULL ! unused 是 UNKNOWN不是 TRUE。同时有已使用券的用户会被保留。如果要把没券的情况也保留必须显式加 IS NULLWHERE c.coupon_id IS NULL OR c.status ! unused大多数开发者在写!、、时都没有意识到 NULL 会导致行数丢失。我的建议是凡是涉及右表字段的比较条件先想想这个字段会不会是 NULL。如果可能为 NULL要么用 IS NULL 语义要么把条件放到 ON 里让那些行继续保持 NULL。5.3 性能与索引的实操建议除了语义正确性能也是 JOIN 绕不开的话题。分享几个实操层面的经验ON 条件字段必须有索引。JOIN 的关联字段没有索引大表关联时会全表扫描查询能慢到你怀疑人生。user_id这种高频关联字段一般建议建普通索引。避免在 ON 条件上做函数运算。比如ON DATE(o.create_time) DATE(u.register_date)这种写法会导致索引失效。可以提前把日期截断成字段或者用范围条件o.create_time 2024-01-01 00:00:00这类写法。小表驱动大表。优化器通常会自己选择合适的连接顺序但如果你用STRAIGHT_JOIN或控制 FROM 顺序一般原则是让结果集小的表先参与连接缩小中间结果集。WHERE 过滤右表大量数据时直接考虑 INNER JOIN。如果业务上确实不需要保留主表 NULL 行写 INNER JOIN 让优化器有更多调优空间通常查询计划也更好。用 EXPLAIN 验证。类似 Oracle 的EXPLAIN PLANMySQL 里EXPLAIN SELECT ...可以看访问类型、索引使用情况。type 列出现ALL就要警惕全表扫描。5.4 一个小技巧先想清楚再动笔最后分享一个我个人的工作习惯写 JOIN 之前永远先回答三个问题——主表是哪张、主表要保留哪些行、右表的哪些条件只是限定关联数据的。这三个问题想清楚再动笔基本不会写错。我也会在写完 SQL 之后习惯性估算一下结果行数。比如主表有 10000 行LEFT JOIN 之后如果行数超过 10000那多半是有重复关联如果少于 10000那说明某个条件偷偷过滤了主表行。这个简单的行数核对比我对着字段一个个检查效率高得多。最后再啰嗦两句写 SQL 这些年我越来越觉得一个知识点的掌握程度不在于能不能说出定义而在于遇到异常结果时能不能快速定位。ON 和 WHERE 的区别说穿了就是JOIN 阶段控制匹配规则和JOIN 完成后的整体过滤的区别。INNER JOIN 里它们结果相同掩盖了差异LEFT JOIN 里差异立刻显现。下次再写 JOIN先在心里过一遍执行顺序再决定条件放哪这个习惯能帮你避开数据报表里最大的隐形坑。