Mysql中left join使用注意事项

原文跳转到原文

两张表：

　　班级表(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的方式进行优化，减少行比较次数，不过这不影响关键的执行流程，不在本文讨论范围之内。

从这个伪代码中，我们可以看出两点：

1. 如果想对右表进行限制，则一定要在on条件中进行，若在where中进行则可能导致数据缺失，导致左表在右表中无匹配行的行在最终结果中不出现，违背了我们对left join的理解。因为对左表无右表匹配行的行而言，遍历右表后b=FALSE,所以会尝试用NULL补齐右表，但是如果条件P2对右表行进行了限制，那么NULL&P2 的结果极有可能也是FALSE，导致没有合并行的出现, 左表的数据则不会加入最终的结果中，导致结果缺失。
2. 如果没有where条件，无论on条件对左表进行怎样的限制，左表的每一行都至少会有一行的合成结果，对左表行而言，若右表若没有对应的行，则右表遍历结束后b=FALSE，会用一行NULL来生成数据，而这个数据是多余的。所以对左表进行过滤必须用where。

根据以上分析我们来用s.sex这个条件来论证：

　　当s.sex在where中时候，先便利c表，根据join去遍历右表，对于三班而言右表学生表是满足的，再对where判断，因为s.sex的存在，右表又不满足了，此时设置b=true,下面的if(!b)也被跳过了，导致t没有数据

　　当s.sex在join中时候，先便利c表，根据join去遍历右表，对于三班而言右表学生表是不满足的，此时b=false保持不变,下面进入f(!b)，用null补全右表，t有数据

以上分析我们可以得出出错原因： 由于在where条件中对右表限制，导致数据缺失（三班 四班应该有个为0的结果）

我的总结：先拿左表数据再根据on条件取右表数据，右表有数据补齐，无数据补null；再根据where条件对该条数据进行取舍。在左表有数据前提下，把右表的条件放到on里，最终结果都会返回数据；把右表条件放到where里, 则最终结果会根据where中右表的条件进行数据取舍