WHERE vs HAVING

The Core Contrast

Running Example

All examples in this chapter use one small orders table:

sql
CREATE TABLE orders (
    id      INT PRIMARY KEY,
    region  VARCHAR(10),
    product VARCHAR(10),
    amount  NUMERIC(8,2)
);

Total of all 8 rows: 1060. Per region: East = 310, West = 550, North = 200. Per product: Widget = 530, Gadget = 530 (100+200+50+120+60 and 150+300+80).

Side-by-Side Example

Row-level condition → use WHERE. "Only consider West and East orders, then total by region":

sql
SELECT region, SUM(amount) AS total_amount
FROM orders
WHERE region IN ('East', 'West')
GROUP BY region
ORDER BY region;

WHERE discards the 2 North rows before grouping — the engine only ever aggregates the remaining 6 rows.

Group-level condition → use HAVING. "Only show regions whose total exceeds 300":

sql
SELECT region, SUM(amount) AS total_amount
FROM orders
GROUP BY region
HAVING SUM(amount) > 300
ORDER BY region;

All 8 rows are aggregated into 3 groups first, then the 300 threshold is checked against each group's SUM(amount).

Both queries happen to return the same two regions here, but for entirely different reasons: the first physically excluded North's rows before any summing happened; the second summed everything and then discarded the North group because its total (200) failed the check.

Combining Both

They are not mutually exclusive — most real reporting queries use both, each for its own job:

sql
SELECT region, SUM(amount) AS total_amount
FROM orders
WHERE product = 'Widget'          -- row filter: only Widget orders
GROUP BY region
HAVING SUM(amount) > 100          -- group filter: only regions totaling > 100
ORDER BY region;

Widget-only rows: East (100, 60 → 160), West (200, 50 → 250), North (120). All three exceed 100, so all three appear.

Why WHERE First Is Cheaper

WHERE executes before grouping, so rows it eliminates never have to be scanned, bucketed, or summed at all — less data reaches the (more expensive) grouping/aggregation step. HAVING conditions, by contrast, force the engine to fully group and aggregate everything first, then throw entire groups away. Rule of thumb: push every condition you can express in terms of raw row values into WHERE; reserve HAVING strictly for conditions that need an aggregate result (SUM, COUNT, AVG, etc.) to evaluate.

Edge Cases

• Writing WHERE SUM(amount) > 300 is a hard error in every mainstream dialect — aggregates simply don't exist yet at the WHERE stage.
• Writing HAVING region = 'West' instead of WHERE region = 'West' is not an error, just wasted work — it aggregates North/East/West and then drops two whole groups instead of never touching their rows.
• A query can have WHERE, GROUP BY, and HAVING together, in that fixed clause order, or any subset of them.

Key Takeaways / Interview Q&A

• *Q: Can WHERE reference an aggregate function like COUNT()?**

A: No — that always requires HAVING.

• Q: Which runs first, WHERE or HAVING?

A: WHERE, because it filters rows before GROUP BY creates the groups that HAVING later filters.

• Q: Given the orders table, which is more efficient — `WHERE region = 'West' GROUP BY region` or `GROUP BY region HAVING region = 'West'`?

A: The WHERE version — it discards non-West rows immediately instead of aggregating all regions first.

• Q: When is HAVING unavoidable?

A: Only when the filter condition itself depends on an aggregate result (e.g., "regions with total sales over 300").