MySQL：leftjoin避坑指南

点击上方“方志朋”&＃xff0c;选择“设为星标”

回复”666“获取新整理的面试资料

作者&＃xff1a;MageekChiu

segmentfault.com/a/1190000020458807

现象

left join在我们使用mysql查询的过程中可谓非常常见&＃xff0c;比如博客里一篇文章有多少条评论、商城里一个货物有多少评论、一条评论有多少个赞等等。但是由于对join、on、where等关键字的不熟悉&＃xff0c;有时候会导致查询结果与预期不符&＃xff0c;所以今天我就来总结一下&＃xff0c;一起避坑。

这里我先给出一个场景&＃xff0c;并抛出两个问题&＃xff0c;如果你都能答对那这篇文章就不用看了。

假设有一个班级管理应用&＃xff0c;有一个表classes&＃xff0c;存了所有的班级&＃xff1b;有一个表students&＃xff0c;存了所有的学生&＃xff0c;具体数据如下&＃xff08;感谢廖雪峰的在线SQL&＃xff09;&＃xff1a;

SELECT * FROM classes;

SELECT * FROM students;

那么现在有两个需求&＃xff1a;

• 找出每个班级的名称及其对应的女同学数量

• 找出一班的同学总数

对于需求1&＃xff0c;大多数人不假思索就能想出如下两种sql写法&＃xff0c;请问哪种是对的&＃xff1f;

SELECT c.name, count(s.name) as num FROM classes c left join students s on s.class_id &＃61; c.id and s.gender &＃61; &＃39;F&＃39;group by c.name


或者

SELECT c.name, count(s.name) as num FROM classes c left join students s on s.class_id &＃61; c.id where s.gender &＃61; &＃39;F&＃39;group by c.name


对于需求2&＃xff0c;大多数人也可以不假思索的想出如下两种sql写法&＃xff0c;请问哪种是对的&＃xff1f;

SELECT c.name, count(s.name) as num FROM classes c left join students s on s.class_id &＃61; c.id where c.name &＃61; &＃39;一班&＃39; group by c.name


或者

SELECT c.name, count(s.name) as num FROM classes c left join students s on s.class_id &＃61; c.id and c.name &＃61; &＃39;一班&＃39; group by c.name


请不要继续往下翻 &＃xff01;&＃xff01;先给出你自己的答案&＃xff0c;正确答案就在下面。
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~

答案是两个需求都是第一条语句是正确的&＃xff0c;要搞清楚这个问题&＃xff0c;就得明白mysql对于left join的执行原理&＃xff0c;下节进行展开。

根源

mysql 对于left join的采用类似嵌套循环的方式来进行从处理&＃xff0c;以下面的语句为例&＃xff1a;

SELECT * FROM LT LEFT JOIN RT ON P1(LT,RT)) WHERE P2(LT,RT)


其中P1是on过滤条件&＃xff0c;缺失则认为是TRUE&＃xff0c;P2是where过滤条件&＃xff0c;缺失也认为是TRUE&＃xff0c;该语句的执行逻辑可以描述为&＃xff1a;

FOR each row lt in LT {// 遍历左表的每一行BOOL b &＃61; FALSE;FOR each row rt in RT such that P1(lt, rt) {// 遍历右表每一行&＃xff0c;找到满足join条件的行IF P2(lt, rt) {//满足 where 过滤条件t:&＃61;lt||rt;//合并行&＃xff0c;输出该行}b&＃61;TRUE;// lt在RT中有对应的行}IF (!b) { // 遍历完RT&＃xff0c;发现lt在RT中没有有对应的行&＃xff0c;则尝试用null补一行IF P2(lt,NULL) {// 补上null后满足 where 过滤条件t:&＃61;lt||NULL; // 输出lt和null补上的行}         }
}


当然&＃xff0c;实际情况中MySQL会使用buffer的方式进行优化&＃xff0c;减少行比较次数&＃xff0c;不过这不影响关键的执行流程&＃xff0c;不在本文讨论范围之内。

从这个伪代码中&＃xff0c;我们可以看出两点&＃xff1a;

• 如果想对右表进行限制&＃xff0c;则一定要在on条件中进行&＃xff0c;若在where中进行则可能导致数据缺失&＃xff0c;导致左表在右表中无匹配行的行在最终结果中不出现&＃xff0c;违背了我们对left join的理解。因为对左表无右表匹配行的行而言&＃xff0c;遍历右表后b&＃61;FALSE,所以会尝试用NULL补齐右表&＃xff0c;但是此时我们的P2对右表行进行了限制&＃xff0c;NULL若不满足P2(NULL一般都不会满足限制条件&＃xff0c;除非IS NULL这种)&＃xff0c;则不会加入最终的结果中&＃xff0c;导致结果缺失。

• 如果没有where条件&＃xff0c;无论on条件对左表进行怎样的限制&＃xff0c;左表的每一行都至少会有一行的合成结果&＃xff0c;对左表行而言&＃xff0c;若右表若没有对应的行&＃xff0c;则右表遍历结束后b&＃61;FALSE&＃xff0c;会用一行NULL来生成数据&＃xff0c;而这个数据是多余的。所以对左表进行过滤必须用where。

下面展开两个需求的错误语句的执行结果和错误原因&＃xff1a;

需求1

需求2

需求1由于在where条件中对右表限制&＃xff0c;导致数据缺失&＃xff08;四班应该有个为0的结果&＃xff09;

需求2由于在on条件中对左表限制&＃xff0c;导致数据多余&＃xff08;其他班的结果也出来了&＃xff0c;还是错的&＃xff09;

总结

通过上面的问题现象和分析&＃xff0c;可以得出了结论&＃xff1a;在left join语句中&＃xff0c;左表过滤必须放where条件中&＃xff0c;右表过滤必须放on条件中&＃xff0c;这样结果才能不多不少&＃xff0c;刚刚好。

SQL 看似简单&＃xff0c;其实也有很多细节原理在里面&＃xff0c;一个小小的混淆就会造成结果与预期不符&＃xff0c;所以平时要注意这些细节原理&＃xff0c;避免关键时候出错。

热门内容&＃xff1a;   

• 支付宝的架构到底有多牛逼&＃xff01;还没看完我就跪了&＃xff01;
  

• 面经&＃xff1a;为了拿到字节跳动offer&＃xff0c;鬼知道我经历了啥...
  

• Spring Boot &＃43; MyBatis &＃43; Druid &＃43; PageHelper 实现多数据源并分页
  

• 是时候扔掉Postman了&＃xff0c;又一个被低估的IDEA插件出来了...
  

• Spring集成任务调度功能
  

最近面试BAT&＃xff0c;整理一份面试资料《Java面试BAT通关手册》&＃xff0c;覆盖了Java核心技术、JVM、Java并发、SSM、微服务、数据库、数据结构等等。

获取方式&＃xff1a;点“在看”&＃xff0c;关注公众号并回复 666 领取&＃xff0c;更多内容陆续奉上。

明天见(&＃xff61;&＃xff65;ω&＃xff65;&＃xff61;)&＃xff89;♡