SQL WHERE vs HAVING Clause Performance

Where each clause sits in execution order

SQL's logical execution order — covered in full in SQL Execution Order Explained — puts these two clauses on opposite sides of the aggregation step:

This ordering is the entire reason for the performance difference — WHERE gets first look at the data, before any grouping work has been done at all.

Why WHERE is (almost always) faster

• It can use an index. A WHERE condition on an indexed column lets the database skip reading rows that don't match entirely, often via an index seek instead of a full scan.
• It shrinks the row set before the expensive work. Grouping and aggregating fewer rows is always cheaper than grouping and aggregating all of them and discarding some afterward.
• HAVING can't use a row-level index at all. By the time HAVING runs, individual rows no longer exist as such — it's evaluating computed aggregate values per group, which have no index to seek against.

When HAVING is genuinely unavoidable

HAVING exists for exactly one job WHERE structurally cannot do: filtering on the result of an aggregate function.

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

Trying to write this with WHERE COUNT(*) > 10 fails outright — WHERE runs before GROUP BY has produced any counts to filter on, so the aggregate simply doesn't exist yet at that point in the query.

The classic mistake

The most common performance mistake with these two clauses: putting a plain, non-aggregate condition in HAVING just because it "feels like" it belongs with the grouping logic.

SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING department = 'Engineering';

SELECT department, COUNT(*)
FROM employees
WHERE department = 'Engineering'
GROUP BY department;

Both return the same result. The left version groups and counts every department first, then throws away every group except Engineering. The right version filters down to only Engineering rows before grouping ever starts — on a large employees table, that's the difference between aggregating the whole table and aggregating a single department's worth of rows.

Worked example with row counts

On a 2-million-row orders table, filtered down to a single high-volume customer's completed orders from the last year, then grouped by month:

The output is identical in both cases — this is purely a cost difference, not a correctness one, but on a large table it's often the difference between a query that returns instantly and one that visibly lags.

Using WHERE and HAVING together

The two clauses aren't competitors — most non-trivial aggregate queries use both, each doing the part the other can't:

SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE hire_date >= '2024-01-01'   -- row-level filter, before grouping
GROUP BY department
HAVING AVG(salary) > 80000;     -- group-level filter, after aggregation

Read it as: narrow the rows first with everything that doesn't need an aggregate (WHERE), then filter the resulting groups with whatever does (HAVING).

Common mistakes

• Using HAVING for a condition that doesn't reference an aggregate. The single most common and most fixable performance mistake with these two clauses.
• Assuming the optimizer will always rewrite it for you. Some do, for simple cases — but it's not guaranteed across every database and query shape, so writing it correctly in the first place is the reliable choice.
• Trying to reference an aggregate in WHERE. This is a hard error in every standard SQL dialect, not just a style issue — aggregates don't exist yet at the point WHERE runs.
• Forgetting that column aliases from SELECT aren't available in either clause in most databases, since both run before SELECT in logical order.