SQL Order of Execution: Why WHERE Can't See Your Alias

You write SELECT first, so it is natural to assume it happens first. It does not. The database evaluates a query in a different order from the one you type, and nearly every confusing SQL error a beginner meets, "column does not exist" for an alias you just defined, "misuse of aggregate" in a WHERE, comes from that gap. Every query below was run with Python's sqlite3 module against SQLite 3.37; the rules quoted come from the PostgreSQL and SQLite documentation listed at the end, as of September 2026.

The problem it solves

A query like "total kilos per apple variety, fresh apples only, varieties with at least 50 kg, heaviest first" uses six clauses. Written, they read SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY. The questions interviewers ask are all about what each clause can see:

• Why can ORDER BY total use the alias, but WHERE total > 50 cannot?
• Why must a condition on SUM(kilos) go in HAVING, not WHERE?
• Why can you not filter on ROW_NUMBER() directly?

The intuition

The logical order is: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, then LIMIT. The PostgreSQL documentation for SELECT lists the same steps, with DISTINCT right after the SELECT list is computed and LIMIT at the end.

Read that order as a pipeline, and each rule follows:

• FROM builds the rows, including joins. Nothing else can run before there is a source.
• WHERE filters single rows. Groups do not exist yet, so aggregates cannot appear here. The SELECT list has not been computed either, so its aliases do not exist.
• GROUP BY folds the surviving rows into one row per group and computes the aggregates.
• HAVING filters whole groups, so it is the first place SUM(kilos) has a value.
• SELECT computes the output columns and names them. Window functions run here, after WHERE, GROUP BY and HAVING, which is why the PostgreSQL tutorial says they are forbidden in those three clauses.
• ORDER BY sorts the finished rows, so it can use the aliases.

One caveat worth saying out loud: this is the logical order, which defines what the result means. The query planner is free to execute it differently, using an index to avoid a sort or applying a filter early, as long as the result is the same.

Watch it run

The animation keeps the clause strip in written order and moves the highlight in evaluated order. FROM reads five apple rows. WHERE rotten = 0 drops one, leaving four. GROUP BY variety makes three bins. HAVING SUM(kilos) >= 50 sets Kingston's 30 kg aside. Only then does SELECT compute SUM(kilos) AS total, and only then does the alias exist. ORDER BY total DESC lines up 100 then 55. The last two frames jump back to explain the errors: WHERE ran four clauses before total existed, and the sum does not exist before HAVING.

The code

The lesson's query, then the three errors, each run for real. SQLite's messages are shown; PostgreSQL rejects the same queries with its own wording:

import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE apple (variety TEXT, kilos REAL, rotten INT);
INSERT INTO apple VALUES ('Dabinett', 60, 0), ('Dabinett', 40, 0),
  ('Kingston', 30, 0), ('Kingston', 90, 1), ('Yarlington', 55, 0);
""")

query = """
SELECT variety, SUM(kilos) AS total   -- 5th
FROM apple                            -- 1st
WHERE rotten = 0                      -- 2nd
GROUP BY variety                      -- 3rd
HAVING SUM(kilos) >= 50               -- 4th
ORDER BY total DESC                   -- 6th
"""
print(db.execute(query).fetchall())   # [('Dabinett', 100.0), ('Yarlington', 55.0)]

def attempt(sql):
    try:
        return db.execute(sql).fetchall()
    except sqlite3.OperationalError as e:
        return "error: " + str(e)

# An aggregate in WHERE: the groups do not exist yet
print(attempt("SELECT variety FROM apple WHERE SUM(kilos) >= 50 GROUP BY variety"))
# error: misuse of aggregate: SUM()

# A window function in WHERE: windows are computed after WHERE, GROUP BY and HAVING
print(attempt("""SELECT variety, kilos FROM apple
                 WHERE ROW_NUMBER() OVER (ORDER BY kilos DESC) = 1"""))
# error: misuse of window function ROW_NUMBER()

# The fix for both: finish one query, then filter its output in an outer one
print(db.execute("""
WITH ranked AS (
  SELECT variety, kilos, ROW_NUMBER() OVER (ORDER BY kilos DESC) AS rn
  FROM apple WHERE rotten = 0
)
SELECT variety, kilos FROM ranked WHERE rn = 1
""").fetchall())                       # [('Dabinett', 60.0)]

# SQLite is lenient: it resolves an output alias in WHERE. PostgreSQL does not.
print(attempt("SELECT variety, kilos * 2 AS doubled FROM apple WHERE doubled > 150"))
# [('Kingston', 180.0)]

The last query is a trap in the other direction: it works in SQLite, so a habit formed there fails elsewhere. The PostgreSQL SELECT page is explicit that an output column's name can be used in ORDER BY and GROUP BY but not in WHERE or HAVING. Write the expression out and the query is portable.

To check the pipeline reading against a real engine, here it is in plain Python, one clause per line, compared with SQLite on 300 random tables with random thresholds and limits:

import random
from collections import defaultdict

def pipeline(rows, min_kilos, min_total, limit):
    """The logical order, one clause at a time, in plain Python."""
    rows = list(rows)                                            # 1 FROM
    rows = [r for r in rows if r[1] >= min_kilos]                # 2 WHERE
    groups = defaultdict(list)                                   # 3 GROUP BY
    for variety, kilos in rows:
        groups[variety].append(kilos)
    groups = {v: ks for v, ks in groups.items() if sum(ks) >= min_total}   # 4 HAVING
    out = [(v, sum(ks), len(ks)) for v, ks in groups.items()]    # 5 SELECT
    out.sort(key=lambda t: (-t[1], t[0]))                        # 6 ORDER BY
    return out[:limit]                                           # 7 LIMIT

random.seed(16)
ok = True
for _ in range(300):
    rows = [(random.choice("ABCDE"), random.randint(1, 50)) for _ in range(random.randint(0, 30))]
    t = sqlite3.connect(":memory:")
    t.execute("CREATE TABLE r (variety TEXT, kilos INT)")
    t.executemany("INSERT INTO r VALUES (?, ?)", rows)
    a, b, k = random.randint(0, 40), random.randint(0, 120), random.randint(1, 5)
    got = t.execute("""SELECT variety, SUM(kilos) AS total, COUNT(*) FROM r
                       WHERE kilos >= ? GROUP BY variety HAVING SUM(kilos) >= ?
                       ORDER BY total DESC, variety LIMIT ?""", (a, b, k)).fetchall()
    ok &= got == pipeline(rows, a, b, k)
print(ok)                              # True

The complexity

The order also explains where the work goes:

• WHERE before GROUP BY means every row dropped early is a row that is never grouped. A filter that does not need an aggregate belongs in WHERE, not HAVING, even when both would give the same answer.
• GROUP BY is a hash or a sort over the surviving rows, roughly O(n) expected or O(n log n).
• ORDER BY is a sort of the output, O(m log m) for m result rows, unless an index already delivers the order.
• LIMIT comes last logically, but an engine can often stop early, for example with a top-k sort.

Where it goes wrong

• Aliases in WHERE. Repeat the expression, or compute it in a subquery or CTE and filter outside.
• Aggregates in WHERE. Move the condition to HAVING.
• Row filters in HAVING. HAVING variety <> 'Kingston' works in many engines but filters after grouping; WHERE does it before, on fewer rows.
• Filtering on a window function. Wrap the query and filter the outer one, as the ranked CTE does.
• Trusting one engine's leniency. SQLite accepts queries PostgreSQL rejects, such as the alias in WHERE above. Test on the database you will ship on.

When it shows up in interviews

It shows up as a warm-up in data and backend interviews, usually as "what is the order of execution of a SQL query?" or as a broken query to fix: an alias in WHERE, or COUNT(*) > 1 outside HAVING. It also comes up in the "top N per group" question, where the answer is a window function in a subquery precisely because of the ordering. In real work, knowing the order is what lets you read an error message and know which clause to move the condition to.

How to say it in an interview

"SQL is written with SELECT first but evaluated FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT. WHERE filters rows before groups or output columns exist, so it cannot use an aggregate or a SELECT alias; HAVING filters groups after aggregation; ORDER BY runs last, so it can use aliases. Window functions are computed with the SELECT list, so to filter on one I wrap it in a subquery or CTE. That is the logical order; the planner may execute it differently as long as the result is the same."

Grouping and HAVING get their own article in SQL GROUP BY and HAVING explained, and ranking inside groups is covered in window functions.

Sources

• SELECT — PostgreSQL Documentation
• Window Functions — PostgreSQL Documentation, tutorial
• SELECT — SQLite Documentation