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(NULL一般都不会满足限制条件,除非IS NULL这种),则不会加入最终的结果中,导致结果缺失。
  2. 如果没有where条件,无论on条件对左表进行怎样的限制,左表的每一行都至少会有一行的合成结果,对左表行而言,若右表若没有对应的行,则右表遍历结束后b=FALSE,会用一行NULL来生成数据,而这个数据是多余的。所以对左表进行过滤必须用where。

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

表的关联查询返回的是表的合并行,之前一直认为左关联是以左表为基表,返回所有左表所有数据,右表自动补全,理解过于片面了;

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

 

上一篇:40个GitHub上最受欢迎的iOS开源项目


下一篇:Caused by: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Truncated incorrect DOUBLE value: 'L