WHERE vs HAVING: Execution Order Breakdown and Examples

Let’s go over the execution order of operations in SQL one more time, and also talk about what WHERE and HAVING can and can’t work with. This is a super important point, and a lot of SQL query nuances are built on top of it. You really need to get this down.

So what’s the difference between WHERE and HAVING, when should you use one or the other, and how are they connected? This will help you not get lost in query logic and filter your data effectively. Let’s sum up what we know about WHERE and HAVING.

What is WHERE?

WHERE is a condition that’s used to filter rows before grouping or applying aggregate functions. So, first you pick out the rows from the table that match your criteria, and only then do you group the remaining data.

👉 Imagine you’re picking fruit at a market. WHERE is like a filter that helps you toss out the bad apples before you even start sorting them by size or color.

Example:

SELECT *
FROM students
WHERE age > 18;

This query selects all students older than 18 before any other operations happen.

What is HAVING?

HAVING is a filter that’s applied after data is grouped (GROUP BY). It lets you set conditions on grouped data, like keeping only those groups where the average student grade is above 80.

👉 Back to the apples example. HAVING is a filter you use after you’ve already sorted apples into baskets (groups). Now you care, for example, only about those baskets (groups) that have more than ten apples.

Example:

SELECT basket, COUNT(*)
FROM apples
GROUP BY basket
HAVING COUNT(*) > 10;

This query will select only those baskets where the number of apples is greater than 10.

Main differences:

Execution order of WHERE, GROUP BY and HAVING

To get a better feel for how WHERE and HAVING work, let’s look at the order SQL queries are executed in:

1. First, FROM runs and picks rows from the table.
2. Then WHERE is applied, filtering only those rows that match the conditions.
3. After that, grouping happens with GROUP BY. You get a new table with group data.
4. HAVING is applied, filtering groups that match the conditions.
5. Finally, SELECT results are picked.

Here’s a schematic:

1. FROM → 2. WHERE → 3. GROUP BY → 4. HAVING → 5. SELECT

Example:

SELECT department, AVG(age) AS avg_age
FROM students
WHERE age > 18
GROUP BY department
HAVING AVG(age) > 20;

Here’s what’s happening:

1. Rows are picked from the students table where age > 18 (WHERE is used).
2. The remaining rows are grouped by department.
3. The average age of students is calculated for each group.
4. Groups where the average age is less than or equal to 20 are excluded by HAVING.
5. The results are shown.

Examples of combined usage

Example 1: Filtering before and after grouping

Task: Find departments with more than 5 students, but only counting students older than 18.

Source table students

Query:

SELECT department, COUNT(*) AS student_count
FROM students
WHERE age > 18
GROUP BY department
HAVING COUNT(*) > 5;

Result: -- Query result

Explanation:

1. First, we remove rows where age <= 18 (WHERE condition).
2. We group the data by department (GROUP BY department).
3. We count the number of students in each group.
4. We remove groups where there are 5 or fewer students (HAVING COUNT(*) > 5).

Example 2: Error when using WHERE instead of HAVING

Task: Find departments where the average age is greater than 22.

Incorrect query:

SELECT department, AVG(age) AS avg_age
FROM students
WHERE AVG(age) > 22
GROUP BY department;

Error: SQL doesn’t let you use aggregate functions like AVG in WHERE, because aggregates aren’t calculated at that stage yet.

Correct query:

SELECT department, AVG(age) AS avg_age
FROM students
GROUP BY department
HAVING AVG(age) > 22;

Here, the condition AVG(age) > 22 is applied after grouping.

Practical tips

If you need to filter rows, use WHERE. Example: Find all employees with a salary over 5000.

SELECT *
FROM employees
WHERE salary > 5000;

If you need to filter groups, use HAVING. Example: Find departments with a total salary over 100,000.

SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department
HAVING SUM(salary) > 100000;

Combine WHERE and HAVING for more complex conditions.

Example: Find countries with a population over 10 million, but only counting cities with a population over 1 million.

SELECT country, SUM(population) AS total_population
FROM cities
WHERE population > 1000000
GROUP BY country
HAVING SUM(population) > 10000000;

Common mistakes and how to fix them

One of the most common mistakes is mixing up WHERE and HAVING. For example, trying to use an aggregate function in WHERE:

SELECT department, COUNT(*)
FROM students
WHERE COUNT(*) > 10
GROUP BY department;

This query will throw an error, because aggregate calculations aren’t available at the WHERE stage. The right way is to use HAVING:

SELECT department, COUNT(*)
FROM students
GROUP BY department
HAVING COUNT(*) > 10;

Another mistake is picking the wrong conditions for WHERE. For example:

SELECT department, AVG(age) AS avg_age
FROM students
WHERE avg_age > 20
GROUP BY department;

Here, the condition avg_age > 20 is incorrect, because avg_age hasn’t been calculated yet. The fix is to move this condition to HAVING:

SELECT department, AVG(age) AS avg_age
FROM students
GROUP BY department
HAVING AVG(age) > 20;

Hope you’ve got a clear understanding now of how WHERE and HAVING are different, how to use them right, and how to avoid common mistakes. This stuff will come in handy when you’re building complex reports, filtering data for analysis, and optimizing queries. Basically, for most real SQL queries you’ll ever write :)