SQL Order of Execution

A query is written in one order and processed in another. You write SELECT first, but the database starts with FROM, filters with WHERE, groups, filters the groups, and only then works out the SELECT list, sorts it, and cuts it to size. Each clause sees only what the clauses before it have produced. That one rule explains why WHERE can't filter on a count(*), why ORDER BY can use a column alias, and why LIMIT without ORDER BY returns arbitrary rows.

The order

This is the logical order: the order the answer is defined in. The database is free to do the work in a different physical order, as long as the answer comes out the same (see below).

One query, step by step

Which states have the most shipped orders in the Garden Shop data? This query uses every main clause:

select c.state, count(*) as shipped_orders
from orders as o
join customers as c on c.customer_id = o.customer_id
where o.status = 'shipped'
group by c.state
having count(*) >= 2
order by shipped_orders desc, c.state
limit 3

Here is what each step hands to the next:

The tie-breaker matters. Three states tie on 2 and only one fits under limit 3, so without c.state in the ORDER BY the third row could be any of them.

What the order explains

WHERE can't see aggregates

WHERE runs at step 2, before any groups exist, so there is no count(*) for it to test. DuckDB says WHERE clause cannot contain aggregates! Filter groups with HAVING, which runs after grouping.

select state, count(*) as customers
from customers
where count(*) >= 3
group by state

select state, count(*) as customers
from customers
group by state
having count(*) >= 3
order by state

That returns CA, OH, and PA. More on this error: aggregate functions are not allowed in WHERE.

WHERE and HAVING can't see window functions

Window functions are computed at step 5, after HAVING, so neither filter can use one. Filter on the result with QUALIFY, which runs right after the window functions, or wrap the query in a CTE and filter outside it.

select product_name, price,
       row_number() over (order by price desc, product_id) as price_rank
from products
qualify price_rank <= 3

See window functions are not allowed in WHERE for the CTE version, which runs on engines without QUALIFY.

Window functions only see rows WHERE kept

Because WHERE runs first, a window over "all rows" means all rows that survived the filter. Garden Shop has 24 products, but this window counts 5:

select product_name, price,
       count(*) over () as products_counted
from products
where price > 20
order by price desc

To compare each row with the whole table, compute the window in a CTE first and filter afterwards.

ORDER BY can use aliases and unselected columns

ORDER BY runs after SELECT, so every engine lets it sort by an alias such as shipped_orders. It can also sort by a column the SELECT list leaves out, because the underlying row is still there:

select product_name
from products
order by price desc, product_id
limit 3

LIMIT comes last

LIMIT keeps the first rows of the sorted result. With no ORDER BY there is no "first", and the engine returns whichever rows it reaches first. See LIMIT without ORDER BY gives unpredictable rows.

Aliases in WHERE: DuckDB bends the rule

By the order above, a SELECT alias doesn't exist yet when WHERE runs. PostgreSQL, MySQL, and SQL Server enforce that. DuckDB, which runs this site's queries, and SQLite look the alias up for you, so this runs here:

select product_name, price - cost as margin
from products
where margin > 10
order by margin desc, product_name

The convenience stops where the order would break. An alias for an aggregate (count(*) as customers) or a window function still can't appear in WHERE, and DuckDB raises the same errors as above. Repeat the expression or use a CTE when the query has to run elsewhere; the column not found error page shows both fixes.

Logical order, not physical order

The logical order defines the answer, not the work. Ask DuckDB for the plan of the shipped-orders query and the filter on status isn't a step after the join: it sits inside the scan of orders, as Filters: status='shipped', so only shipped orders ever reach the join. The result is the same; the join just has fewer rows to match.

explain
select c.state, count(*)
from orders as o
join customers as c on c.customer_id = o.customer_id
where o.status = 'shipped'
group by c.state

So use the logical order to reason about what a query means, and EXPLAIN to see what the engine does to answer it.

What can go wrong

• An alias that reuses a column name. In select price * 0 as price ... where price > 20, DuckDB's WHERE reads the real price column and keeps the 5 products priced over 20, while ORDER BY price sorts by the alias. The same word means two things in one query. Give a calculated column a new name.
• A WHERE filter on the right side of a LEFT JOIN. WHERE runs after the join has added its NULL rows, so a condition on the right table throws them away again. Put that condition in ON. See why a LEFT JOIN turns into an INNER JOIN.
• DISTINCT with ORDER BY on a column you didn't select. select distinct category_id from products order by price runs in DuckDB, but each category has products at several prices, so the order has no clear meaning. PostgreSQL rejects it: for SELECT DISTINCT, ORDER BY expressions must appear in select list. Sort by an aggregate instead, such as group by category_id order by min(price).
• Code that works here and fails in production. An alias in WHERE, or QUALIFY, is fine in DuckDB and an error in PostgreSQL. Check the table below before you move a query.

Other databases

Every engine uses the same logical order. They differ in how far they let a SELECT alias reach back into earlier clauses, and in whether QUALIFY exists.

All five accept an alias in ORDER BY. Snowflake, BigQuery, and Databricks also support QUALIFY. The portable habit is the same everywhere: filter on the full expression, or name it in a CTE and filter outside.