HAVING vs WHERE
Filtering before grouping versus after, and which clause sees the aggregate.
HAVING vs WHERE is a free SQL Interview Prep lesson on CoddyKit — lesson 2 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 Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
The Question You Will Get
'What is the difference between WHERE and HAVING?' is one of the most asked SQL interview questions. A weak answer says 'HAVING is for aggregates.' A strong answer explains when each clause runs in the query pipeline.
That timing is the whole story: WHERE filters rows before grouping; HAVING filters groups after aggregation.
Where They Sit in Execution Order
Recall the logical execution order of a query:
FROM/JOIN→ build the row setWHERE→ filter individual rowsGROUP BY→ collapse into groupsHAVING→ filter the groupsSELECT→ project columnsORDER BY→ sort
WHERE happens before groups exist; HAVING happens after, so HAVING can see aggregates and WHERE cannot.
WHERE Cannot See Aggregates
Because WHERE runs before grouping, it has no aggregate values yet. Writing WHERE COUNT(*) > 5 is a syntax error in every standard database.
Interviewers plant this exact line to check whether you understand the pipeline. The aggregate does not exist when WHERE is evaluated.
-- ERROR: aggregate not allowed in WHERE
SELECT department, COUNT(*)
FROM employees
WHERE COUNT(*) > 5
GROUP BY department;HAVING Filters the Groups
Move the aggregate condition to HAVING and it works, because HAVING runs after the groups and their aggregates are computed.
Read it as: 'group the employees, then keep only departments whose count exceeds five.'
SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;Put Row Filters in WHERE
The reverse mistake is filtering raw rows in HAVING. It often gives the right answer but is slower and misleading, because you grouped rows you intended to discard.
Rule of thumb: filter on a raw column value → WHERE. Filter on an aggregate → HAVING. Doing row filtering early shrinks the data the grouping must process.
-- Better: drop inactive rows BEFORE grouping
SELECT department, COUNT(*) AS headcount
FROM employees
WHERE status = 'active'
GROUP BY department
HAVING COUNT(*) > 5;Both Clauses Together
A complete query frequently uses both. WHERE narrows rows first; HAVING then keeps qualifying groups. Reading top to bottom matches the logical order.
Worked example: among orders placed this year, find customers who spent more than 1000 in total.
SELECT customer_id, SUM(amount) AS total_spent
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY customer_id
HAVING SUM(amount) > 1000;HAVING on Non-Aggregated Columns
HAVING can reference a grouping column, not just aggregates. HAVING department = 'Sales' is legal but pointless: that filter belongs in WHERE so it runs earlier.
If an interviewer shows you a HAVING that filters a plain grouped column, the expected critique is 'move it to WHERE for efficiency.'
-- Works but inefficient; prefer WHERE department = 'Sales'
SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING department = 'Sales';HAVING Without GROUP BY
A subtle one: HAVING is legal even without GROUP BY. The whole table becomes a single implicit group, and HAVING filters that one group.
If the aggregate condition is false, you get zero rows; if true, one row. Rarely useful, but interviewers ask it to confirm you understand the implicit-group concept.
-- Returns the count only if the table has > 100 rows
SELECT COUNT(*) AS total
FROM orders
HAVING COUNT(*) > 100;Can HAVING Use a SELECT Alias?
Like the alias-scope trick elsewhere, dialects differ. Postgres and MySQL let HAVING reference a SELECT alias; SQL Server and Oracle do not.
The portable habit is to repeat the aggregate expression in HAVING. It works in every engine and avoids surprises in a cross-database interview.
-- Portable: repeat the aggregate, do not rely on the alias
SELECT region, SUM(amount) AS total
FROM sales
GROUP BY region
HAVING SUM(amount) > 5000;Performance Framing
To impress, connect the clauses to performance: WHERE reduces rows the grouping engine must scan and can use indexes; HAVING runs on already-aggregated groups, so it cannot reduce the grouping cost.
The takeaway interviewers want: push every filter as early as possible. Only conditions that genuinely depend on an aggregate need HAVING.
The One-Sentence Answer
Memorize this for the interview: 'WHERE filters rows before grouping and cannot see aggregates; HAVING filters groups after aggregation and is the only clause that can test an aggregate value.'
Follow it with the execution-order list and you have given a complete, senior-sounding answer.
Quick Check
Decide which clause each condition belongs in.
Recap
WHERE: filters rows before GROUP BY, no aggregates allowed. HAVING: filters groups after aggregation, the only place an aggregate condition is legal.
- Put raw-column filters in WHERE for speed and index use.
- HAVING can reference grouped columns but should not for plain filters.
- HAVING works without GROUP BY on the whole-table implicit group.
- Repeat aggregate expressions in HAVING for cross-dialect safety.
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 Interview Prep 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 Interview Prep course, upgrade to CoddyKit PRO.
What will I learn in “HAVING vs WHERE”?
Filtering before grouping versus after, and which clause sees the aggregate. You practise SQL Interview Prep 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 Interview Prep?
No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 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 Interview Prep lesson?
Yes. Every SQL Interview Prep 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.