HAVING Clause — SQL Tutorial with Examples

What You'll Learn

HAVING filters groups produced by GROUP BY. It's WHERE for aggregated results — applied after aggregation, unlike WHERE which runs before. You can use aggregate functions in HAVING that aren't in SELECT.

Syntax

SELECT col, AGG(col2) FROM table
GROUP BY col
HAVING AGG(col2) > value;

Example

-- Departments with more than 1 employee
SELECT department_id, COUNT(*) AS headcount
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 1;

-- WHERE and HAVING together
SELECT department_id, AVG(salary) AS avg_sal
FROM employees
WHERE hire_date >= '2021-01-01'   -- rows first: drops Alan (hired 2020)
GROUP BY department_id
HAVING AVG(salary) > 70000;       -- groups after

Beyond the Basics

Data in play — reference tables for this topic

-- employees (from Topic 1)
-- id | name    | department_id | salary | hire_date
-- 1  | Ada     | 1             | 145000 | 2021-03-01
-- 2  | Grace   | 1             | 115000 | 2022-06-13
-- 3  | Alan    | 2             | 92000  | 2020-11-02
-- 4  | Edsger  | NULL          | 78000  | 2023-08-19
-- 5  | Barbara | 3             | 64000  | 2024-01-08

-- orders (from Topic 7)
-- id | customer_id | status    | total
-- 1  | 1           | shipped   | 240.00
-- 2  | 2           | shipped   | 89.90
-- 3  | 1           | pending   | 430.00
-- 4  | 3           | shipped   | 59.50
-- 5  | 4           | cancelled | 120.00

Two gates in one query — trace the pipeline

The second example above looks abstract until you trace it on real rows. WHERE fires first and removes Alan (hired 2020). GROUP BY then forms three groups from the four survivors. HAVING fires last and filters those groups. Every clause is a gate on a different stage of the pipeline.

SELECT department_id, AVG(salary) AS avg_sal
FROM employees
WHERE hire_date >= '2021-01-01'  -- OUT: Alan (2020)
GROUP BY department_id
HAVING AVG(salary) > 70000;
-- Groups formed: 1 → (145000+115000)/2 = 130000  ✓ kept
--                3 → 64000                      ✗ dropped
-- NULL → 78000                                  ✓ kept — Edsger rides NULL

When a WHERE + GROUP BY + HAVING query surprises you, annotate the pipeline stage by stage on sample data — the bug is almost always in which stage you thought was filtering.

HAVING without GROUP BY: the whole table is one group

With no GROUP BY, all rows form a single group, and HAVING filters that one giant group. It looks exotic but has a killer real use: data-quality checks that compare two counts of the same table — the exact COUNT(*) vs COUNT(col) mismatch from Topic 8, promoted from SELECT to a gate.

-- Fails loudly when any employee row lacks a department:
SELECT COUNT(*) AS total,
       COUNT(department_id) AS with_dept
FROM employees
HAVING COUNT(*) != COUNT(department_id);
-- Returns one row today (Edsger) — fix the data, and it returns nothing

A HAVING that compares two aggregates over the whole table is a schema constraint you can query — empty output means the data is clean.

Filter groups on aggregates you never selected

HAVING does not care what is in the SELECT list — it can use any aggregate, even one you never display. That lets a report show only 'affordable departments' while measuring their ceiling.

-- Which departments pay nobody above 100000?
SELECT department_id
FROM employees
GROUP BY department_id
HAVING MAX(salary) < 100000;
-- 2 (max 92000), 3 (max 64000), NULL (max 78000)
-- Dept 1 is excluded by Ada's 145000 — never selected, still measured

SELECT decides what you see; HAVING decides what survives. They are independent — most readers expect them to be the same list, and they are not.

Common Mistakes

Trying to use a SELECT alias in HAVING (most DBs don't allow it — use the aggregate expression again). Confusing WHERE and HAVING: WHERE filters individual rows, HAVING filters groups after aggregation.

Interview Tips

Execution order is key: FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Interviewers test this constantly. HAVING without GROUP BY applies to the entire table as one group.

Official References

Test Yourself — 6 questions

Self-check · 0/6 answered

1. What does HAVING filter that WHERE cannot?

2. Can you use an alias defined in SELECT inside HAVING?

3. HAVING without a GROUP BY clause…?

4. Can HAVING filter on an aggregate that is NOT in the SELECT list?

5. Why does SELECT * FROM employees WHERE AVG(salary) > 70000 fail?

6. What is the general performance rule for WHERE vs HAVING?

Practice

Return each department_id that has more than one employee, along with its headcount.

⚡ Solve it in the SQL playground →

Frequently Asked Questions

Why can't I put AVG(salary) in WHERE?

WHERE runs before GROUP BY, so no aggregate exists yet — the engine raises an error. Aggregation happens later, which is exactly why HAVING exists: it is the only clause that can see aggregate results.

Is HAVING slower than WHERE?

Usually, yes — HAVING evaluates after rows have been read and grouped, while WHERE discards rows before any of that work. A general tuning rule: push every possible condition up into WHERE, and reserve HAVING for conditions that genuinely need aggregation.