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里都需要写条件。