LEFT JOIN 中 ON 与 WHERE 过滤的差异

在 MySQL 数据库开发中LEFT JOIN左外连接是一个最常被误用的语法。许多开发者往往习惯性地将所有过滤条件一股脑地往ON后面塞或者为了排版好看将条件全部扔到WREHRE里面。这种模糊的逻辑在普通内连接INNER JOIN中确实没有区别但在LEFT JOIN中多条件写在 ON 还是 WHERE会导致完全不同的执行结果与查询性能。1. 核心结论一句话总结写在ON里面代表连接条件。不论右表是否满足此条件左驱动表的数据绝对不会丢。若右表不满足右表字段直接补NULL。写在WHERE里面代表过滤条件。它是对关联后的整个结果集进行大筛查。一旦右表字段因不满足而被补了NULL它就会在WHERE过滤中被彻底抹去。2. 真实案例单步拆解为了还原现场我们准备两张最简单的基础表表 a商品表f1 (商品ID)name (商品名)1苹果2香蕉表 b价格表f1 (商品ID)f2 (促销价格)130250场景一多条件写在ON之中促销条件关联SELECT*FROMaLEFTJOINbON(a.f1b.f1ANDb.f230);MySQL 底层单步执行逻辑由于是LEFT JOIN表a被指定为驱动表。执行器会拿着表a的数据一行行去匹配表b匹配的硬性考核指标是a.f1 b.f1并且b.f2 30。**处理“苹果”行(1, 苹果)**拿着f11去表b找找到了满足b.f11的行紧接着评估第二个条件b.f230。此时3030匹配成功。本步结果→\rightarrow→(1, 苹果, 1, 30)。**处理“香蕉”行(2, 香蕉)**拿着f12去表b找找到了b.f12的行但它的b.f2是 50不满足b.f230的要求。关键机制因为是LEFT JOIN左表数据不能丢。既然表b没有行能同时满足这两个条件那就强行输出“香蕉”右表全部填NULL。本步结果→\rightarrow→(2, 香蕉, NULL, NULL)。最终场景一输出结果a.f1a.nameb.f1b.f21苹果1302香蕉NULLNULL语义总结“我只想和表 b 中价格是 30 的促销项连。如果它不是 30我就不跟它连但我自己表 a依然要保留右边展示为 NULL 即可。”场景二条件挪到WHERE之中连接后过滤SELECT*FROMaLEFTJOINbON(a.f1b.f1)WHEREb.f230;MySQL 底层单步执行逻辑第一阶段连接首先只看ON (a.f1 b.f1)此时“苹果”和“香蕉”都能正常连上表b。生成一个中间临时结果集记录一(1, 苹果, 1, 30)记录二(2, 香蕉, 2, 50)第二阶段大过滤连接完全结束后WHERE b.f2 30开始收网。执行器检查上面两条记录发现记录二的b.f2是 50不符合WHERE条件当场予以剔除消失。最终场景二输出结果a.f1a.nameb.f1b.f21苹果130⚠️ 惊人的幕后优化驱动表被调换了在场景二中既然WHERE条件强制限定了b.f2 30这意味着表b中所有不匹配或者补NULL的行统统都是无效的。MySQL 优化器敏锐地发现了这一点会在后台默默将这个LEFT JOIN直接改写为内连接INNER JOIN。改写后由于表b带有b.f2 30的强过滤条件数据集更小右表b反而会反客为主变成真正的驱动表。3. 避坑指南如何防范为了避免線上环境列表无故少数据或者多出了全是NULL的垃圾数据我们在写外连接时需要遵守以下原则明确逻辑目的如果你要找的是“不满足某些条件的残缺对照”例如查出所有没有参加 30 元促销的商品必须把条件写在ON里并配合右表主键IS NULL过滤。如果你仅仅是想对右表进行结果筛选请直接写JOIN不要写LEFT JOIN。利用EXPLAIN脑补流程如果你写了LEFT JOIN却在Extra字段里看到了Using where且第一行的表驱动表变成了右表说明你的WHERE条件已经打破了LEFT JOIN的语义被优化器降级改写了。在处理多表关联的业务如复杂报表、用户主页信息流时多花半分钟确认过滤条件在ON还是WHERE能为你省下大量的线上 Debug 时间。