解决Mysql的left join无效及使用的注意事项说明

这篇文章主要介绍了解决Mysql的left join无效及使用的注意事项说明,具有很好的参考价值,希望对大家有所帮助。如有错误或未考虑完全的地方,望不吝赐教

Mysql的left join无效及使用

今天写sql发现使用left join 没有把左边表的数据全部查询出来,让我郁闷了一会,后来仔细研究了一会才知道自己犯了个常识性的错误(我是菜鸟)

这是原sql

解决Mysql的left join无效及使用的注意事项说明

这样的查询并不能将tb_line这张表的数据都查询出来,好尴尬…

后面我才知道原来当我们进行多表查询,在执行到where之前,会先形成一个临时表

而on就是临时表中的条件筛选,使用left join则不管条件是否为真,都会查询出左边表的数据,条件为假的,则显示为null

解决Mysql的left join无效及使用的注意事项说明

where则是在临时表生成之后的过滤条件

解决Mysql的left join无效及使用的注意事项说明

在第一张图中,我将tb_vehicle这张表的过滤条件放在where之中,那left join所产生条件为假的数据,则会在where 的 v.del_flag="0"中被过滤掉(因为条件为假的数据,del_flag都为空)

所以我看似使用了left join ,实际上这样写与使用inner join的结果是一样的

正确sql如下:

解决Mysql的left join无效及使用的注意事项说明

在临时表中就做好条件筛选,这样就能够得到左边表的数据

总结:

使用left join 并需要做条件查询的时候,需要仔细斟酌改条件筛选放在on后面还是where后面

Mysql left join 避坑指南

现象

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

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

假设有一个班级管理应用,有一个表classes,存了所有的班级;有一个表students,存了所有的学生,具体数据如下:

SELECT * FROM classes;

解决Mysql的left join无效及使用的注意事项说明

SELECT * FROM students;

解决Mysql的left join无效及使用的注意事项说明

那么现在有两个需求:

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

找出一班的同学总数

对于需求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这种),则不会加入最终的结果中,导致结果缺失。

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

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

需求1

解决Mysql的left join无效及使用的注意事项说明

需求2

解决Mysql的left join无效及使用的注意事项说明

需求1由于在where条件中对右表限制,导致数据缺失(四班应该有个为0的结果)

需求2由于在on条件中对左表限制,导致数据多余(其他班的结果也出来了,还是错的)

总结

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

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

以上为个人经验,希望能给大家一个参考,也希望大家多多支持钦钦技术栈。

原文链接:https://blog.csdn.net/weixin_42214548/article/details/80324111

版权声明:本文(即:原文链接:https://www.qin1qin.com/catagory/6676/)内容由互联网用户自发投稿贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 630367839@qq.com 举报,一经查实,本站将立刻删除。

(0)
上一篇 2022年 7月 29日 10:20:11
下一篇 2022年 7月 29日 10:20:24

软件定制开发公司

相关阅读

发表回复

登录后才能评论
通知:禁止投稿所有关于虚拟货币,币圈类相关文章,发现立即永久封锁账户ID!