SQL’s logical query order

You write a query starting with SELECT, but the database doesn't read it in that order. Each clause works on the output of the one before it, in a fixed logical order:

1. FROM / JOIN    build the rows: tables, joined
2. WHERE          keep rows that match
3. GROUP BY       collapse rows into groups
4. HAVING         keep groups that match
5. SELECT         compute output columns (and aliases)
6. DISTINCT       remove duplicate output rows
7. ORDER BY       sort
8. LIMIT/OFFSET   cut

This is the order the result is defined in. The planner is free to execute it differently, for example by using an index so it never reads the rows WHERE would throw away. But it must return what this order would produce.

#What the order explains

Most "why is this an error?" moments in SQL come from this list.

• An alias from SELECT isn't visible in WHERE. WHERE runs at step 2, and the alias doesn't exist until step 5. Repeat the expression, or compute it in a subquery or CTE.
• ORDER BY can use the alias. It runs after SELECT, so ORDER BY total DESC works.
• You can't use an aggregate in WHERE. No groups exist yet at step 2. To filter on count(*), use HAVING.
• Every non-aggregated column in SELECT must be grouped. After step 3 there's one row per group, so a column that differs within a group has no single value.
• Window functions can't be filtered in WHERE. They're computed alongside SELECT. Wrap the query and filter outside.

-- Customers with more than 5 orders, biggest spenders first
SELECT customer_id, count(*) AS orders, sum(total) AS spent
FROM orders
WHERE status = 'paid'          -- row filter: step 2
GROUP BY customer_id
HAVING count(*) > 5            -- group filter: step 4 (alias not usable yet)
ORDER BY spent DESC;           -- alias usable: step 7

#WHERE or HAVING?

Put a condition in WHERE if it's about individual rows, and in HAVING only if it's about the group. Filtering early means fewer rows to group, so it's usually faster. It's also clearer about what you meant.

When a filter on an outer join behaves strangely, the cause is usually the order plus NULL semantics. A WHERE on the right-hand table runs after the join, so it throws away the NULL-padded rows the LEFT JOIN kept. Put that condition in the ON clause instead.