SQL Academy · Lesson

HAVING vs WHERE

Distinguish HAVING (filters groups) from WHERE (filters rows), and apply both correctly in the same query.

HAVING vs WHERE is a free SQL Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Two Filter Stages

WHERE filters individual rows BEFORE grouping. HAVING filters groups AFTER aggregation. They look similar but operate at different stages.

WHERE Example

WHERE filters raw rows. It cannot see aggregates:

-- Sum totals for rows where status is 'paid':
SELECT user_id, SUM(total) AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY user_id;

HAVING Example

HAVING filters groups. It can reference aggregates:

SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) >= 5;     -- only users with ≥ 5 orders

Combine WHERE and HAVING

Both can appear in the same query:

SELECT user_id, SUM(total) AS revenue
FROM orders
WHERE created_at >= NOW() - INTERVAL '90 days'
GROUP BY user_id
HAVING SUM(total) > 1000;

Don't Put Aggregates in WHERE

The planner runs WHERE before grouping — aggregates aren't available yet:

-- WRONG:
SELECT user_id, COUNT(*) FROM orders
WHERE COUNT(*) > 5         -- ERROR
GROUP BY user_id;

-- RIGHT — move to HAVING:
SELECT user_id, COUNT(*) FROM orders
GROUP BY user_id
HAVING COUNT(*) > 5;

Push Predicates to WHERE When Possible

WHERE filters before grouping, so fewer rows enter the aggregation. Only use HAVING for things that can't be expressed in WHERE.

-- Slightly slower (filters after grouping):
SELECT user_id, COUNT(*) FROM orders
GROUP BY user_id
HAVING user_id IN (1,2,3);

-- Faster (filters before grouping):
SELECT user_id, COUNT(*) FROM orders
WHERE user_id IN (1,2,3)
GROUP BY user_id;

Logical Execution Order

Conceptual order:

FROM — pick tables and join
WHERE — filter rows
GROUP BY — make groups
HAVING — filter groups
SELECT — project columns
ORDER BY — sort
LIMIT — top N

HAVING Without GROUP BY

You can use HAVING with no GROUP BY — the whole table becomes one group:

SELECT SUM(total) FROM orders
HAVING SUM(total) > 1000000;       -- prints only if total over 1M

Aliases in HAVING

Some databases let HAVING reference SELECT aliases; PostgreSQL does, MySQL does, standard SQL doesn't. Most portable: repeat the aggregate:

SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) >= 5;       -- portable
-- HAVING order_count >= 5  -- works in PG / MySQL

HAVING with Multiple Aggregates

Use HAVING for compound aggregate conditions:

SELECT user_id, COUNT(*) AS orders, SUM(total) AS revenue
FROM orders
GROUP BY user_id
HAVING COUNT(*) >= 5 AND SUM(total) > 1000;

Performance Note

An aggregate filter in HAVING can't use indexes the way WHERE on a column can. Move what you can to WHERE.

Recap

WHERE = filter rows. HAVING = filter groups.

Aggregates only in HAVING, never WHERE
Push predicates to WHERE for speed
Use both when needed

Quick Check

You want users who have placed more than 10 orders. Which clause filters that?

Frequently asked questions

Is the “HAVING vs WHERE” lesson free?

Yes — the full text of “HAVING vs WHERE” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “HAVING vs WHERE”?

Distinguish HAVING (filters groups) from WHERE (filters rows), and apply both correctly in the same query. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “HAVING vs WHERE” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Academy