All Articles

GROUP BY vs HAVING vs WHERE: The Complete Guide

Fundamentals
#where-clause#having-clause#group-by#sql-basics#sql-interview

Why These Three Get Confused

WHERE, GROUP BY, and HAVING all filter or shape data, which is exactly why beginners mix them up — but each one operates at a different stage of query execution, and understanding that order is the key to using them correctly. This is also one of the most reliable "do they actually understand SQL, or just pattern-match" interview questions, because getting it wrong produces a query that runs without error but silently returns the wrong answer.

The Real Order of Execution

SQL is written in one order but executed in a different one. Understanding this logical execution order explains almost every "why doesn't this work" question involving these three clauses:

1. FROM      -- identify the source tables
2. JOIN      -- combine tables
3. WHERE     -- filter individual rows, BEFORE any grouping
4. GROUP BY  -- collapse rows into groups
5. HAVING    -- filter groups, AFTER aggregation
6. SELECT    -- compute the output columns
7. ORDER BY  -- sort the final result
8. LIMIT     -- restrict the row count

This is why you can't reference a SELECT-defined column alias in a WHERE clause on most databases — WHERE runs before SELECT even exists yet, logically speaking.

WHERE: Filters Rows Before Grouping

SELECT department, salary
FROM employees
WHERE salary > 50000;   -- removes individual rows before anything else happens

WHERE cannot reference aggregate functions (SUM, COUNT, AVG) because, at the point WHERE executes, no aggregation has happened yet — there's nothing to aggregate.

-- This fails: aggregates don't exist yet when WHERE runs
SELECT department, COUNT(*) FROM employees
WHERE COUNT(*) > 5   -- ERROR
GROUP BY department;

GROUP BY: Collapses Rows Into Groups

SELECT department, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

Every column in the SELECT list that isn't wrapped in an aggregate function must appear in GROUP BY — this is a strict rule on PostgreSQL and SQL Server (MySQL historically allowed exceptions with relaxed ONLY_FULL_GROUP_BY settings, but modern MySQL defaults to enforcing it too).

HAVING: Filters Groups After Aggregation

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 80000;   -- filters the already-computed group averages

HAVING is where aggregate conditions belong, specifically because it runs after GROUP BY has produced the aggregated values to filter on.

Using All Three Together

SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE hire_date > '2020-01-01'   -- 1. filter individual rows first (recent hires only)
GROUP BY department               -- 2. then group by department
HAVING AVG(salary) > 80000        -- 3. then filter groups by their average
ORDER BY avg_salary DESC;         -- 4. finally sort the result

The order these clauses are written in SQL (WHERE, then GROUP BY, then HAVING) actually matches their logical execution order — which is a helpful mnemonic, even though the full execution order also includes FROM/JOIN before WHERE and SELECT/ORDER BY after HAVING.

Why Filtering Early With WHERE Is Usually Faster

If a condition can be expressed as a row-level filter, put it in WHERE rather than HAVING, even if both would technically be legal for that particular case (e.g. filtering on the grouping column itself, not an aggregate).

-- Less efficient: filters AFTER grouping every row, including ones you'll discard
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING department = 'Engineering';

-- More efficient: filters BEFORE grouping, so fewer rows are ever aggregated
SELECT department, AVG(salary)
FROM employees
WHERE department = 'Engineering'
GROUP BY department;

Both return the same result here, but the second version does less total work — the database only aggregates the rows that matter, instead of aggregating everything and throwing most of it away afterward. HAVING should be reserved specifically for conditions that depend on the aggregated value itself.

Can You Use HAVING Without GROUP BY?

SELECT AVG(salary) FROM employees
HAVING AVG(salary) > 80000;

Yes — without an explicit GROUP BY, the entire table is treated as a single implicit group. It's legal SQL but unusual in practice; most engineers would write this as a subquery comparison instead, since a bare HAVING without GROUP BY reads ambiguously to anyone maintaining the code later.

Common Mistakes

  • Putting an aggregate condition in WHERE — causes a hard error, since aggregates don't exist at the point WHERE executes.
  • Putting a row-level condition in HAVING for performance reasons — technically legal in many cases but wasteful, since it forces the database to aggregate rows it's about to discard anyway.
  • Forgetting non-aggregated SELECT columns in GROUP BY — causes an error on PostgreSQL/SQL Server, and produces an arbitrary, non-deterministic value on older/relaxed MySQL configurations, which is worse than an error because it fails silently.
  • Referencing a SELECT alias inside WHERE — fails on most databases because WHERE executes before SELECT defines that alias; use the full expression again, or move the logic into a subquery/CTE.

Common Interview Questions

  1. What's the difference between WHERE and HAVING? WHERE filters individual rows before grouping; HAVING filters groups after aggregation.
  2. Why can't you use an aggregate function in WHERE? Because WHERE executes before GROUP BY, in the logical execution order — no aggregation has happened yet at that point.
  3. Write a query that filters both individual rows and aggregated groups in one statement. The combined example above — WHERE for the row-level filter, HAVING for the aggregate filter.
  4. Can HAVING reference a column that isn't in GROUP BY? Only if it's wrapped in an aggregate function — the same rule that applies to SELECT applies to HAVING, since both execute after grouping.

Frequently Asked Questions

Does the order WHERE/GROUP BY/HAVING is written in SQL match how it executes?

Yes, for these three specifically — WHERE is written first and executes first, GROUP BY second, HAVING third. The mismatch between written and execution order mainly shows up around SELECT (written before WHERE/GROUP BY/HAVING but executed after) and ORDER BY/LIMIT (written last, and also executed last).

Is there a performance difference between WHERE and HAVING when both would technically work?

Yes — WHERE reduces the row count before the (often expensive) grouping and aggregation step happens, while HAVING only filters after that work is already done. Always prefer WHERE for row-level conditions.

Practice This Pattern

Understanding execution order is one of the highest-leverage things to internalize early in SQL — it explains dozens of "why doesn't this work" errors before they happen. Try it hands-on with our SQL practice questions, or apply it to real aggregation problems in our case studies. For the aggregate patterns that pair naturally with HAVING, see our guide to finding duplicate rows.

Back to All Articles