HAVING vs WHERE

WHERE and HAVING both filter rows, but they run at different points in a query, which is the whole reason both exist.

WHERE filters before grouping

WHERE filters individual rows, before GROUP BY has combined anything:

SELECT customer_id, SUM(total) AS total_spent
FROM orders
WHERE order_date >= '2026-01-01'
GROUP BY customer_id;

This keeps only orders from 2026 onward, then groups and sums what's left. WHERE has no idea what an aggregate result will look like, because aggregation hasn't happened yet when WHERE runs — which is exactly why you can't write WHERE SUM(total) > 100. At the point WHERE is evaluated, there's no SUM to compare against yet, only individual rows.

HAVING filters after grouping

HAVING filters groups, after GROUP BY and the aggregate functions have already run:

SELECT customer_id, SUM(total) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(total) > 100;

This computes every customer's total first, then keeps only the customers whose total exceeds 100. HAVING is the only clause that can reference an aggregate function's result directly in its condition.

Using both together

WHERE and HAVING commonly appear in the same query, each doing its own job:

SELECT customer_id, COUNT(*) AS order_count
FROM orders
WHERE order_date >= '2026-01-01'   -- filter rows first: only recent orders
GROUP BY customer_id
HAVING COUNT(*) > 5;               -- then filter groups: only frequent customers

Filtering with WHERE wherever possible, before grouping, is also better for performance — it shrinks the number of rows the database has to group and aggregate in the first place, versus computing aggregates over everything and discarding some of them afterward with HAVING.

Can you use a SELECT alias in HAVING?

This varies by dialect. MySQL, PostgreSQL, and SQLite allow referencing a SELECT alias in HAVING:

SELECT customer_id, SUM(total) AS total_spent
FROM orders
GROUP BY customer_id
HAVING total_spent > 100; -- works in MySQL/PostgreSQL/SQLite

Some databases (notably older SQL Server versions) don't, and require repeating the full expression (HAVING SUM(total) > 100) instead — one more spot where checking your specific database's behavior matters more than assuming the standard.

The next lesson covers CASE expressions — for adding conditional, if/else-style logic directly inside a query.