MySQL:Left Join 避坑指南
程序员知识码头
共 2495字,需浏览 5分钟
· 2019-12-11
链接 | segmentfault.com/a/1190000020458807
现象
left join在我们使用mysql查询的过程中可谓非常常见,比如博客里一篇文章有多少条评论、商城里一个货物有多少评论、一条评论有多少个赞等等。但是由于对join、on、where等关键字的不熟悉,有时候会导致查询结果与预期不符,所以今天我就来总结一下,一起避坑。
这里我先给出一个场景,并抛出两个问题,如果你都能答对那这篇文章就不用看了。
假设有一个班级管理应用,有一个表classes,存了所有的班级;有一个表students,存了所有的学生,具体数据如下(感谢廖雪峰的在线SQL):
SELECT * FROM classes;
SELECT * FROM students;
那么现在有两个需求:
对于需求1,大多数人不假思索就能想出如下两种sql写法,请问哪种是对的?
- 找出每个班级的名称及其对应的女同学数量
- 找出一班的同学总数
SELECT c.name, count(s.name) as num
FROM classes c left join students s
on s.class_id = c.id
and s.gender = 'F'
group by c.name
或者SELECT c.name, count(s.name) as num
FROM classes c left join students s
on s.class_id = c.id
where s.gender = 'F'
group by c.name
对于需求2,大多数人也可以不假思索的想出如下两种sql写法,请问哪种是对的?SELECT c.name, count(s.name) as num
FROM classes c left join students s
on s.class_id = c.id
where c.name = '一班'
group by c.name
或者SELECT c.name, count(s.name) as num
FROM classes c left join students s
on s.class_id = c.id
and c.name = '一班'
group by c.name
请不要继续往下翻 !!先给出你自己的答案,正确答案就在下面。答案是两个需求都是第一条语句是正确的,要搞清楚这个问题,就得明白mysql对于left join的执行原理,下节进行展开。
根源
mysql 对于left join的采用类似嵌套循环的方式来进行从处理,以下面的语句为例:SELECT * FROM LT LEFT JOIN RT ON P1(LT,RT)) WHERE P2(LT,RT)
其中P1是on过滤条件,缺失则认为是TRUE,P2是where过滤条件,缺失也认为是TRUE,该语句的执行逻辑可以描述为:FOR each row lt in LT {// 遍历左表的每一行
BOOL b = FALSE;
FOR each row rt in RT such that P1(lt, rt) {// 遍历右表每一行,找到满足join条件的行
IF P2(lt, rt) {//满足 where 过滤条件
t:=lt||rt;//合并行,输出该行
}
b=TRUE;// lt在RT中有对应的行
}
IF (!b) { // 遍历完RT,发现lt在RT中没有有对应的行,则尝试用null补一行
IF P2(lt,NULL) {// 补上null后满足 where 过滤条件
t:=lt||NULL; // 输出lt和null补上的行
}
}
}
当然,实际情况中MySQL会使用buffer的方式进行优化,减少行比较次数,不过这不影响关键的执行流程,不在本文讨论范围之内。从这个伪代码中,我们可以看出两点:如果想对右表进行限制,则一定要在on条件中进行,若在where中进行则可能导致数据缺失,导致左表在右表中无匹配行的行在最终结果中不出现,违背了我们对left join的理解。因为对左表无右表匹配行的行而言,遍历右表后b=FALSE,所以会尝试用NULL补齐右表,但是此时我们的P2对右表行进行了限制,NULL若不满足P2(NULL一般都不会满足限制条件,除非IS NULL这种),则不会加入最终的结果中,导致结果缺失。下面展开两个需求的错误语句的执行结果和错误原因:需求1需求2需求1由于在where条件中对右表限制,导致数据缺失(四班应该有个为0的结果)需求2由于在on条件中对左表限制,导致数据多余(其他班的结果也出来了,还是错的)
如果没有where条件,无论on条件对左表进行怎样的限制,左表的每一行都至少会有一行的合成结果,对左表行而言,若右表若没有对应的行,则右表遍历结束后b=FALSE,会用一行NULL来生成数据,而这个数据是多余的。所以对左表进行过滤必须用where。
总结
通过上面的问题现象和分析,可以得出了结论:在left join语句中,左表过滤必须放where条件中,右表过滤必须放on条件中,这样结果才能不多不少,刚刚好。SQL 看似简单,其实也有很多细节原理在里面,一个小小的混淆就会造成结果与预期不符,所以平时要注意这些细节原理,避免关键时候出错。- End -
技术连载目录(可点击跳转即可阅读):
面试题系列教程 点击--> 面试题技术干货连载目录 跳转
Maven系列教程 点击--> Maven技术干货连载目录 跳转
MyBatis系列教程 点击--> MyBatis技术干货连载目录 跳转
JVM调优总结系列教程 点击--> JVM调优技术干货连载目录 跳转
点击在看,愿你我不再陌生
评论
展讯平台手机重启问题分析指南
和你一起终身学习,这里是程序员Android经典好文推荐,通过阅读本文,您将收获以下知识点:一、 User 版本 默认开启 sysdump 方法二、插入SD卡 抓取Sysdump log三、 sysdump log 分析四、展讯平台抓取重启 串口log的方案五、展讯平台判断重启类型六、展讯平台关闭
程序员Android
0
2024跨屏营销指南
下载报告去公众号:硬核刘大 后台回复“ 跨屏营销”,即可下载完整PDF文件。更多报告内容,可加微信:chanpin628 领取。(ps:加过微信:yw5201a1 的不要再加,分享的内容一样,有一个号就行。)申明:报告版权 勾正科技&MMA
产品刘
0
面试官:MySQL 上亿大表,如何深度优化?
来源:cnblogs.com/YangJiaXin/p/10828244.html背景分析测试实施索引优化后delete大表优化为小批量删除总结前段时间刚入职一家公司,就遇上这事!背景XX实例(一主一从)xxx告警中每天凌晨在报SLA报警,该报警的意思是存在一定的主从延迟(若在此时发生主从切换,需要
好好学java
0
i/o timeout , 希望你不要踩到这个net/http包的坑
文章持续更新,可以微信搜一搜「golang小白成长记」第一时间阅读,回复【教程】获golang免费视频教程。本文已经收录在GitHub https://github.com/xiaobaiTech/golangFamily , 有大厂面试完整考点和成长路线,欢迎Star。问题我们来看一段日常代码。
Go语言进阶学习
0
MySQL 巨坑:永远不要在 MySQL 中使用 UTF-8!!
来源:网络👉 欢迎加入小哈的星球 ,你将获得: 专属的项目实战 / Java 学习路线 / 一对一提问 / 学习打卡 / 赠书福利全栈前后端分离博客项目 2.0 版本完结啦, 演示链接:http://116.62.199.48/ ,新项目
小哈学Java
10
8种专坑运维的 SQL 写法,性能降低100倍,您不来看看?
来源:机器之心1、LIMIT 语句分页查询是最常用的场景之一,但也通常也是最容易出问题的地方。比如对于下面简单的语句,一般 DBA 想到的办法是在 type,name, create_time 字段上加组合索引。这样条件排序都能有效的利用到索引,性能迅速提升。SELECT *FROM opera
良许Linux
0
50条MySQL常用脚本
关注公众号,SQL干货及时获取后台回复:1,进SQL技术交流群SQL面试专栏《SQL145题第2版》花时间整理了50条常用的MySQL脚本,查漏补缺,希望对大家有所帮助。-- 1、插入数据INSERT INTO example_table (name, age) VALUES&n
SQL数据库开发
0
大模型并行训练指南:通俗理解Megatron-DeepSpeed之模型并行与数据并行(下)
文末《大模型项目开发线上营》秒杀倒计时↓↓↓接前文:(上)篇>>>大模型并行训练指南:通俗理解Megatron-DeepSpeed之模型并行与数据并行(上)(中)篇>>>大模型并行训练指南:通俗理解Megatron-DeepSpeed之模型并行与数据并行(中)06
七月在线实验室
10