4 ms·
It's not redundant. As you point out: selecting rows _before_ aggregation and _after_ are different beasts. You can't exclude rows from aggregation with HAVING.
by maweki 5y ago
It's not redundant. As you point out: selecting rows _before_ aggregation and _after_ are different beasts. You can't exclude rows from aggregation with HAVING.
- dspillett 5y ago> You can't exclude rows from aggregation with HAVING. In theory¹ a DB that supports skip-scanning could use feedback from some HAVING clauses to short-circuit such a scan significantly, for instance if HAVING COUNT(*)<4 is present and a fourth row for that grouping comes up it can discard the row and skip to the next value. This would likely only be useful for indexes containing large numbers of the same few values though. [1] I'm not aware of any engine that does this. It might be that the time cost of assessing if the optimisation is worthwhile² far outways the sum benefit over time of the saving when it is actually found to be worthwhile. [2] Similarly, writing+testing+maintaining the code to do so might not be good use of dev time compared to other improvements
- namibj 5y agoI believe I sorta did something like this recently in differential reasoner. See this code here: <insert link to the code range in differential reasoner in the file where I use the count trace to throw out ineligible ones>. It's only _kinda_ like this, though. I will post the link later, if necessary in a comment.
- tgv 5y agoI don't think HAVING is strictly needed, but you need to use a sub-query. SELECT x ... HAVING y => SELECT * FROM (x ...) t WHERE y
- throwawayffffas 5y agoYeah, you are correct it's really just syntactic sugar for that. As another comment points out, WHERE and HAVING are just σ in relational algebra.
- maweki 5y agoIn that sense, HAVING and WHERE being different is basically a "free" subquery without allowing subqueries. One inner σ (WHERE) and one outer σ (HAVING). If subqueries are allowed, then you don't need that.
- FedericoRazzoli 5y agoYou are correct, HAVING is essentially syntax sugar. But to be fair, I'm glad it exists.
- tgv 5y agoSure, and I remember the version of mySQL that didn't have sub-queries. Having HAVING has your back, then.
- gpvos 5y agoI was thinking more in terms of during than before or after. And that HAVING was part of the GROUP BY syntax, and could therefore not be used without it.
- frozenice 5y agoThink of it this way: there is always a GROUP BY (implicitly), i.e. GROUP BY rownum(). So there is always a big group with all the rows (or rather many small groups).
- FedericoRazzoli 5y agoYou are right, though (some?) DBMSs allow to use HAVING for columns that appear in the GROUP BY clause.