Query Execution Order

The two errors this explains

"Column does not exist" on an alias in WHERE:

SELECT price * quantity AS line_total
FROM   order_lines
WHERE  line_total > 100;          -- error

WHERE runs at step 2 and SELECT at step 5, so line_total does not exist yet. Repeat the expression, or wrap the query:

SELECT * FROM (
  SELECT price * quantity AS line_total FROM order_lines
) t WHERE line_total > 100;

"Aggregate not allowed in WHERE":

SELECT   customer_id, SUM(total)
FROM     orders
WHERE    SUM(total) > 1000        -- error
GROUP BY customer_id;

The sum is produced at step 3; WHERE runs at step 2. HAVING is the clause that runs after grouping, and is therefore the one that can see it.

The mirror image is why ORDER BY can use an alias: it runs at step 6, after SELECT has created it.

WHERE and HAVING are not interchangeable

Both filter, at different stages, and the distinction matters for correctness as well as speed:

• WHERE filters individual rows, before grouping. Excluded rows never reach the aggregate.
• HAVING filters groups, after aggregation. All rows contribute to the aggregate; whole groups are then discarded.

When a condition could go in either — a plain column filter — put it in WHERE. Filtering earlier means fewer rows to group, which is almost always faster.

Written order versus the order things happen

SQL is written in an order designed to read like English and executed in a different one. Almost every confusing error message in SQL comes from that mismatch.

So the real sequence is: assemble the rows, filter them, group them, filter the groups, work out what to display, sort, then cut.

(Strictly, this is the logical order. The planner may physically do things in another order entirely — pushing a filter down into an index scan, for instance — as long as the result is what this sequence would produce.)

Following one query through all seven steps

SELECT   c.country, COUNT(*) AS orders, SUM(o.total) AS revenue
FROM     orders o
JOIN     customers c ON c.id = o.customer_id
WHERE    o.status = 'complete'
GROUP BY c.country
HAVING   SUM(o.total) > 5000
ORDER BY revenue DESC
LIMIT    5;

1. FROM/JOIN — build the combined set of order-and-customer rows. If 50,000 orders match 8,000 customers, this is where the row count is decided.
2. WHERE — drop everything not complete. Say 30,000 rows survive.
3. GROUP BY — collapse those into one row per country. Perhaps 40 rows now.
4. HAVING — drop countries under 5,000 in revenue. Perhaps 12 rows.
5. SELECT — compute and name the output columns, including the aliases orders and revenue.
6. ORDER BY — sort those 12 rows by revenue, using the alias created one step earlier.
7. LIMIT — keep the top 5.

Reading it this way answers most "why is this slow?" questions too: the expensive steps are the ones operating on the largest row counts, which are almost always the join and the filter. Narrowing step 2 helps everything after it.

Exploration guide

1. Step through a query. Press Step repeatedly and watch which clause runs at each stage. FROM produces the rows, WHERE removes some, GROUP BY collapses the rest, and SELECT is near the end.Run this
2. Break it deliberately. Enable the option that uses a SELECT alias in WHERE. It fails, because at that point the alias has not been created.
3. Then use the same alias in ORDER BY. Enable that option instead. It works — same alias, different clause, and the execution order is the only reason.
4. Reset and compare. Press Reset and run a working query, watching how many rows survive each stage. Filtering early leaves far less work for everything downstream.Run this

What usually goes wrong

• Using a SELECT alias in WHERE or GROUP BY. Repeat the expression, or wrap the query in a subquery or CTE where the alias already exists.
• Putting an aggregate in WHERE. Use HAVING.
• Using HAVING for row filters. Correct results, more work — the rows are grouped before being discarded.
• Expecting LIMIT to speed up an aggregate. LIMIT runs last, so the database has already grouped and sorted everything before it truncates.
• Filtering an outer join in WHERE. A condition on the right-hand table in WHERE discards the NULL rows and silently turns the LEFT JOIN into an INNER JOIN. Put it in the ON clause instead.

Key takeaway

SQL executes FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT — not the order it is written in. That single fact explains why SELECT aliases work in ORDER BY but not WHERE, why aggregates belong in HAVING rather than WHERE, and why filtering early is faster. When a query does something surprising, walking it through the execution order usually answers it in one step.

Where the other clauses fit

Several things do not appear in the classic seven-step list and need placing.

• DISTINCT runs after SELECT and before ORDER BY. That is why SELECT DISTINCT x FROM t ORDER BY y fails in strict engines — y was removed before the sort could use it.
• Window functions are computed after HAVING and before DISTINCT. They can see the grouped rows, and WHERE/HAVING cannot see them. Filtering on a window function always needs an outer query.
• CTEs (WITH) are conceptually evaluated first, as named inputs to the main query, though modern planners inline them and optimise across the boundary.
• OFFSET runs with LIMIT, at the very end, after everything has been produced and sorted — which is exactly why deep offsets are slow.

Putting those together, the fuller order is:

FROM → WHERE → GROUP BY → HAVING → window functions → SELECT → DISTINCT → ORDER BY → LIMIT/OFFSET

What the planner is allowed to change

The logical order defines the result, not the physical work. The optimiser rewrites freely as long as the answer is identical:

• Predicate pushdown — a WHERE condition can be applied while scanning a table, before the join, rather than after it.
• Join reordering — inner joins can be executed in any order; the planner picks the one with the smallest intermediate results.
• Index-provided ordering — if an index already returns rows in the requested order, step 6 costs nothing.
• Early termination — with LIMIT and a matching index, the engine can stop as soon as it has enough rows, without producing the full result.

This is why EXPLAIN output does not look like the seven steps: it shows the physical plan. The logical order is what you reason about; the plan is what actually ran.

The order it is written in is not the order it runs

SQL is written SELECT first and evaluated SELECT almost last. Most of the confusing errors about aliases and aggregates follow from that -- and this engine is more forgiving than the standard, in a way worth knowing before you move the query somewhere stricter.

-- Written order:   SELECT  FROM  WHERE  GROUP BY  HAVING  ORDER BY  LIMIT
-- Evaluated order: FROM  WHERE  GROUP BY  HAVING  SELECT  ORDER BY  LIMIT
--
-- FROM runs first and SELECT runs fifth, which is why a query can filter
-- on a column it never selects, and why the errors below happen.

SELECT c.name                    AS customer,
       count(o.id)               AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE  c.signup < '2024-01-01'
GROUP BY c.id, c.name
HAVING count(o.id) > 0
ORDER BY orders DESC
LIMIT 2;

-- Three experiments. Run each by editing the query above.
--
-- 1. Replace "HAVING count(o.id) > 0" with "WHERE count(o.id) > 0".
--    SQLite answers "misuse of aggregate: count()". At the moment WHERE
--    is evaluated the rows have not been grouped, so there is nothing to
--    count. This is the error the evaluation order predicts.
--
-- 2. Replace "ORDER BY orders DESC" with "ORDER BY nonsense". You get
--    "no such column: nonsense" -- ORDER BY is resolved after SELECT, so
--    it can see the aliases SELECT created, and only those.
--
-- 3. Now the one that will surprise you. Try filtering on a plain
--    (non-aggregate) alias in WHERE:

SELECT name, upper(city) AS big
FROM customers
WHERE big = 'LONDON';

-- That WORKS here. By the standard it should not: WHERE is evaluated
-- before SELECT, so the alias "big" does not exist yet, and PostgreSQL
-- and MySQL both reject it. SQLite chooses to resolve SELECT aliases in
-- WHERE as a convenience.
--
-- Which makes it a portability trap rather than a feature. A query that
-- runs in your local SQLite and fails in production is a worse outcome
-- than one that fails in both, and this is one of the few places the two
-- genuinely differ. Write the expression out in WHERE and it works
-- everywhere:

SELECT name, upper(city) AS big
FROM customers
WHERE upper(city) = 'LONDON';

Questions people ask

Why can ORDER BY use an alias but WHERE cannot? Because SELECT (which creates aliases) runs after WHERE and before ORDER BY.

Does LIMIT make the query faster? Sometimes dramatically — with a matching index the engine can stop early. Without one, it may still have to produce and sort everything first.

Is SELECT * slower? It transfers more data and can prevent index-only scans, so on wide tables, yes. Name the columns you need.

Where do subqueries fit? A subquery in FROM is evaluated as part of step 1; one in WHERE as part of step 2; a correlated one runs in the context of the row being tested.

Does this order apply to every database? The logical order is standard and consistent across engines. Which relaxations they allow — aliases in GROUP BY, for instance — varies.

Why does my query return more rows after adding a join? Because step 1 happens first, and a one-to-many join multiplies rows before any filtering or grouping gets a chance to run.

Recap in one screen

• Execution order is FROM, WHERE, GROUP BY, HAVING, window functions, SELECT, DISTINCT, ORDER BY, LIMIT.
• Aliases created in SELECT are unavailable in WHERE and HAVING, and available in ORDER BY.
• Aggregates do not exist until GROUP BY has run, which is why they belong in HAVING.
• The join happens first, so it decides the row count everything else works on.
• The planner may reorder the physical work freely — read EXPLAIN for what actually happened.