The SELECT pipeline: order of operations

Why the order matters

The most common SQL bug after NULL handling is "I wrote my SELECT in the wrong mental order". Beginners read SQL top-to-bottom (SELECT ... FROM ... WHERE ...) and assume that's the execution order. It isn't. Knowing the logical order — and the gap between it and the typed order — is the difference between guessing and reasoning about every query you'll ever write.

Logical execution order

  FROM      pick rows from the source(s)
    ↓
  WHERE     filter rows BEFORE aggregation       ◄── SELECT aliases NOT visible
    ↓
  GROUP BY  collapse rows into groups
    ↓
  HAVING    filter groups AFTER aggregation
    ↓
  SELECT    evaluate expressions, name aliases   ──▶ aliases born here
    ↓
  ORDER BY  sort the result                       ◄── aliases ARE visible
    ↓
  LIMIT     take a slice (server-side, not client-side)

This is the logical order — what the query means. The optimizer can execute it differently (push filters down, reorder joins) as long as the result is identical; Module 6 covers that.

The arrows on WHERE and ORDER BY are the alias-visibility rule. WHERE runs before SELECT, so an alias defined in SELECT doesn't exist yet — Postgres errors with "column 'net' does not exist". ORDER BY runs after SELECT, so it can name the alias. Most SQL bugs come from reading the query in typed order instead of this logical order.

-- Error: column "net" does not exist
SELECT total_cents * 0.9 AS net FROM orders WHERE net > 1000;

-- Works — ORDER BY runs after SELECT
SELECT total_cents * 0.9 AS net FROM orders ORDER BY net;

WHERE vs HAVING — finally

WHERE filters rows. HAVING filters groups.

• WHERE total_cents > 1000 works on every order, before aggregation.
• HAVING SUM(total_cents) > 1000 works on the aggregated total per group, after aggregation.

If you WHERE SUM(total_cents) > 1000, Postgres errors with "aggregate functions are not allowed in WHERE". That error message is the database telling you the order of operations: aggregation hasn't happened yet.