SQL: The difference between WHERE and HAVING

Re:SQL: The difference between WHERE and HAVING by gpvos on Tuesday December 14, 2021 @08:12AM (#29551122)

Huh, I always thought HAVING was the equivalent for GROUP BY that WHERE is for SELECT, i.e. that it happened during aggregation, just as WHERE happens during data retrieval from the table. But apparently you can use HAVING instead of WHERE and it would work the same even if you don't have a GROUP BY clause (and even though it would possibly be less efficient)? Seems a bit redundant.

[ Parent | on HN ]

Re:SQL: The difference between WHERE and HAVING by maweki on Tuesday December 14, 2021 @08:16AM (#29551150)

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.

[ Parent | on HN ]

Re:SQL: The difference between WHERE and HAVING by dspillett on Tuesday December 14, 2021 @08:52AM (#29551459)

> 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

[ Parent | on HN ]

Re:SQL: The difference between WHERE and HAVING by namibj on Tuesday December 14, 2021 @09:14AM (#29551669)

I 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.

[ Parent | on HN ]

Re:SQL: The difference between WHERE and HAVING by gpvos on Tuesday December 14, 2021 @09:07AM (#29551604)

I 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.

[ Parent | on HN ]

Re:SQL: The difference between WHERE and HAVING by frozenice on Tuesday December 14, 2021 @09:19AM (#29551739)

Think 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).

[ Parent | on HN ]

Re:SQL: The difference between WHERE and HAVING by tgv on Tuesday December 14, 2021 @08:55AM (#29551483)

I 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

[ Parent | on HN ]

Re:SQL: The difference between WHERE and HAVING by throwawayffffas on Tuesday December 14, 2021 @09:03AM (#29551559)

Yeah, you are correct it's really just syntactic sugar for that. As another comment points out, WHERE and HAVING are just σ in relational algebra.

[ Parent | on HN ]

Re:SQL: The difference between WHERE and HAVING by maweki on Tuesday December 14, 2021 @09:43AM (#29552025)

In 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.

[ Parent | on HN ]

Re:SQL: The difference between WHERE and HAVING by FedericoRazzoli on Tuesday December 14, 2021 @12:50PM (#29554694)

You are correct, HAVING is essentially syntax sugar. But to be fair, I'm glad it exists.

[ Parent | on HN ]

Re:SQL: The difference between WHERE and HAVING by tgv on Tuesday December 14, 2021 @12:59PM (#29554844)

Sure, and I remember the version of mySQL that didn't have sub-queries. Having HAVING has your back, then.

[ Parent | on HN ]

Re:SQL: The difference between WHERE and HAVING by FedericoRazzoli on Tuesday December 14, 2021 @12:53PM (#29554747)

You are right, though (some?) DBMSs allow to use HAVING for columns that appear in the GROUP BY clause.

[ Parent | on HN ]