WHERE vs HAVING — what's the difference, and which is faster?
⚡ Short Answer
WHERE filters rows BEFORE grouping/aggregation; HAVING filters groups AFTER aggregation. WHERE can use indexes and reduces rows early (faster), so push filters into WHERE and reserve HAVING for conditions on aggregates like COUNT(*) > 5.
☕Coffee Chat Question
Concept Made Simple
“WHERE vs HAVING — what's the difference, and which is faster?”
🧠Mind Map Answer
Remember It Faster
⌨️Hands-on Keyboard
Learn by Doing
SELECT region, COUNT(*) AS orders
FROM orders
WHERE status = 'PAID' -- row filter (uses index)
GROUP BY region
HAVING COUNT(*) > 100; -- group filter🔥What If?
Think Beyond the Expected
Someone filters a non-aggregate column in HAVING — why is that a code smell?
Filtering a plain column in HAVING forces the DB to group ALL rows first, then discard groups — wasteful. That predicate belongs in WHERE so rows are eliminated before grouping and an index can help.
😂Real World
Moving a misplaced row-filter from HAVING to WHERE is a common quick win on slow reporting queries — it cuts the rows that reach the aggregation step.
🎯Interviewer's Expectation
Keywords they're listening for:
⚠️Common Mistakes
- ✗Putting row filters in HAVING
- ✗Expecting WHERE to filter aggregates
- ✗Assuming SELECT aliases are usable in WHERE
✅Best Practices
- ✓Filter rows in WHERE, groups in HAVING
- ✓Index the WHERE predicates
- ✓Reduce the working set as early as possible
🔁Follow-up Questions
- 1Can you reference a SELECT alias in WHERE? In HAVING?
- 2What's the logical order of SQL clause evaluation?
- 3When is HAVING genuinely required?
🧩Related Technologies
Continue Learning with AI
Take this question deeper with your favourite AI assistant. Pick a depth, copy the prompt, or open it directly — AI is your learning companion, not a shortcut.
Plain-language foundations
I'm preparing for a software engineering interview and want to understand this from scratch, as a beginner. Topic: Optimization (SQL) Interview question: "WHERE vs HAVING — what's the difference, and which is faster?" Please: 1. Explain the core idea in simple, plain language, using an everyday analogy. 2. Define any technical terms you use. 3. Walk through one small, concrete example. 4. Finish with a single sentence I can easily remember. Keep the tone friendly and assume I'm new to this topic.
Was this answer helpful?
⭐ Featured Products
Support our platform by exploring our recommended products.
As an Amazon affiliate, purchases through these links may earn us a small commission — at no extra cost to you. It helps keep Full Stack Interview Guru free.