原文跳转到原文
两张表:
班级表(classes):
ID | 班级 |
1 | 一班 |
2 | 二班 |
3 | 三班 |
4 | 四班 |
学生表(students):
ID | class_id | name | sex |
1 | 1 | 小A | 1 |
2 | 1 | 小B | 0 |
3 | 1 | 小C | 1 |
4 | 1 | 小D | 1 |
5 | 2 | 小E | 1 |
6 | 2 | 小F | 0 |
7 | 2 | 小G | 1 |
8 | 3 | 小H | 0 |
9 | 3 | 小I | 0 |
10 | 3 | 小J | 0 |
查询个班级的sex=1的人数:
方式1:
SELECT c.name, count(s.name) as num FROM classes c left join students s on s.class_id = c.id where s.sex= 1 group by c.name
查询结果
name | num |
一班 | 3 |
二班 | 2 |
方式二:
SELECT c.name, count(s.name) as num FROM classes c left join students s on s.class_id = c.id and s.sex = 1 group by c.name
查询结果
name | num |
一班 | 3 |
二班 | 2 |
三班 | 0 |
四班 | 0 |
很明显方式二才是我们想要的结果
附上原作者分析:
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。
以上分析我们可以得出出错原因: 由于在where条件中对右表限制,导致数据缺失(三班 四班应该有个为0的结果)
表的关联查询返回的是表的合并行,之前一直认为左关联是以左表为基表,返回所有左表所有数据,右表自动补全,理解过于片面了;
在left join语句中,左表过滤必须放where条件中,右表过滤必须放on条件中,这样结果才能不多不少,刚刚好。