Timeline for Why is there just one HAVING in SQL?
Current License: CC BY-SA 4.0
10 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jul 9, 2020 at 19:58 | comment | added | Steve | @BartvanIngenSchenau, it's also worth saying, in the original design of the SQL language (and still to some extent), there isn't a one-to-one correspondence between clauses and operators, and the order of the clauses does not strictly reflect the order of evaluation. For example, use of the group-by-clause usually involves aggregation operators being specified separately in the select-clause. But over the years, there has been a tendency to make the SQL language conform more closely to the idea of a series of operators being evaluated in order of appearance (as with the 92-style join syntax). | |
| Jul 9, 2020 at 19:44 | comment | added | Steve | @BartvanIngenSchenau, agreed, in such cases (with no other operator between, such as a group-by, or an additional join, etc.) additional where-clauses would be unnecessary, since they would be logically equivalent to ANDing the criteria contained in both clauses. | |
| Jul 9, 2020 at 18:10 | comment | added | Bart van Ingen Schenau | I understand the difference between WHERE and HAVING and why it is useful to have both. I meant the second WHERE as in 'WHERE A WHERE B'. | |
| Jul 9, 2020 at 16:04 | comment | added | Steve | @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). | |
| Jul 9, 2020 at 8:50 | comment | added | Bart van Ingen Schenau | You could also say that the second (and third, etc) WHERE is spelled AND. | |
| Jul 9, 2020 at 8:20 | comment | added | marstato |
@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.
|
|
| Jul 8, 2020 at 20:43 | vote | accept | porton | ||
| Jul 8, 2020 at 20:31 | comment | added | Steve | @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. | |
| Jul 8, 2020 at 20:01 | comment | added | porton | Can you give an example when multiple HAVINGs would be necessary (given we can't use subqueries or multiple queries)? | |
| Jul 8, 2020 at 19:10 | history | answered | Steve | CC BY-SA 4.0 |