Why is there just one HAVING in SQL?

Question

In SQL there is WHERE and HAVING: ... WHERE ... GROUP BY ... HAVING ....

Why isn't there multiple GROUP BY and multiple HAVINGs per SELECT statement like: ... WHERE ... GROUP BY ... HAVING ... GROUP BY ... HAVING ....

Why is one WHERE and one HAVING enough? Or isn't it enough?

Answer 1

The distinction between the where-clause and having-clause, is that one filters the rows before aggregation, the other after. They are essentially the same filtering operation, just performed at different stages in the evaluation of the query.

The reason why there cannot be multiple such clauses, evaluated one after another in order of their appearance (similar to joins), is simply an inherited limitation of the SQL language, the fundamentals of which were laid down in the 1970s.

The effect of multiple group-by- and having-clauses can still be achieved (at modest additional length) with nested queries (or "derived tables").

Crucially however, in the original SQL language design, there was no facility for nested queries. Everything you wanted to achieve in a single statement, had to be done in a single flat query (i.e. with at most one appearance of each clause, and no nesting). Joins did not have their own separate clause.

I believe the facility to query nested tables was only introduced in SQL 92 (and the with-clause, defining "common table expressions", was introduced only in SQL 99). The reason the facility didn't exist before then was because database engines couldn't handle the complexity of parsing and executing such queries - you could either use stored procedures with multiple statements and intermediate tables, or do the processing in application code.

But the need to filter rows before and after aggregation, was obviously considered such an important need from the outset, that the where-clause and having-clause were incorporated as part of the original (i.e. pre-standardised) design of the SQL language.