left join on mysql_mysql的left join_MySQL：left join 避坑指南

现象

Left join 在我们使用 MySQL 查询中很常见，比如一篇博客文章有多少条评论，商城中某款商品有多少条评论，某条评论有多少个赞等等。但由于对 join、on、where 等关键字不熟悉，有时候查询结果可能与预期不一致，所以今天就来总结一下，一起避开陷阱。

这里我给出一个场景，问两个问题，如果你能正确回答，就不需要看这篇文章了。

假设有一个班级管理应用，有一张表存储所有班级；有一张表存储所有学生。具体数据如下（感谢廖雪峰的在线SQL）：

* 从 ;

* 从 ;

所以现在有两个需求：找出每个班级的名字以及对应的女生人数

查找班级的学生总数

对于需求1，大多数人可以不假思索地想到以下两种编写SQL的方式。哪一个是正确的？

c.name, count(s.name) 作为 num

FROM c 左连接 s

在 s. = c.id

且 s. = 'F'

按 c.name 分组

或者

c.name, count(s.name) 作为 num

FROM c 左连接 s

在 s. = c.id

其中 s. = 'F'

按 c.name 分组

对于需求2，大多数人可以不假思索地想到以下两种编写SQL的方式。哪一个是正确的？

c.name, count(s.name) 作为 num

FROM c 左连接 s

在 s. = c.id

其中 c.name = 'Class 1'

按 c.name 分组

或者

c.name, count(s.name) 作为 num

FROM c 左连接 s

在 s. = c.id

和 c.name = 'Class 1'

按 c.name 分组

请不要向下滚动！请先给出您自己的答案。正确答案如下。

答案是第一条语句对于两个要求都是正确的。要理解这个问题，需要先了解MySQL对于left join的执行原理，下一节会展开讲到。

来源

MySQL 使用嵌套循环的方式来处理左连接。以以下语句为例：

* 从 LT 左连接 RT 至 P1(LT,RT)) 其中 P2(LT,RT)

P1 为 on 过滤条件，缺失则为 TRUE。P2 为 where 过滤条件，缺失则同样为 TRUE。该语句的执行逻辑可以描述为：

FOR each row lt in LT {// 遍历左表的每一行

布尔b = 假；

FOR each row rt in RT such that P1(lt, rt) {// 遍历右表的每一行，并找到满足连接条件的行

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也会采用这种方式进行优化，减少行比较的次数，但是这并不影响key的执行过程，不在本文的讨论范围内。

从这个伪代码中我们可以看出两点：如果要限制右表，就必须在on条件中做，如果在where条件中做，可能会造成数据缺失，导致左表中在右表中没有匹配行的行不会出现在最终结果中，这就违背了我们对left join的理解。因为对于左表中在右表中没有匹配行的行，在遍历完右表之后b=FALSE，所以我们会尝试用NULL填充右表，但是此时我们的P2对右表中的行进行了限制，如果NULL不满足P2（NULL一般不满足限制条件，除非是IS NULL），就不会加入到最终结果中，导致结果缺失。

如果没有where条件，无论on条件对左表作何限制，左表的每一行都会有至少一行合成结果。对于左表的某一行，如果右表中没有对应的行，右表遍历完成后b=FALSE，会用一行NULL来生成数据，这是多余的。因此必须使用where来筛选左表。

以下是两个必填错误语句的执行结果及错误原因：

要求 1

要求 2

需求1：由于where条件右表限制，数据缺失（类别4的结果应该为0）

需求2由于on条件中对左表的限制，导致数据冗余（其他类的结果也是可以的，但是依然是错的）

总结

通过以上问题现象及分析，我们可以得出结论：在left join语句中，左表过滤一定要放在where条件里，右表过滤一定要放在on条件里，这样结果才不会太多也不会太少，刚刚好。

SQL看似简单，其实包含很多细节和原则，一个小小的失误就可能导致结果与预期不一致，所以在关键时刻要注意这些细节和原则，避免犯错。