Skip to content
BytePatterns

WHERE vs HAVING: Filter Rows Before Grouping or Groups After

6 min readBytePatterns

WHERE vs HAVING: WHERE drops rows before GROUP BY and cannot see aggregates; HAVING drops whole groups after. The same test in each gives different counts.

WHERE vs HAVING in one line: WHERE tests individual rows before they are grouped, so it decides which rows go into each aggregate and cannot mention one; HAVING tests whole groups after GROUP BY and the aggregates have been computed, so it can filter on SUM, COUNT or AVG. PostgreSQL's tutorial puts it as WHERE selecting input rows and HAVING selecting group rows. Below, both are run live in SQLite from Python; PostgreSQL and MySQL rules are quoted from their manuals, as of October 2026.

WHEREHAVING
Filtersrows, before groupinggroups, after aggregation
Can use aggregatesno: misuse of aggregateyes, even ones not in SELECT
Changes the aggregate valuesyes, fewer rows go inno, it keeps or drops a group whole

The problem it solves

"Pickers who brought in at least 10 kilos" is two different questions. Total over the week, at least 10? That is a test on a group. Count only the baskets of at least 10 kilos? That is a test on rows. The SQL differs by one keyword and a few words, and both run without error, so writing the wrong one silently returns the wrong report.

The intuition

Follow the evaluation order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY.

  • WHERE runs on raw rows. Each row passes or fails on its own values. No group exists yet, so there is no sum to test, which is why an aggregate in WHERE is an error. Whatever it drops never reaches the aggregate.
  • HAVING runs on finished groups. By then every group has its SUM and COUNT. The test keeps or removes the group as a unit; it cannot remove one row from inside a group.
  • The same condition can mean two things. kilos >= 10 in WHERE removes small baskets and then sums what remains. SUM(kilos) >= 10 in HAVING sums everything and then removes small totals. Different counts, different sums, sometimes different pickers.
  • A test on the grouping column means the same in both places, because every row in a group shares that value. It still belongs in WHERE: PostgreSQL's tutorial calls it more efficient, since rows that fail never get grouped, and MySQL's manual says HAVING is applied nearly last, with no optimization, and tells you not to use it for items that belong in WHERE.

Two edge rules. A HAVING with no GROUP BY treats the whole filtered table as one group: the result is one row or no rows. And engines differ on aliases: MySQL lets HAVING refer to SELECT list columns as an extension to the standard, while PostgreSQL makes you repeat the expression.

When to use which

  • The condition uses only column values of a single row: WHERE.
  • The condition uses an aggregate (SUM, COUNT, AVG, MIN, MAX): HAVING.
  • The condition is on a GROUP BY column: WHERE. It gives the same answer and filters before the grouping work.
  • Both kinds at once: both clauses in one query, the row tests in WHERE and the group tests in HAVING.

Watch it run

The animation is the lesson's tea estate. Five weighings sit in the pluck table. GROUP BY picker folds them into three piles, SUM(kilos) turns Meena's 18.0 and 22.0 into 40.0, and Ravi's two small baskets become 17.5. Then HAVING SUM(kilos) >= 20 tests the piles and drops Ravi's whole group, both rows with it, leaving two output lines. The last frame puts the two filters side by side: WHERE on the rows before, HAVING on the groups after. Watch where the drop happens, because that position is the whole difference.

Aggregations & GROUP BY

Step 1 of 10

Five weighings at the shed, one line per picker per day. Payroll never reads them singly.

The same interactive animation as the lesson — step through it with the controls.

The code

The lesson's table with one more basket for Suri. The same threshold, 10 kilos, applied to rows and then to groups:

import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
  CREATE TABLE pluck (picker TEXT, day TEXT, kilos REAL);
  INSERT INTO pluck VALUES ('Meena','Mon',18.0), ('Meena','Tue',22.0),
                           ('Ravi','Mon',9.5),   ('Ravi','Tue',8.0),
                           ('Suri','Mon',30.0),  ('Suri','Tue',6.0);
""")
q = lambda sql: db.execute(sql).fetchall()

# The same test, kilos >= 10, before and after grouping
print(q("""SELECT picker, COUNT(*), SUM(kilos) FROM pluck
           WHERE kilos >= 10 GROUP BY picker"""))
# [('Meena', 2, 40.0), ('Suri', 1, 30.0)]
print(q("""SELECT picker, COUNT(*), SUM(kilos) FROM pluck
           GROUP BY picker HAVING SUM(kilos) >= 10"""))
# [('Meena', 2, 40.0), ('Ravi', 2, 17.5), ('Suri', 2, 36.0)]

# A test on the grouping column gives the same answer in either place
w = q("SELECT picker, SUM(kilos) FROM pluck WHERE picker <> 'Ravi' GROUP BY picker")
h = q("SELECT picker, SUM(kilos) FROM pluck GROUP BY picker HAVING picker <> 'Ravi'")
print(w == h, w)                        # True [('Meena', 40.0), ('Suri', 36.0)]

# HAVING may test an aggregate that is not in the SELECT list; WHERE may not test one at all
print(q("SELECT picker FROM pluck GROUP BY picker HAVING MIN(kilos) < 7"))   # [('Suri',)]
try:
    q("SELECT picker FROM pluck WHERE kilos > AVG(kilos)")
except sqlite3.OperationalError as e:
    print(e)                            # misuse of aggregate function AVG()

# HAVING without GROUP BY: the whole table is one group (SQLite 3.39.0 or later)
print(sqlite3.sqlite_version)           # 3.37.2
try:
    print(q("SELECT COUNT(*), SUM(kilos) FROM pluck HAVING SUM(kilos) > 50"))
except sqlite3.OperationalError as e:
    print(e)                            # a GROUP BY clause is required before HAVING
print(q("SELECT * FROM (SELECT COUNT(*) AS n, SUM(kilos) AS s FROM pluck) WHERE s > 50"))
# [(6, 93.5)]

In WHERE, the threshold removed three baskets: Ravi disappeared, and Suri's count fell to 1 and her sum to 30.0. In HAVING, nothing was removed from inside a group and all three pickers passed. The grouping-column test agreed in both places. The SQLite bundled with this Python build is 3.37.2, which still rejects HAVING without GROUP BY; SQLite 3.39.0 added it, and on a newer build that line prints [(6, 93.5)], the same as the portable subquery.

Now filtering early, at size: 300,000 weighings for 500 pickers, an index on picker, and the same filter written three ways:

import random
import time

random.seed(5)
names = [f"P{i:03}" for i in range(500)]
db.executemany("INSERT INTO pluck VALUES (?, ?, ?)",
               ((random.choice(names), random.choice(["Mon", "Tue"]), random.randint(1, 30))
                for _ in range(300000)))
db.execute("CREATE INDEX pluck_picker ON pluck(picker)")

base = "SELECT picker, SUM(kilos) FROM pluck "
queries = [base + "WHERE picker = 'P042' GROUP BY picker",
           base + "GROUP BY picker HAVING picker = 'P042'",
           base + "GROUP BY picker HAVING MAX(picker) = 'P042'"]
for sql in queries:
    print(db.execute("EXPLAIN QUERY PLAN " + sql).fetchone()[3])
    start = time.perf_counter()
    print(q(sql), round(time.perf_counter() - start, 4))
# SEARCH pluck USING INDEX pluck_picker (picker=?)
# [('P042', 9133.0)] 0.0004
# SEARCH pluck USING INDEX pluck_picker (picker=?)
# [('P042', 9133.0)] 0.0002
# SCAN pluck USING INDEX pluck_picker
# [('P042', 9133.0)] 0.0823
# on one machine, roughly; the plans and rows are exact, the seconds are not

SQLite's planner noticed that HAVING picker = 'P042' does not depend on any aggregate and moved it into WHERE: both plans are an index SEARCH. Hide the same test inside an aggregate and the planner cannot move it, so it groups all 300,000 rows and throws away 499 groups, roughly two hundred times slower here. MySQL's manual describes HAVING as applied nearly last, with no optimization, so do not count on that rewrite there.

The complexity

  • WHERE: evaluated once per input row, O(n), or less when an index lets the engine skip rows altogether.
  • GROUP BY and aggregates: O(rows that passed WHERE) with hashing, or a sort; every row WHERE dropped is grouping work saved.
  • HAVING: evaluated once per group, O(g), after all the grouping work has been paid.

Where it goes wrong

  • Aggregate in WHERE. WHERE SUM(kilos) > 20 is an error; move it to HAVING.
  • A row condition in HAVING by habit. Correct only on grouping columns, and slower wherever the planner does not move it.
  • Expecting WHERE and HAVING to agree. WHERE kilos >= 10 and HAVING SUM(kilos) >= 10 answer different questions and return different counts.
  • A bare column in HAVING. SQLite accepts HAVING kilos > 7 with no aggregate and tests some row of the group; PostgreSQL rejects it. Wrap it in MIN, MAX or SUM, whichever you mean.
  • NULL in either clause. A comparison with NULL is unknown, so the row or group is dropped.

When it shows up in interviews

As the direct question, as a "fix this query" with an aggregate in WHERE, or as a reporting task needing both clauses. The full grouping mechanics, COUNT(*) against COUNT(col) and bare columns, are in SQL GROUP BY and HAVING explained, and why a NULL comparison drops rows in both clauses is in SQL NULL in WHERE. The short version with the collapsing animation is the Aggregations and GROUP BY lesson.

How to say it in an interview

"WHERE filters rows before grouping, so it controls what goes into each aggregate and cannot use one. HAVING filters groups after aggregation, so it is the only place to test SUM or COUNT, and it keeps or drops a group whole. The same threshold in each gives different results: in WHERE it shrinks the sums, in HAVING it removes whole groups. If a condition does not need an aggregate, I put it in WHERE, because filtering early saves the grouping work and can use an index; some planners move it for you, but MySQL documents HAVING as applied with no optimization."

Frequently asked questions

What is the difference between WHERE and HAVING in SQL?

WHERE filters individual rows before GROUP BY, so it decides which rows the aggregates see and cannot contain an aggregate. HAVING filters groups after aggregation, so it can test SUM, COUNT or AVG, and it keeps or removes each group as a whole.

Can you use HAVING without GROUP BY?

Yes, in PostgreSQL and MySQL: the whole result of WHERE is treated as one group, so the query returns one row or none. SQLite allows it from version 3.39.0; older SQLite builds raise an error asking for a GROUP BY clause.

Is WHERE faster than HAVING?

For conditions that do not need an aggregate, usually yes. WHERE removes rows before grouping and can use an index, so less data is grouped. Some planners move such HAVING terms into WHERE themselves, but MySQL applies HAVING nearly last without optimization, so write it in WHERE.

Sources