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."

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:

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

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What is the primary difference between the WHERE and HAVING clauses?

Question 2

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

Question 3

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

Question 4

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

Question 5

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

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

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Using DISTINCT with Aggregate Functions

Filtering Groups with HAVING vs WHERE

Beginner12 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Filtering Groups with HAVING vs WHERE: Critical Distinctions

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."

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:

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

Why does this fail?

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

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What is the primary difference between the WHERE and HAVING clauses?

Question 2

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

Question 3

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

Question 4

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

Question 5

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

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

Filtering Groups with HAVING vs WHERE: Critical Distinctions

1. The Core Architectural Difference

2. Comparing WHERE and HAVING Side-by-Side

3. Real-World Practical Example

4. Why Can't WHERE Use Aggregate Functions?

Why does this fail?

5. Best Practices & Common Pitfalls

Multiple Choice Questions

Using DISTINCT with Aggregate Functions

Related Lessons

Practice Quiz

Loading Page Content...

Filtering Groups with HAVING vs WHERE: Critical Distinctions

1. The Core Architectural Difference

2. Comparing WHERE and HAVING Side-by-Side

3. Real-World Practical Example

4. Why Can't WHERE Use Aggregate Functions?

Why does this fail?

5. Best Practices & Common Pitfalls

Multiple Choice Questions

Using DISTINCT with Aggregate Functions

Related Lessons

Practice Quiz

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

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