MySQL- 技术专题 -LEFT JOIN 避坑指南
![MySQL-技术专题-LEFT JOIN避坑指南](https://static001.geekbang.org/infoq/43/438ce11b10f86533fab4da0e247177be.png)
简介
现象 `left join`在我们使用**mysql**查询的过程中可谓非常常见,比如博客里一篇文章有多少条评论、商城里一个货物有多少评论、一条评论有多少个赞等等。但是由于对`join`、`on`、`where`等关键字的不熟悉,有时候会导致查询结果与预期不符,所以今天我就来总结一下,一起避坑。 这里我先给出一个场景,并抛出两个问题,如果你都能答对那这篇文章就不用看了。
现象
这里我先给出一个场景,并抛出两个问题,如果你都能答对那这篇文章就不用看了。
假设有一个班级管理应用,有一个表classes,存了所有的班级;有一个表students,存了所有的学生,具体数据如下(感谢廖雪峰的在线SQL):
SELECT * FROM classes;
SELECT * FROM students;
那么现在有两个需求:
找出每个班级的名称及其对应的女同学数量
找出一班的同学总数
对于需求1,大多数人不假思索就能想出如下两种sql写法,请问哪种是对的?
或者
对于需求2,大多数人也可以不假思索的想出如下两种sql写法,请问哪种是对的?
或者
答案是两个需求都是第一条语句是正确的,要搞清楚这个问题,就得明白mysql对于left join
的执行原理,下节进行展开。
根源
mysql 对于left join
的采用类似嵌套循环的方式来进行从处理,以下面的语句为例:
其中P1
是on
过滤条件,缺失则认为是TRUE
,P2
是where
过滤条件,缺失也认为是TRUE
,该语句的执行逻辑可以描述为:
当然,实际情况中MySQL会使用buffer的方式进行优化,减少行比较次数,不过这不影响关键的执行流程,不在本文讨论范围之内。
从这个伪代码中,我们可以看出两点:
如果想对右表进行限制,则一定要在
on
条件中进行,若在where
中进行则可能导致数据缺失,导致左表在右表中无匹配行的行在最终结果中不出现,违背了我们对left join
的理解。
因为对左表无右表匹配行的行而言,遍历右表后b=FALSE
,所以会尝试用NULL
补齐右表,但是此时我们的P2
对右表行进行了限制,NULL若不满足P2
(NULL
一般都不会满足限制条件,除非IS NULL
这种),则不会加入最终的结果中,导致结果缺失。
如果没有where
条件,无论on
条件对左表进行怎样的限制,左表的每一行都至少会有一行的合成结果,对左表行而言,若右表若没有对应的行,则右表遍历结束后b=FALSE
,会用一行NULL
来生成数据,而这个数据是多余的。
所以对左表进行过滤必须用where。
下面展开两个需求的错误语句的执行结果和错误原因:
需求1
需求2
需求1由于在where条件中对右表限制,导致数据缺失(四班应该有个为
0
的结果)需求2由于在on条件中对左表限制,导致数据多余(其他班的结果也出来了,还是错的)
总结
通过上面的问题现象和分析,可以得出了结论:在left join
语句中,左表过滤必须放where条件中,右表过滤必须放on条件中,这样结果才能不多不少,刚刚好。
SQL 看似简单,其实也有很多细节原理在里面,一个小小的混淆就会造成结果与预期不符,所以平时要注意这些细节原理,避免关键时候出错。
评论