MySql中on与where的区别个人总结——分清楚条件应该写在哪里
一、前言
最近工作中,遇到了这样一个场景:
有2个数据库表,
其中一个是班级课程表class_course,记录了一个班级中有多少门课程,字段如下:
如图,a班有4门课,b班有2门课;
另一个是学生上课记录表student_course,记录了一个学生已经上了哪几门课,字段如下:
如图,zhangsan01上了a班的3门课,b班的1门课;
lisi上了b班的2门课;
问题:如何查询出,zhangsan01是否上完了a班所有的课程?
二、解决思路
首先,本人想到,可以根据course_id,用class_course表左联student_course,然后筛选出a班的、zhangsan01的记录;
其中如果有联查后student_course.course_id为null的,就说明zhangsan01没有上过这门课,就说明zhangsan01没有上完a班所有的课程;
如果不存在null,就说明zhangsan01上完了a班所有的课程。
如图:
只要能用sql查询出这样的List<String>
,然后java里循环下,发现有为null的,说明zhangsan01确实没上a班的a4课,也就能得出zhangsan01没有上完a班的所有课程的结论。
这样就可以实现需求了,下一个问题是sql怎么写了。
三、踩坑流程——错误sql
1.条件全写在on里面
select b.course_id,a.* from class_course a left join student_course b
on a.course_id=b.course_id and a.class_id='a' and b.student_id='zhangsan01';
本人最开始想到的sql就是这样,但是查询到的结果不符合需求:
虽然on里限定了条件a.class_id=‘a’,但是不知道为什么结果还是有b班的数据,这样查询的还是不对。
2.主要条件写where里面
select b.course_id,a.* from class_course a left join student_course b
on a.course_id=b.course_id
where a.class_id='a' and b.student_id='zhangsan01';
首先左联时要on a.course_id=b.course_id ,这个是毫无疑问的;
然后由于条件写on里不对,因此本人尝试写在where里,但是结果还是不对:
因为样例数据中,zhangsan01没有上a4课,所以应该有一行null的;
但是这样并没有查到null,也不对。
四、正确sql
一部分条件在on里,一部分条件在where里:
select b.course_id,a.* from class_course a left join student_course b
on a.course_id=b.course_id and a.class_id='a' and b.student_id='zhangsan01'
where a.class_id='a';
结果如图:
sql中,on里限制了只用a班的、zhangsan01的数据进行联查;
不过如果只在on里限制的话,由于是左联,因此class_course表里的其它数据也会显示出来,只不过连接的student_course表里的数据为空而已,不符合要求;
所以要在where中限制,只展示a班的,这样得到的结果就是想要的了;
需要注意,where中不能再限制只展示zhangsan01的了,如果加上后,最后一行null就展示不出来了。
上面的sql中,增加a.*是为了容易理解些,实际上最终要的sql是:
select b.course_id from class_course a left join student_course b
on a.course_id=b.course_id and a.class_id='a' and b.student_id='zhangsan01'
where a.class_id='a';
执行这句sql后,java中可以得到长度为4的List<String>
,遍历后,发现有为null的,就能得出zhangsan01没有上完a班的所有的课程的结论,就实现了需求。
五、总结
1.on中的条件,是决定根据什么进行联查(on a.course_id=b.course_id
) 、用哪些数据进行联查( and a.class_id='a' and b.student_id='zhangsan01'
)用的。
2.where中的条件,是决定最后数据怎么过滤用的(where a.class_id='a'
)。
3.联查时需要注意条件写在on里还是where里;如本文中的左联查询,只写在on里不能满足需求,左表中其它的数据也会连接空数据显示出来;所以on里与where里都需要写条件。
yangymy: 谢谢你解决了我的问题,我是麒麟系统遇到这个问题,找了很久终于解决了。
weixin_39183449: 传入中文好像有问题
虾仁猪芯: 感谢大佬,搞了半天还以为是版本或者镜像问题
小王毕业啦: 博主的文章真是让人眼前一亮,对于如何利用CSS实现图片渐变切换效果这一主题,博主的详细解说让我对整个过程有了更深入的理解。文章中的细节描写非常到位,让我不仅学到了知识,也感受到了博主的专业功底。期待博主未来能够继续分享更多有价值的内容,也希望能够得到博主的指导,一起进步。感谢博主的付出和支持!
微微一头猪: 你这8080进入nginx的server了,后就是路径匹配,完事了你这后面这个路径开头时/a他要是给你匹配到了/b开头的路径,这不合理啊。