SQL语句中过滤条件放在on和where子句中的区别和联系

摘要: 介绍在多表关联SQL语句中，过滤条件放在on和where子句中的区别——inner join中没区别，外连接就不一样。

综述

  蚂蚁金服的一道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 的查询效率要高。

小结

  感兴趣的话，请移步《SQL语句中过滤条件放在on、where、having的区别和联系》，了解更多。

  以上就是这篇文章的全部内容了，希望本文对大家的学习或者工作具有一定的参考和学习价值；如果有疑问，大家可以在评论区留言交流，也希望大家多多点赞关注。谢谢大家对楼兰胡杨的支持！

Reference

posted @ 2022-09-17 21:57  楼兰胡杨  阅读(1634)  评论(0)  编辑  收藏  举报