SQL Order of Execution: How a Query Really Runs
· 7 min read
SQL does not run a query in the order you write it. You write SELECT first, but the database logically starts with FROM. The SQL order of execution is:
FROMandJOIN: get the rowsWHERE: filter rowsGROUP BY: build groupsHAVING: filter groupsSELECT: calculate columns, including aliases and window functionsDISTINCT: remove duplicate rowsORDER BY: sortLIMIT/OFFSET: keep the first N rows
SELECT customer_name, SUM(amount) AS total
FROM orders
WHERE status = 'completed'
GROUP BY customer_name
HAVING SUM(amount) > 200
ORDER BY total DESC
LIMIT 2;Almost every confusing SQL error has the same cause: a clause trying to use something that does not exist yet. Below, the query above is traced step by step, followed by the rules that fall out of the order, and the alias mix-up that changes results without an error.
A worked example
| order_id | customer_name | status | amount |
|---|---|---|---|
| 1 | Acme Dental | completed | 120 |
| 2 | Acme Dental | completed | 260 |
| 3 | Birch Cafe | completed | 90 |
| 4 | Birch Cafe | cancelled | 500 |
| 5 | Cobalt Labs | completed | 310 |
| 6 | Cobalt Labs | completed | 140 |
| 7 | Dune Studio | completed | 70 |
Here is what happens at each stage:
| Step | Clause | What is left |
|---|---|---|
| 1 | FROM orders | all 7 rows |
| 2 | WHERE status = 'completed' | 6 rows (order 4 is gone) |
| 3 | GROUP BY customer_name | 4 groups: Acme 380, Birch 90, Cobalt 450, Dune 70 |
| 4 | HAVING SUM(amount) > 200 | 2 groups: Acme, Cobalt |
| 5 | SELECT ... AS total | the total column now exists |
| 7 | ORDER BY total DESC | Cobalt, then Acme |
| 8 | LIMIT 2 | both rows kept |
Run the full query and that is exactly what comes back:
SELECT customer_name, SUM(amount) AS total
FROM orders
WHERE status = 'completed'
GROUP BY customer_name
HAVING SUM(amount) > 200
ORDER BY total DESC
LIMIT 2;| customer_name | total |
|---|---|
| Cobalt Labs | 450 |
| Acme Dental | 380 |
Notice Birch Cafe. Its 500 order would have pushed it over 200, but WHERE removed that cancelled row in step 2, long before HAVING looked at totals in step 4. The difference between those two filters has its own guide: HAVING vs WHERE.
"Logical" order means this is how the result is defined, not necessarily how the engine does the work. The optimiser can reorder steps internally (for example, filter before a join) as long as the result is the same. For writing correct queries, the logical order is the one that matters.
Why an alias in the WHERE clause fails
The alias total is created in step 5. WHERE runs in step 2. So in standard SQL, this fails:
SELECT order_id, amount * 0.8 AS net
FROM orders
WHERE net > 200;Postgres answers column "net" does not exist, and SQL Server says Invalid column name 'net'. You have two fixes. Repeat the expression:
SELECT order_id, amount * 0.8 AS net
FROM orders
WHERE amount * 0.8 > 200
ORDER BY order_id;| order_id | net |
|---|---|
| 2 | 208.0 |
| 4 | 400.0 |
| 5 | 248.0 |
Or calculate the alias in an inner step and filter outside, where it already exists:
SELECT order_id, net
FROM (
SELECT order_id, amount * 0.8 AS net
FROM orders
) AS t
WHERE net > 200
ORDER BY order_id;That returns the same three rows. A CTE does the same thing and reads better for longer queries: see SQL CTE explained.
A dialect note: DuckDB is friendlier. It accepts WHERE net > 200 and returns the same three rows. MySQL allows aliases in HAVING but not in WHERE. Code that works in DuckDB may fail when you move it to Postgres or SQL Server, so the portable habit is to not rely on it.
What the order lets you do (and not do)
| You want to | Allowed? | Why |
|---|---|---|
Use an alias in ORDER BY | yes, everywhere | ORDER BY (7) runs after SELECT (5) |
Use an alias in WHERE | no in standard SQL | WHERE (2) runs before SELECT (5) |
Use an alias in GROUP BY or HAVING | depends on the database | DuckDB and MySQL allow both, Postgres only in GROUP BY, SQL Server in neither |
Use SUM() in WHERE | no | groups do not exist until step 3 |
Use a window function in WHERE | no | window functions are calculated in step 5 |
Use LIMIT to cut rows before grouping | no | LIMIT (8) is always last |
Window functions run in SELECT
Ranking functions are calculated in step 5, so filtering on a rank in WHERE fails. DuckDB says WHERE clause cannot contain window functions!. Compute the rank first, then filter:
WITH ranked AS (
SELECT order_id, amount,
RANK() OVER (ORDER BY amount DESC) AS rnk
FROM orders
WHERE status = 'completed'
)
SELECT order_id, amount, rnk
FROM ranked
WHERE rnk <= 3
ORDER BY rnk;| order_id | amount | rnk |
|---|---|---|
| 5 | 310 | 1 |
| 2 | 260 | 2 |
| 6 | 140 | 3 |
The cancelled 500 order is not ranked, because the inner WHERE removed it before the window function ran. More on this in SQL window functions.
LIMIT is the last thing that happens
LIMIT 10 does not mean "use only 10 rows". It means "after everything else, show 10". The sorting and aggregating still happen on all rows. That is also why LIMIT without ORDER BY gives you an arbitrary set of rows.
Common mistakes
An alias that shadows a real column (the AI mistake to watch for)
AI assistants like to reuse column names. Ask for "net amounts (after 20% commission) above 250" and you may get this:
SELECT order_id, amount * 0.8 AS amount
FROM orders
WHERE amount > 250
ORDER BY order_id;| order_id | amount |
|---|---|
| 2 | 208.0 |
| 4 | 400.0 |
| 5 | 248.0 |
No error, but two of the three rows show net amounts below 250. The WHERE ran in step 2, before the alias existed, so it filtered on the original amount column (260, 500, 310). The output column has the same name, which hides the problem. The correct result for net above 250 is only order 4.
This one is nasty because it works in every database, including DuckDB, which otherwise accepts aliases in WHERE: when an alias and a real column share a name, the real column wins.
How to spot it: compare the filter with the output. If the question says "above 250" and you can see values below 250 in the filtered column, the filter is looking at a different column than you think. Never give an alias the same name as an existing column.
Expecting WHERE to see aggregates
WHERE SUM(amount) > 200 fails because groups are built in step 3. Aggregate conditions go in HAVING.
Assuming the written order is the run order
If you read a query top to bottom, it looks like SELECT happens first. Read it in execution order instead: start at FROM, then WHERE, and only then the columns. Most "why doesn't this work" questions answer themselves.
FAQ
What is the correct order of execution in SQL?
FROM (with joins), WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, then LIMIT. The written order is different: SELECT comes first in the text.
Why can I use an alias in ORDER BY but not in WHERE?
ORDER BY runs after SELECT, so the alias exists by then. WHERE runs before SELECT, so the alias has not been created yet.
Does the database really run the steps in this order?
Not always physically. The optimiser can rearrange work for speed, but the result must match the logical order, so that is the order to reason with.
Where does JOIN fit in the order of execution?
In step 1, with FROM. All joins are resolved before WHERE filters the combined rows.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
Try “The AI found no big spenders” →