FresherTechnicalSQL

In what order does a SELECT query logically execute?

Try answering first

Model answer

SQL is written in one order but evaluated in another. The logical order is:

FROM and JOIN: build the working rows.
WHERE: filter rows.
GROUP BY: form groups.
Aggregates, then HAVING: filter groups.
Window functions.
SELECT: compute expressions and aliases.
DISTINCT.
ORDER BY.
LIMIT / OFFSET.

SELECT price * qty AS line_total
FROM order_items
WHERE line_total > 100;   -- error: alias does not exist yet

The alias is created in step 6, but WHERE runs at step 2. Repeat the expression in WHERE, or wrap the query in a subquery or CTE. ORDER BY runs later, so it can use the alias. Some engines, such as MySQL, are more lenient in GROUP BY and HAVING.

The same reason explains why you cannot filter on a window function in WHERE: use a subquery.

This is a logical model. The optimizer may physically reorder steps, such as filtering early, as long as the result is the same.

Knowing this order explains most "why does this alias fail" errors.

Key points

FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT
Aliases are defined in SELECT, after WHERE
ORDER BY can use aliases, WHERE cannot
The optimizer may reorder physically

Likely follow-ups

Why can you not filter on ROW_NUMBER() directly in WHERE?

Common mistakes

Using a SELECT alias in WHERE
Thinking the written order is the execution order