Hacker News Reader

Indeed. Just as I think git’s N different ways to refer to the same operation was a blunder.

But pre- and post- aggregation filtering is not really "the same" operation.

If I use a CTE and filter the aggregate, feels the same to me.

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

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

The main (only?) task I routinely use HAVING for is finding duplicates.

Reply on news.ycombinator.com