Mysql连接查询时查询条件放在On之后和Where之后的区别

背景

在一次对数据进行统计的时候，需要对两张表进行关联，类似于这样的语句a left join b on a.id = b.id where b.name = xx。发现最终的结果和预期不一致，汇总之后的数据变少了。

一开始还比较费解，后面回过神来才发现，犯了一个低级的错误，就是在使用left join时过滤条件放到on后面还是where后面是有区别的，如果没有搞清楚他们的区别，连表汇总的结果就会变少或者变多。

探究

利用廖雪峰提供的在线工具，利用student表和classes表我们做一个测试，

student表

 classes表

1.统计每个班级中女生的数量

问题SQL

select a.name, count(b.name) as num from classes a left join students b 
on a.id = b.class_id
where b.gender = 'F'
group by a.name

 查询结果

 正确的写法

select a.name, count(b.name) as num 
from classes a left join students b 
on a.id = b.class_id and b.gender = 'F'
group by a.name

查询结果

2： 只统计‘一班’的学生数量

错误的写法

select a.name, count(b.name) as num 
from classes a left join students b 
on a.id = b.class_id and a.name = '一班'
group by a.name　

查询结果

 正确的写法

select a.name, count(b.name) as num 
from classes a left join students b 
on a.id = b.class_id 
where a.name = '一班'
group by a.name　

查询结果

 原因

 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补上的行
    }         
  }
}

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

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

 

问题一错误的原因：由于在where条件中对右表限制，导致数据缺失（四班应该有个为0的结果）

问题二错误的原因：由于在on条件中对左表限制，导致数据多余（其他班的结果也出来了，还是错的）。on 后跟关联表（从表）的过滤条件，如果再加筛选条件只针对关联表！

on 后跟关联表（从表）的过滤条件，where 后跟主表或临时表的筛选条件（左连接为例，主表的数据都会查询到，所以临时表中必定包含主表所有的字段，需要给主表加什么筛选条件，直接给临时表加效果相同） 

总结

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

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

 

本篇文章如有帮助到您，请给「翎野君」点个赞，感谢您的支持。

首发链接：https://www.cnblogs.com/lingyejun/p/16380106.html