Filtering Groups with HAVING vs WHERE0%

Filtering Groups with HAVING vs WHERE

Beginner12 min read•Updated: 2026-09-12

Filtering Groups with HAVING vs WHERE: Critical Distinctions

One of the most frequent questions in SQL interviews and architectural design reviews is: "What is the difference between WHERE and HAVING?". Both clauses filter data, but they operate at fundamentally different stages of the query execution pipeline. Understanding this distinction is mandatory for writing aggregate queries.

1. The Core Architectural Difference

Visual Architecture Blueprint

+-----------------------------------------------------------------+
   | WHERE  ==> Filters INDIVIDUAL ROWS BEFORE grouping occurs.      |
   |            CANNOT use aggregate functions (e.g. WHERE AVG > 50).|
   +-----------------------------------------------------------------+
                                  |
                                  v
   +-----------------------------------------------------------------+
   | GROUP BY ==> Collapses remaining rows into aggregate buckets.   |
   +-----------------------------------------------------------------+
                                  |
                                  v
   +-----------------------------------------------------------------+
   | HAVING ==> Filters SUMMARY GROUPS AFTER aggregation has computed.|
   |            CAN and DOES use aggregate functions!                |
   +-----------------------------------------------------------------+

2. Comparing WHERE and HAVING Side-by-Side

Feature	`WHERE` Clause	`HAVING` Clause
Stage of Execution	Pre-aggregation (Filters raw table rows).	Post-aggregation (Filters aggregated summary rows).
Can use Aggregates?	NO (`WHERE SUM(amount) > 100` is a syntax error!).	YES (`HAVING SUM(amount) > 100` is valid!).
Operates on	Individual table rows.	Grouped buckets generated by `GROUP BY`.
Indexes Utilized?	Yes (Can use standard B-Tree indexes directly).	Generally no (Filters intermediate grouped memory buffers).

3. Real-World Practical Example

Suppose management requests: "Find all departments with more than 5 active employees where the average salary exceeds ₹60,000."

SQL

1SELECT 
2    department,
3    COUNT(employee_id) AS total_employees,
4    AVG(salary) AS average_salary
5FROM employees
6WHERE is_active = TRUE         -- 1. WHERE: Discards inactive employees BEFORE grouping!
7GROUP BY department            -- 2. GROUP BY: Groups active employees by department
8HAVING COUNT(employee_id) > 5  -- 3. HAVING: Discards departments with <= 5 employees
9   AND AVG(salary) > 60000.00  --    and departments with avg salary <= 60000!
10ORDER BY average_salary DESC;  -- 4. ORDER BY: Sorts the remaining qualifying departments

4. Why Can't `WHERE` Use Aggregate Functions?

Consider this common error:

SQL

1-- WRONG! Syntax Error: Invalid use of group function
2SELECT department, AVG(salary) 
3FROM employees 
4WHERE AVG(salary) > 50000;

Why does this fail?

Because WHERE evaluates while scanning individual rows. At the moment row 1 is being read, the average salary of the entire department does not exist yet! Aggregates only exist after all rows have been scanned and grouped.

5. Best Practices & Common Pitfalls

Do Not Put Non-Aggregate Filters in HAVING: While MySQL syntactically permits writing HAVING is_active = TRUE, this is an architectural anti-pattern! It forces the engine to group millions of inactive rows before filtering them out in memory. Always filter raw rows in WHERE as early as possible!
Complete SQL Query Execution Sequence:

1
FROM & JOIN
2
WHERE
3
GROUP BY
4
HAVING
5
SELECT
6
DISTINCT
7
ORDER BY
8
LIMIT

Multiple Choice Questions

1. What is the primary difference between the `WHERE` and `HAVING` clauses?

A. WHERE is for MySQL; HAVING is for Oracle B. WHERE filters individual rows before grouping; HAVING filters aggregated groups after grouping C. WHERE is case-sensitive; HAVING is not D. HAVING only works with numbers Answer: B Explanation: WHERE filters raw candidate rows prior to aggregation, while HAVING filters aggregated summary rows after the GROUP BY operation.

2. Why does the query `SELECT dept, SUM(sales) FROM stores WHERE SUM(sales) > 100000 GROUP BY dept;` fail?

A. The word stores is misspelled B. Aggregate functions cannot be used in a WHERE clause because groups have not yet been formed C. SUM must be lowercase D. GROUP BY must appear before WHERE Answer: B Explanation: WHERE operates on individual rows before group calculations occur; filtering based on aggregate values must be performed using HAVING.

3. In the logical order of SQL execution, when does the `HAVING` clause execute?

A. Immediately before FROM B. After GROUP BY and before SELECT C. At the very end after LIMIT D. Before the WHERE clause Answer: B Explanation: In the SQL processing order, HAVING evaluates immediately after GROUP BY to filter the aggregated group buckets.

4. Which query correctly finds product categories that have generated more than ₹500,000 in total sales?

A. SELECT category, SUM(total) FROM orders WHERE SUM(total) > 500000 GROUP BY category; B. SELECT category, SUM(total) FROM orders GROUP BY category HAVING SUM(total) > 500000; C. SELECT category, SUM(total) FROM orders HAVING total > 500000; D. SELECT category FROM orders FILTER SUM(total) > 500000; Answer: B Explanation: GROUP BY category HAVING SUM(total) > 500000 correctly collapses rows by category and filters groups by their aggregate sum.

5. Why is it a performance mistake to write `HAVING status = 'Active'` instead of `WHERE status = 'Active'`?

A. MySQL does not support status in HAVING B. It forces the database to unnecessarily process and group inactive rows before discarding them, wasting CPU and memory C. It deletes the active rows D. It locks the transaction log Answer: B Explanation: Filtering with WHERE discards unwanted rows immediately, reducing the data volume entering the GROUP BY stage and utilizing indexes effectively.

Next Lesson

Using DISTINCT with Aggregate Functions

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Grouping Data with GROUP BY	Using DISTINCT with Aggregate Functions

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PrevNext