WHERE vs HAVING: what is the difference?

Definition:

WHERE filters individual rows before groups are built, while HAVING filters the groups produced by aggregation. They exist at different logical stages of a query. Use WHERE for conditions on source rows and HAVING for conditions such as “the group's total is greater than 1000.”

Key points:

• WHERE filters rows before grouping.

• HAVING filters groups after aggregation.

• Example: filter paid orders with WHERE, then customers spending over 1000 with HAVING.

• A condition on an aggregate such as SUM(amount) generally belongs in HAVING, not WHERE.

Example:

SELECT customer_id, SUM(amount) AS total
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING SUM(amount) > 1000;

• Only paid orders enter the groups; only customer groups over 1000 remain.

Interview point:

• Explain the rows that enter the group separately from the groups that survive the final filter.