Unit 04.02: WHERE before grouping, HAVING after

Unit ID: SQL-M04-U03 - Estimated active time: 12-15 minutes Objective: place a condition in the clause that produces the meaning you intend.

The two clauses filter different things

From Module 2's evaluation order: WHERE runs before GROUP BY; HAVING runs after.

• WHERE removes rows before groups are formed.
• HAVING removes groups after they are formed.

That is not a style choice. It changes the answer.

Same condition, two meanings

-- Groups formed from completed orders only
SELECT status, COUNT(*) AS orders
FROM orders
WHERE status = 'completed'
GROUP BY status;
-- completed | 988

-- All groups formed, then small ones discarded
SELECT status, COUNT(*) AS orders
FROM orders
GROUP BY status
HAVING COUNT(*) > 100;
-- completed | 988

The first restricted the input. The second restricted the output. Here both return one row, but they answer different questions: "how many completed orders" versus "which statuses have more than 100 orders". The 12 pending orders exist in the second query's world and were judged; in the first they were never considered.

The rule of thumb

Filter on a column value → WHERE. Filter on an aggregate → HAVING.

-- Correct: aggregate condition belongs in HAVING
SELECT status, ROUND(AVG(order_total), 2) AS avg_total
FROM orders
GROUP BY status
HAVING AVG(order_total) > 2000;
-- completed | 2718.53

Putting AVG(order_total) > 2000 in WHERE is an error in every engine: the average does not exist yet when WHERE runs.

Why WHERE first is usually better

When both would work, prefer WHERE. It discards rows before the grouping work happens, so the database does less. More importantly it states your intent earlier, where a reader sees it.

Practice

Write a query listing each country with more than 500 customers, excluding customers with no country. Say which condition goes where and why.

SELECT country, COUNT(*) AS customers
FROM customers
WHERE country IS NOT NULL
GROUP BY country
HAVING COUNT(*) > 500
ORDER BY customers DESC;
-- IN | 2813
-- GB |  902
-- AE |  516

Takeaway

WHERE shapes the input, HAVING judges the output. If the condition mentions an aggregate, it cannot run in WHERE - the value does not exist yet.

---

Mark complete