Why do WHERE and HAVING exist as separate clauses in SQL?

Question

I understand the distinction between WHERE and HAVING in a SQL query, but I don't see why they are separate clauses. Couldn't they be combined into a single clause that could handle both aggregated and non-aggregated data?

Answer 1

Here's the rule. If a condition refers to an aggregate function, put that condition in the HAVING clause. Otherwise, use the WHERE clause.

Here's another rule: You can't use HAVING unless you also use GROUP BY.

The main difference is that WHERE cannot be used on grouped item (such as SUM(number)) whereas HAVING can.The reason is the WHERE is done before the grouping and HAVING is done after the grouping is done.

ANOTHER DIFFERENCE IS WHERE clause requires a condition to be a column in a table, but HAVING clause can use both column and alias.

Here's the difference:

SELECT `value` v FROM `table` WHERE `v`>5;

Error #1054 - Unknown column 'v' in 'where clause'

SELECT `value` v FROM `table` HAVING `v`>5; -- Get 5 rows

WHERE clause requires a condition to be a column in a table, but HAVING clause can use both column and alias.

This is because WHERE clause filters data before select, but HAVING clause filters data after select.

So put the conditions in WHERE clause will be more effective if you have many many rows in a table.

Try EXPLAIN to see the key difference:

EXPLAIN SELECT `value` v FROM `table` WHERE `value`>5;
+----+-------------+-------+-------+---------------+-------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key   | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+-------+---------+------+------+--------------------------+
|  1 | SIMPLE      | table | range | value         | value | 4       | NULL |    5 | Using where; Using index |
+----+-------------+-------+-------+---------------+-------+---------+------+------+--------------------------+

EXPLAIN SELECT `value` v FROM `table` having `value`>5;
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key   | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
|  1 | SIMPLE      | table | index | NULL          | value | 4       | NULL |   10 | Using index |
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+

You can see either WHERE or HAVING uses index, but the rows are different.

So there is a need of both of them especially when we need grouping and additional filters.

Answer 2

This question seems to illustrate a misunderstanding that WHERE and HAVING are both missing up to 1/2 of the information necessary to fully process a query.

Consider the following SQL:

drop table if exists foo; create table foo (
  ID int,
  bar int
); insert into foo values (1, 1);

select now() as d, bar as b
from foo
where bar = 1 and d <= now()
having bar = 1 and ID = 1
;

In the where clause, d is not available because the selected items have not been processed to create it yet.

In the having clause ID has been discarded because it was not selected. In aggregate queries ID may not even have meaning in context of multiple rows combined into one. ID may also be meaningless when joining different tables into a single result.

Answer 3

Could it be done? Sure, but on the back-end it'd do the same as it does now, because you have to aggregate something before you can filter based on that aggregation. Ultimately that's the reason, it's a logical separation of different processes. Why waste resources aggregating records you could have filtered with a WHERE?

Answer 4

The question could only be fully answered by the designer since it asks intent. But the implication is that both clauses do the same thing only against aggregated vs. non-aggregated data. That's not true. "The HAVING clause is typically used together with the GROUP BY clause to filter the results of aggregate values. However, HAVING can be specified without GROUP BY."

As I understand it, the important thing is that "The HAVING clause specifies additional filters that are applied after the WHERE clause filters."

http://technet.microsoft.com/en-us/library/ms179270(v=sql.105).aspx