0

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?

8

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.

8
  • Can you give an example when multiple HAVINGs would be necessary (given we can't use subqueries or multiple queries)? – porton Jul 8 '20 at 20:01
  • 1
    @porton, I can't think of a concrete and non-contrived example offhand of grouping-then-filtering-then-grouping-then-filtering, but I could imagine some sort of statistical process employing it. – Steve Jul 8 '20 at 20:31
  • 2
    @porton it's hard to give a concrete example without a schema and a use case (too much for a comment). At work i often join multiple WITH ... AS ... tables together. Most of the temporary tables have GROUP BY and HAVING. I then do a final WHERE in the top-level SELECT ... FROM clause. – marstato Jul 9 '20 at 8:20
  • You could also say that the second (and third, etc) WHERE is spelled AND. – Bart van Ingen Schenau Jul 9 '20 at 8:50
  • @BartvanIngenSchenau, that is only true in certain circumstances. Otherwise, there would never have been any need for the having-clause. Speaking algebraically (but using the familiar terms for the operators), where the input to a where-operator is the result of a group-by-operator, or where the input to a group-by-operstor is the result of a where-operator, then the order of operations matters, and expressing that order is why there is a distinction between the where-clause and the having-clause (which both cause a where-operator to be applied, but in a specific order). – Steve Jul 9 '20 at 16:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.