Modules/Database/ Execution Order Lab

Query Execution Order

Databases & SQLBy Ashish JangraUpdated 26 October 2026

You type SELECT first. The database runs it fifth. Step through the real order and watch the row count change at every stage.

Step Through It

Press Step to run the first stage: FROM.

Try Breaking It

Use the SELECT alias in WHERE Use the SELECT alias in ORDER BY

The Query, Written

—

The Order It Actually Runs

Rows At This Stage

Row Count

Surviving Rows

Overview

A useful simplified order for a query is: FROM and joins, WHERE, GROUP BY, HAVING, the SELECT list, DISTINCT, ORDER BY, then LIMIT or its equivalent. This is a reasoning model for the query's meaning, not a requirement that the physical engine execute each stage literally in this order.

The logical stages

The distinction explains common errors. WHERE filters input rows before aggregate values are formed, so it cannot normally refer to an aggregate from the SELECT list. HAVING filters groups after aggregation. A select-list alias is generally not available in WHERE because that logical stage comes earlier, though dialects vary in which later clauses accept aliases.

Follow a grouped query

To list customers with more than two paid orders:

query.sqlSQLite

SELECT customer_id, COUNT(*) AS paid_orders
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING COUNT(*) > 2
ORDER BY paid_orders DESC;

SELECT customer_id, COUNT(*) AS paid_orders
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING COUNT(*) > 2
ORDER BY paid_orders DESC;

Result

WHERE selects the contributing orders; GROUP BY defines one result per customer; HAVING keeps qualifying groups; ORDER BY arranges the final rows.

Logical order is not the physical plan

Window functions are evaluated after grouping and HAVING in the logical model, but before the final ORDER BY. That is why a window result usually needs an outer query or CTE before it can be filtered. The database planner can reorder joins, push filters, and choose indexes as long as the result semantics stay correct. Do not infer actual runtime from the logical model. Use EXPLAIN to inspect a physical plan; see query plans. For grouping clauses, see GROUP BY and HAVING.

Why aliases can surprise you

In many SQL dialects, an alias introduced in the SELECT list is available to ORDER BY but not WHERE. The WHERE stage logically happens before that output alias exists. If a derived value needs to be filtered, repeat the expression or place it in a CTE/subquery and filter from the outer query. Dialects sometimes extend alias visibility, so portable code should not depend on a nonstandard shortcut. The logical order also clarifies why HAVING can use aggregate conditions while WHERE cannot.

Separate stages with a query boundary

A CTE or derived table creates a clear boundary when an expression needs to be filtered after it is calculated. First select the computed value in the inner query, then reference its column from the outer WHERE. This is useful for window results and aliases, but it does not force the database to materialize the inner query. Keep the logical explanation separate from performance claims and inspect the plan if the nested form is unexpectedly slow.