MySQL语句中过滤条件放在哪？on、where还是having，他们区别和联系

SQL语句中过滤条件放在不同筛选器on、where和having的区别和联系。

蚂蚁金服的一道SQL面试题如下SQL语句中过滤条件放在on和where子句中的区别是什么 当时满脑子是left join和inner join等觉得没区别啊当天晚上细思极恐故梳理一下。

在多表连接查询语法中最令人迷惑的非on和where这两个筛选器的区别莫属了。在编写查询SQL脚本的时候筛选条件无论是放在on子句还是where子句查询到的结果集总是一模一样的既然如此为什么还要让sql查询支持两种筛选器呢这不是多此一举吗其实这两种筛选器在执行效率方面存在差异只是如果不深度挖掘不容易发现罢了。

过滤条件放在on和where中的区别

数据库在通过连接两张或多张表来查询记录时都会先通过join on子句生成一张中间的临时表然后再在临时表中通过where子句过滤数据并将结果集返回给用户。在使用多表关联时on和where子句的区别如下

1、 on子句是在生成临时表时使用的。它不管on中的条件是否为真都会返回驱动表中的记录被驱动表成立就返回对应数据不成立就赋值为null。

通俗地说 对于左外连接left join或者右外连接right join的驱动表来说如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录那么该记录仍然会被加入到结果集中对应的被驱动表记录的各个字段使用NULL值填充。

2、where子句是对已经生成的临时表进行过滤的条件这时过滤临时表中全部条件不为真的记录。

需要注意的是内连接中的WHERE子句和ON子句是等价的。ON子句是专门为外连接驱动表中的记录在被驱动表找不到匹配记录时应不应该把该记录加入结果集这个场景提出的所以如果把ON子句放到内连接中MySQL会把它和WHERE子句一样对待。

在使用 INNER JOIN 时会产生一个结果集WHERE 条件在这个结果集中再根据条件进行过滤如果把条件都放在 ON 中在 INNER JOIN 的时候就进行过滤了比如

SELECT A.* FROM A
INNER JOIN B ON B.ID = A.ID AND B.State = 1 -- on子句可以写多个条件
INNER JOIN C ON B.ID = C.ID

在联查 B 表时就过滤掉状态不等于 1 的记录从而使得状态不等于 1 的记录就不需要去联查 C 表了而

SELECT A.* FROM A
INNER JOIN B ON B.ID = A.ID
INNER JOIN C ON B.ID = C.ID
WHERE B.State = 1

则不考虑 B 的状态是否满足都去联查 C生成临时表最后再通过where子句将满足B 状态State = 1的记录查出来。综上所述得出的结论就是把 过滤条件放入inner join on 比直接 where 的查询效率要高。

on、where、having的区别和联系

在写SQL语句的时候我们经常会用到各种表连接left join、right join、inner join和 full join等还有各种分组聚合函数sum、min、max、avg和count等。那么我们在写连接操作SQL的时候对于不同的过滤条件是放在 ON 子句还是代表分组操作的 having 子句抑或 where子句中呢我们先看一下三种条件关键字的执行顺序

on > where > 聚合函数 > having

详细的来讲就是

步骤一、根据on筛选器生成生成临时表。此时的临时表会因为left join或right join的特性而一定带有主表的记录也就是主表的记录不会被 on 条件过滤掉。

步骤二、根据where筛选器过滤临时表。因为临时表已经生成完毕根据where条件过滤时主表记录也会被过滤。

步骤三、聚合函数进行运算。

步骤四、 聚合函数运算完毕having子句生效对运算完毕的临时表进行过滤 生成最终的结果表。

三个筛选器on、where和having都可以加条件on是最先执行where次之having最后。有时候如果这先后顺序不影响中间结果的话那最终结果是相同的。但因为on是先把不符合条件的记录过滤后才进行统计它就可以减少中间运算要处理的数据按理说应该速度是最快的。

了解了上述执行过程后我们来聊聊应该把过滤条件放在哪即如何使用on、where和having筛选器。下面是一些基础的选择标准供各位参考

1. 所有的连接条件都必需要放在ON子句。
2. 如果在表关联后需要保留主表的所有记录不论有没有相匹配的从表记录那么我们就应该将过滤条件放在 on 中。
3. 如果过滤条件需要在聚合函数运算完毕之后才能确定比如我们想要找出平均分数大于60分的班级那么就必须等待分组聚合函数执行完毕才能进行过滤那这个过滤条件肯定就是放在having中了因为where生效的时候聚合函数还没有进行运算。
4. 如果过滤条件不依赖聚合函数只是想要符合条件的部分记录而且没有要求保留主表的全部记录那么我们就应该放在where子句中。当然如果表关联是采用inner join的话因为没有主从表的关系所以放在 where 和 on 中是一样的。
5. 就执行效率来看因为on生效最早所以放在on中应该最快其次是where最后是having。