HAVING, and WHERE vs HAVING

Concept

WHERE and HAVING both remove things, but at different stages of the pipeline and from different units:

Syntax

SELECT   grouping_column, aggregate_function(column)
FROM     table_name
WHERE    row_level_condition
GROUP BY grouping_column
HAVING   group_level_condition
ORDER BY column;

Example

-- Departments with more than one active employee and an average salary above 80000
SELECT dept_id,
       COUNT(*)              AS headcount,
       ROUND(AVG(salary), 2) AS avg_salary
FROM   employees
WHERE  status = 'active'          -- row filter: drop inactive people first
GROUP BY dept_id
HAVING COUNT(*) > 1                -- group filter: drop small departments
   AND AVG(salary) > 80000         -- group filter: drop low paying ones
ORDER BY avg_salary DESC;

Explanation

Both clauses are doing work that only they can do. status = 'active' is a property of a single employee, so it belongs in WHERE: putting it in HAVING would be impossible, because by then individual employees no longer exist. COUNT(*) > 1 is a property of a department, which does not exist until the grouping has happened, so it can only be in HAVING.

The performance rule

-- Inefficient: every row is grouped, then most groups are thrown away
SELECT dept_id, COUNT(*)
FROM   employees
GROUP BY dept_id
HAVING dept_id IN (10, 20);

-- Efficient: irrelevant rows never enter the grouping at all
SELECT dept_id, COUNT(*)
FROM   employees
WHERE  dept_id IN (10, 20)
GROUP BY dept_id;

Both return the same answer. The second does less work, and on a large table the difference is large. Modern optimisers often push such a predicate down automatically, but writing it in the right place makes the intent obvious and does not depend on the optimiser being clever.

Important rules

• A condition that mentions an aggregate can only go in HAVING.
• A condition on a plain column should go in WHERE, always.
• HAVING without GROUP BY is legal: the whole result is treated as one group.
• HAVING is evaluated before SELECT in the standard, so aliases are not portable there. Repeat the aggregate expression instead.

Common mistakes

• Putting a row level condition in HAVING "because it comes after GROUP BY in the query". It works, and it is slower and less clear.
• Writing WHERE COUNT(*) > 1, which cannot work - the count does not exist yet.
• Relying on HAVING avg_salary > 80000 with a select list alias, then porting the query to PostgreSQL where it fails.
• Forgetting that HAVING filters groups, so a department can disappear entirely rather than showing a zero.

Best practices

• Filter rows in WHERE, filter groups in HAVING. Use both in the same query when both apply.
• Repeat the aggregate expression in HAVING rather than referencing an alias, for portability.
• When the result should include groups that fail the test but show as zero, use conditional aggregation instead of HAVING.

Practice

1. Find customers who have placed more than one order.
2. Return departments whose total active payroll exceeds 200000, excluding inactive staff.
3. Rewrite ... GROUP BY status HAVING status <> 'cancelled' so the filter happens in the right place.