WHERE vs. HAVING Clause

Easy

Interview Question: "What is the difference between WHERE and HAVING in SQL? Why can't aggregate functions be used in a WHERE clause, and what is the severe performance penalty of placing non-aggregate filters in HAVING?"

The Quick Answer

"The fundamental distinction lies in when the filter is evaluated and what granularity of data it operates on:

• WHERE filters individual raw rows before grouping occurs and can leverage B-Tree indexes. It cannot use aggregate functions.
• HAVING filters aggregated summary groups after GROUP BY has executed in memory. It is designed specifically for aggregate conditions (COUNT, SUM, AVG)."

---

The 8-Step SQL Query Execution Lifecycle

To truly impress an interviewer, explain why WHERE cannot use aggregates by walking through the database's internal logical processing pipeline:

1. FROM & JOIN  ───> Identify and combine table sources
2. WHERE        ───> Filter raw individual rows (Index-backed)
3. GROUP BY     ───> Aggregate rows into distinct buckets
4. HAVING       ───> Filter calculated group buckets
5. SELECT       ───> Project requested columns & calculate window functions
6. DISTINCT     ───> Deduplicate rows
7. ORDER BY     ───> Sort the final result set
8. LIMIT/OFFSET ───> Paginate returned records

• Why WHERE SUM(salary) > 50000 is impossible: At Step 2 (WHERE), the database is simply reading individual rows off disk or cache. The GROUP BY grouping operation (Step 3) has not occurred yet. Because groups do not yet exist, aggregate functions (SUM, COUNT, AVG) are mathematically undefined at the time WHERE executes, resulting in: ERROR: aggregate functions are not allowed in WHERE.

---

Key Comparison

Feature	WHERE Clause	HAVING Clause
Operates On	Individual raw rows	Grouped / Aggregated rows
Pipeline Step	Step 2: Before GROUP BY	Step 4: After GROUP BY
Aggregate Functions?	❌ No (WHERE COUNT(*) > 1 is an error)	✅ Yes (HAVING SUM(amount) > 1000)
Index Utilization	✅ Yes. Can directly scan B-Tree indexes	❌ No. Evaluates computed buckets in RAM
Primary Goal	Minimize dataset before heavy aggregation	Filter out groups failing business criteria

---

The Performance Trap: Non-Aggregate Conditions in HAVING

Interviewers frequently present a query like this and ask for a critique:

-- ❌ BAD PERFORMANCE ANTIPATTERN:
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING department_id = 10 AND AVG(salary) > 80000;

• The Problem: Standard SQL allows non-aggregated columns in HAVING (if they exist in GROUP BY). However, placing department_id = 10 in HAVING forces the engine to sort, hash, and aggregate all 1,000,000 rows across all 50 departments in memory, only to discard 49 departments at Step 4!
• The Fix: Move non-aggregate conditions into WHERE:

  -- ✅ OPTIMIZED:
  SELECT department_id, AVG(salary)
  FROM employees
  WHERE department_id = 10 -- Filters rows immediately using the index!
  GROUP BY department_id
  HAVING AVG(salary) > 80000;

---

The ELI5 Analogy: Grading Classroom Quizzes

Imagine a teacher sorting test papers from 10 classrooms:

• WHERE (Row filtering before grouping): Before doing any math, the teacher immediately throws out papers where the student cheated: WHERE cheated = FALSE.
• GROUP BY: The teacher sorts the remaining clean papers into 10 separate stacks, one for each classroom.
• HAVING (Group filtering after summary math): The teacher calculates the average grade for each classroom stack. Then, the teacher only throws a pizza party for classrooms whose average exceeds 85%: HAVING AVG(score) > 85.

---

Crucial Nuance: Can HAVING Be Used Without GROUP BY?

Yes. If a query contains a HAVING clause but no GROUP BY, the engine treats the entire table as a single, global group:

SELECT AVG(salary) 
FROM employees 
HAVING COUNT(*) > 10;

If the table contains 10 or fewer total rows, the query returns an empty result set (0 rows). If it contains more than 10 rows, it returns the single overall average.