SQL语句过滤条件在on与where子句

摘要:

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

综述

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

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

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

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

通俗地说 ，对于左外连接或者右外连接的驱动表来说，如果无法在被驱动表中找到匹配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 AND B.State = 1
INNER JOIN C ON B.ID = C.ID
WHERE B.State = 1

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

小结

Reference

• https: