4 ms·
In the query plan, filtering before or after an aggregation is the same, so it's a strange quirk that SQL requires a different word.
by singron 2y ago
In the query plan, filtering before or after an aggregation is the same, so it's a strange quirk that SQL requires a different word.
- 0cf8612b2e1e 2y agoIndeed. Just as I think git’s N different ways to refer to the same operation was a blunder.
- andy800 2y agoBut pre- and post- aggregation filtering is not really "the same" operation.
- 0cf8612b2e1e 2y agoIf I use a CTE and filter the aggregate, feels the same to me.
- andy800 2y agoIf you perform an aggregation query in a CTE, then filter on that in a subsequent query, that is different, because you have also added another SELECT and FROM. You would use WHERE in that case whether using a CTE or just an outer query on an inner subquery. HAVING is different from WHERE because it filters after the aggregation, without requiring a separate query with an extra SELECT.
- RaftPeople 2y ago> HAVING is different from WHERE because it filters after the aggregation, without requiring a separate query with an extra SELECT. Personally I rarely use HAVING and instead use WHERE with subqueries for the following reasons: 1-I don't like repeating/duplicating a bunch of complex calcs, easier to just do WHERE in outer query on result 2-I typically have outer queries anyway for multiple reasons: break logic into reasonable chunks for humans, also for join+performance reasons (to give the optimizer a better chance at not getting confused)
- sgarland 2y agoThe main (only?) task I routinely use HAVING for is finding duplicates.
- andy800 2y agoI was not there at the original design decisions of the language, but I imagine it was there specifically to help the person writing/editing the query easily recognize and interpret filtering before or after an aggregation. The explicitness makes debugging a query much easier and ensures it fails earlier. I don't see much reason to stop distinguishing one use case from the other, I'm not sure how that helps anything.
- 0cf8612b2e1e 2y agoI think this stems from the non-linear approach to reading a SQL statement. If it were top-to-bottom linear, like PRQL, then the distinction does not seem merited. It would then always be filtering from what you have collected up to this line.
- yen223 2y agoI think the original sin here is not making aggregation an explicitly separate thing, even though it should be. Adding a count(*) fundamentally changes what the query does, and what it returns, and what restrictions apply.
- singron 2y agoI also wasn't there, but I think this actually wasn't to help authors and instead was a workaround for the warts of SQL. It's a pain to write SELECT * FROM (SELECT * FROM ... GROUP BY ...) t WHERE ... and they decided this was common enough that they would introduce a HAVING clause for this case SELECT * FROM ... GROUP BY ... HAVING ... But the real issue is that in order to make operations in certain orders, SQL requires you to use subselects, which require restating a projection for no reason and a lot of syntactical ceremony. E.g. you must give the FROM item a name (t), but it's not required for disambiguation. Another common case is projecting before the filter. E.g. you want to reuse a complicated expression in the SELECT and WHERE clauses. Standard SQL requires you to repeat it or use a subselect since the WHERE clause is evaluated first.