文章目录
WHERE 与 HAVING
WHERE 与 ON
SQL 提供了多种对数据进行过滤的方式，包括WHERE、HAVING以及ON子句等。虽然它们都能够实现类似的功能，但是你知道它们之间的区别吗？让我们一起来探讨一下。
除非特殊说明，以下内容适用于各种数据库，包括 MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite 等；其中的示例表和数据来源于 GitHub。
WHERE 与 HAVING
WHERE与HAVING的根本区别在于：
WHERE子句在GROUP BY分组和聚合函数之前对数据行进行过滤；
HAVING子句对GROUP BY分组和聚合函数之后的数据行进行过滤。
因此，WHERE子句中不能使用聚合函数。例如，以下语句将会返回错误：
-- 查找人数大于 5 的部门
select dept_id, count(*)
from employee
where count(*) > 5
group by dept_id;
由于在执行WHERE子句时，还没有计算聚合函数 count(*)，所以无法使用。正确的方法是使用HAVING对聚合之后的结果进行过滤：
-- 查找人数大于 5 的部门
select dept_id, count(*)
from employee
group by dept_id
having count(*) > 5;
dept_id|count(*)|
-------|--------|
4| 9|
5| 8|
另一方面，HAVING子句中不能使用除了分组字段和聚合函数之外的其他字段。例如，以下语句将会返回错误：
-- 统计每个部门月薪大于等于 30000 的员工人数
select dept_id, count(*)
from employee
group by dept_id
having salary >= 30000;
因为经过GROUP BY分组和聚合函数之后，不再存在 salary 字段，HAVING子句中只能使用分组字段或者聚合函数。
⚠️SQLite 虽然允许HAVING子句中出现其他字段，但是得到的结果不正确。
从性能的角度来说，HAVING子句中如果使用了分组字段作为过滤条件，应该替换成WHERE子句；因为WHERE可以在执行分组操作和计算聚合函数之前过滤掉不需要的数据，性能会更好。下面示例中的语句 1 应该替换成语句 2：
-- 语句 1
select dept_id, count(*)
from employee
group by dept_id
having dept_id = 1;
-- 语句 2
select dept_id, count(*)
from employee
where dept_id = 1
group by dept_id;
当然，WHERE和HAVING可以组合在一起使用。例如：
select dept_id, count(*)
from employee
where salary > 10000
group by dept_id
having count(*) > 1;
dept_id|count(*)|
-------|--------|
1| 3|
该语句返回了月薪大于 10000 的员工人数大于 1 的部门；WHERE用于过滤月薪大于 10000 的员工；HAVING用于过滤员工数量大于 1 的部门。
WHERE 与 ON
当查询涉及多个表的关联时，我们既可以使用WHERE子句也可以使用ON子句指定连接条件和过滤条件。这两者之间的主要区别在于：
对于内连接（inner join）查询，WHERE和ON中的过滤条件等效；
对于外连接（outer join）查询，ON中的过滤条件在连接操作之前执行，WHERE中的过滤条件（逻辑上）在连接操作之后执行。
对于内连接查询而言，以下三个语句的结果相同：
-- 语句 1
select d.dept_name, e.emp_name, e.sex, e.salary
from employee e, department d
where e.dept_id = d.dept_id
and e.emp_id = 10;
dept_name|emp_name|sex|salary |
---------|--------|---|-------|
研发部 |廖化 |男 |6500.00|
-- 语句 2
select d.dept_name, e.emp_name, e.sex, e.salary
from employee e
join department d on (e.dept_id = d.dept_id and e.emp_id = 10);
dept_name|emp_name|sex|salary |
---------|--------|---|-------|
研发部 |廖化 |男 |6500.00|
-- 语句 3
select d.dept_name, e.emp_name, e.sex, e.salary
from employee e
join department d on (e.dept_id = d.dept_id)
where e.emp_id = 10;
dept_name|emp_name|sex|salary |
---------|--------|---|-------|
研发部 |廖化 |男 |6500.00|
语句 1 在WHERE中指定连接条件和过滤条件；语句 2 在ON中指定连接条件和过滤条件；语句 3 在ON中指定连接条件，在WHERE中指定其他过滤条件。上面语句不但结果相同，数据库的执行计划也相同。以 MySQL 为例，以上语句的执行计划如下：
id|select_type|table|partitions|type |possible_keys |key |key_len|ref |rows|filtered|Extra|
--|-----------|-----|----------|-----|--------------------|-------|-------|-----|----|--------|-----|
1|SIMPLE |e | |const|PRIMARY,idx_emp_dept|PRIMARY|4 |const| 1| 100| |
1|SIMPLE |d | |const|PRIMARY |PRIMARY|4 |const| 1| 100| | 📝关于各种数据库的执行计划，可以参考这篇文章。
尽管如此，仍然建议将两个表的连接条件放在ON子句中，将其他过滤条件放在WHERE子句中；这样语义更加明确，更容易阅读和理解。对于上面的示例而言，推荐使用语句 3 的写法。
对于外连接而言，连接条件只能用ON子句表示，因为WHERE子句无法表示外连接的语义。
