WHERE vs HAVING in SQL — what is the actual difference?

WHERE filters rows before grouping. HAVING filters groups after aggregation.

Memory hook

WHERE works on rows. HAVING works on groups.

Example

-- Sales above ₹1000, grouped by city, only cities totalling > ₹50k
SELECT city, SUM(amount) AS total
FROM sales
WHERE amount > 1000           -- row-level filter (before aggregation)
GROUP BY city
HAVING SUM(amount) > 50000;   -- group-level filter (after aggregation)

The flow:

1. WHERE amount > 1000 discards rows where the individual sale was tiny.
2. GROUP BY city rolls them into one row per city.
3. HAVING SUM(amount) > 50000 discards cities whose total is still small.

Cannot use aggregates in WHERE

-- ❌ error: aggregate functions are not allowed in WHERE
SELECT city, SUM(amount) FROM sales WHERE SUM(amount) > 50000 GROUP BY city;

-- ✅ correct
SELECT city, SUM(amount) FROM sales GROUP BY city HAVING SUM(amount) > 50000;

Cannot reference non-grouped columns

-- ❌ error: 'amount' is not a grouped column
SELECT city, SUM(amount) FROM sales GROUP BY city HAVING amount > 1000;

-- ✅ — use WHERE for row-level filter
SELECT city, SUM(amount) FROM sales WHERE amount > 1000 GROUP BY city;

Performance tip

Always push filters to WHERE when possible. Filtering 10 million rows down to 100 thousand BEFORE aggregation is far cheaper than aggregating all 10 million and then filtering groups.

-- Slower
SELECT user_id, COUNT(*) FROM events GROUP BY user_id HAVING user_id < 1000;

-- Faster — same result, fewer rows aggregated
SELECT user_id, COUNT(*) FROM events WHERE user_id < 1000 GROUP BY user_id;

Edge case — HAVING without GROUP BY

SELECT SUM(amount) FROM sales HAVING SUM(amount) > 100000;

Valid — the implicit "single group" of all rows. Used rarely; usually you just do the comparison in application code.