HAVING Clause

In the previous lesson, we learned how to use GROUP BY to summarize data. Now we need a way to filter those grouped results.

That is exactly what the HAVING clause does.

Why HAVING is Needed

Let’s say you want to answer this question:

• Show only departments where the average salary is greater than 65,000

We cannot use WHERE here because AVG(salary) is calculated after grouping.

This is where HAVING comes in.

WHERE vs HAVING (Very Important)

Many beginners confuse WHERE and HAVING. They are used at different stages of query execution.

Basic HAVING Syntax

The HAVING clause is always used with GROUP BY.

SELECT column_name, AGGREGATE_FUNCTION(column)
FROM table_name
GROUP BY column_name
HAVING condition;
  

Example Table

We will continue using the employees table:

+----+----------+------------+--------+
| id | name     | department | salary |
+----+----------+------------+--------+
| 1  | Alice    | IT         | 75000  |
| 2  | Bob      | IT         | 72000  |
| 3  | Charlie  | HR         | 60000  |
| 4  | Diana    | Finance    | 70000  |
| 5  | Evan     | HR         | 58000  |
+----+----------+------------+--------+
  

HAVING with AVG

Example: Departments with average salary greater than 65,000.

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 65000;
  

Explanation:

• Rows are grouped by department
• AVG(salary) is calculated per group
• HAVING filters groups based on the aggregate result

HAVING with COUNT

Example: Departments with more than one employee.

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 1;
  

Using WHERE and HAVING Together

WHERE and HAVING can be used in the same query. Each has a different role.

Example:

• Include only employees with salary above 60,000
• Then show departments with average salary above 70,000

SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE salary > 60000
GROUP BY department
HAVING AVG(salary) > 70000;
  

Execution logic:

1. WHERE filters individual rows
2. GROUP BY creates groups
3. HAVING filters the grouped results

Common Beginner Mistakes

• Using HAVING without GROUP BY
• Using WHERE instead of HAVING for aggregates
• Forgetting aggregate functions inside HAVING
• Assuming HAVING replaces WHERE

Execution Order (Final Picture)

Understanding execution order makes SQL much easier:

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

Why HAVING is Powerful

HAVING allows you to:

• Filter summary results
• Build advanced reports
• Apply conditions on aggregated data
• Answer complex business questions

What’s Next?

In the next lesson, we will learn how to use IN, BETWEEN, and LIKE to create flexible and expressive filters.