The SELECT mental model: logical processing order

The order you write is not the order it runs

A SELECT statement is written in one order and evaluated in a completely different one. The written order is fixed by the grammar — SELECT always comes first. But the logical processing order (the order the SQL standard says the clauses are conceptually evaluated) puts SELECT near the end:

1. FROM (and JOINs) — build the working set of rows from the source tables.
2. WHERE — keep only rows where the predicate is TRUE (this is selection).
3. GROUP BY — collapse the surviving rows into groups.
4. HAVING — keep only groups where the predicate is TRUE.
5. SELECT — evaluate the output expressions and assign aliases (this is projection).
6. DISTINCT — drop duplicate output rows.
7. ORDER BY — sort the result.
8. LIMIT / OFFSET — slice the sorted result.

Together, these eight steps form the logical pipeline: steps 1–4 decide which rows and groups survive, step 5 decides what each output row looks like, and steps 6–8 shape the final presentation. Without internalizing this order, alias-scope errors and performance surprises look like Postgres being arbitrary — they’re not.

This is a logical model: the executor doesn’t literally materialize a full set after each step (it streams and the planner reorders for speed — that’s the previous lesson’s pipeline and the databases track’s execution-plans chapter). But the name-resolution rules you can observe follow this order precisely, and that’s what bites people.

Projection vs selection: two different jobs

Two words from relational algebra clear up most confusion:

• Selection = choosing rows. That’s WHERE (and HAVING for groups). It answers “which rows survive?”
• Projection = choosing and computing columns. That’s SELECT. It answers “what does each output row look like?”

They’re separate stages. WHERE runs in step 2 on the raw table columns; SELECT runs in step 5 and is the only place where output column aliases come into existence.

-- orders(id, user_id, price, qty, status, created_at)
SELECT
  user_id,
  price * qty AS revenue          -- 'revenue' is born HERE, in step 5
FROM orders                       -- step 1
WHERE status = 'paid'             -- step 2: 'revenue' does not exist yet
ORDER BY revenue DESC             -- step 7: 'revenue' already exists
LIMIT 20;                         -- step 8

Why the alias works in ORDER BY but not WHERE

Now the opening contradiction dissolves. WHERE is step 2; the SELECT alias revenue isn’t created until step 5. So referencing it in WHERE is a time-travel error — you’re naming something that doesn’t exist yet. Postgres rejects it with column "revenue" does not exist.

ORDER BY is step 7 — after SELECT. By then the alias exists, so ORDER BY revenue resolves cleanly. ORDER BY is the one clause that can see SELECT output names, precisely because it runs after projection.

The fix for the WHERE case is to repeat the expression (the engine evaluates it once anyway under a good plan), or wrap the query in a subquery / CTE so the alias materializes in an inner step:

-- Option A: repeat the expression in WHERE (it refers to base columns, always legal)
SELECT user_id, price * qty AS revenue
FROM orders
WHERE status = 'paid' AND price * qty > 100;

-- Option B: name it once in an inner query, filter in the outer (alias now visible)
SELECT * FROM (
  SELECT user_id, price * qty AS revenue
  FROM orders WHERE status = 'paid'
) s
WHERE s.revenue > 100;

Column vs expression output, and one production trap

Every item in the SELECT list is either a bare column (user_id) or a computed expression (price * qty, lower(email), count(*)). A bare column with no alias keeps its name; an expression with no alias gets an ugly auto-name like ?column?. Always alias expressions — downstream code, BI tools, and ORDER BY all key off that name.

The production trap that follows directly from the processing order: putting heavy logic in SELECT doesn’t reduce the rows scanned. WHERE ran first. If your SELECT calls an expensive function per row but the WHERE is non-selective, you still pay that function for every surviving row. Filtering is the lever for how many rows; projection only shapes what each row shows. A team once moved a slow regex into SELECT thinking it would “run less” — it ran on all 4 million surviving rows exactly as before, because the WHERE hadn’t changed. Selectivity lives in WHERE, not SELECT.