The difference between WHERE and HAVING

WHERE

在单表查询中，在query执行开始时处理。我们过去认为它决定了查询将返回哪些行，但这并不准确：

WHERE determines which rows will be processed by the query.

当我们使用group by时，区别会变得很明显。

HAVING

在单表查询中，在query执行结束时处理。

After all rows have been processed, HAVING determines which of rows will be sent to the client.

The differences

SELECT department_id, count(*) AS employees_no FROM employee WHERE gender = 'F' GROUP BY department_id HAVING employees_no < 10;

• WHERE excludes non-female employees. Those rows are not read at all by the query.
• GROUP BY groups (or aggregates) the found rows, producing only one row for each distinct .department_id
• HAVING eliminates the aggregated rows where is less than 10.employees_no

注意：

• WHERE employees_no < 10 would fail with an error, because that value doesn’t exist before aggregation.
• HAVING gender = 'F' would fail with an error, because the gender column doesn’t exist in the aggregated rows (or, if you prefer, in the clause).SELECT

Query performance

SELECT * FROM employee WHERE date_of_birth > '2000-01-01';

HAVING读取所有行，返回符合条件的行

WHERE只读取符合条件的行，如果有索引则使用索引。

Note that some DBMSs are smart enough to translate an unnecessary HAVING clause into a WHERE clause.