EasyExperience level: 0-2 years · 3-5 years1 min read

WHERE vs HAVING — what's the difference, and which is faster?

Reviewed by Gurusankar M.

Asked inInfosysTCSCognizantAccenture
#where#having#group by#aggregate#filtering
Report issue

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.

On this page

Concept Made Simple

Coffee Chat Question

“WHERE vs HAVING — what's the difference, and which is faster?”

Remember It Faster

Mind Map Answer

WHERE
filters rows before GROUP BY (index-usable)
HAVING
filters groups after aggregation
Rule
filter early in WHERE; aggregates in HAVING

Learn by Doing

Hands-on Keyboard

sql
SELECT region, COUNT(*) AS orders
FROM orders
WHERE status = 'PAID'        -- row filter (uses index)
GROUP BY region
HAVING COUNT(*) > 100;       -- group filter

Think Beyond the Expected

What If?

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:

  • before vs after aggregation
  • WHERE uses indexes
  • HAVING for aggregates
  • filter early

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

  1. Can you reference a SELECT alias in WHERE? In HAVING?
  2. What's the logical order of SQL clause evaluation?
  3. When is HAVING genuinely required?

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.
Open inChatGPTGeminiClaude

Was this answer helpful?

Affiliate links

Tools for Your Developer Workspace

Equipment for a comfortable, productive setup.

As an Amazon Associate I earn from qualifying purchases.