Stage 1: From returns the whole world of rows.
Stage 2: Where filters down to the desired set of rows.
Stage 3: Group By aggregates the filtered rows.
Stage 4: Having filters again on the aggregated result.
Stage 5: Select picks out the columns.
(I know that I am probably missing some important technical points, I would like to learn about them)
select product
, sum(price) as price
from table
where price<1000
having price>10000
Is there an implicit group by in this query?
If that's not a requirement, this approach can add some clarity
Find me the list of non-deleted users who have more than 50 dollars worth of transactions in the transaction table.
Technically you can always subquery and use a where instead of a having but its nice to ... have.
WITH A AS (
SELECT x, sum(y) AS z
FROM SomeTable
GROUP BY x
)
SELECT * FROM A WHERE z > 10
Also, although it's not an issue these days given how good query planners are (any decent engine will produce exactly the same query plan with a subquery or an having clause, it's indexes that fuck up stuff), but you're signaling that the filter happens "at the end".
It's like having both "while" and "for" in a programming language. Technically you don't need it, but it's for humans and not for compilers.