MySQL：left join 避坑指南

原文地址：https://mp.weixin.qq.com/s/ktZpTSKqXjAtOAbXrSMMtg

作者：MageekChiu

segmentfault.com/a/1190000020458807

总结：

在 left join 语句中，左表过滤必须放 where 条件中，右表过滤必须放 on 条件中，这样结果才能不多不少，刚刚好。

廖雪峰的在线 SQL：https://www.liaoxuefeng.com/wiki/1177760294764384/1179611432985088

1. 现象

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

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

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

SELECT * FROM classes;

SELECT * FROM students;

那么现在有两个需求：

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

对于需求 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 的执行原理，下节进行展开。

2. 根源

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

需求 2

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

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

3. 总结

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

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

打赏