DataslopeQuery Execution Order
The hidden order in which SQL clauses really run — and why understanding it explains many puzzling errors and results.
You write a query as SELECT ... FROM ... WHERE ... GROUP BY ...,
but PostgreSQL does not run it in that order. SQL has a hidden
logical execution order, and learning it explains a whole class
of confusing errors — like "why can't I use an alias in WHERE?"
This is one of the most clarifying ideas in all of SQL.
Written order vs. execution order
The order you type is for human readability. The order the database reasons about the query is different:
Read the numbers: FROM is first, SELECT is near the end, and
LIMIT is last. The clause you write first (SELECT) is among the
last to actually run.
Why this explains real errors
Most "why doesn't this work?" moments dissolve once you know the order. Two classic examples:
You cannot use a SELECT alias in WHERE. Because WHERE
(step 2) runs before SELECT (step 5), the alias does not exist
yet when WHERE is evaluated:
But you can use the alias in ORDER BY, because ORDER BY
(step 7) runs after SELECT (step 5), by which time the alias
exists. The execution order predicts exactly which works.
A rule you can now derive, not memorize
"Aliases work in ORDER BY but not in WHERE" used to be a fact to
memorize. Now it is something you can derive: ORDER BY comes
after SELECT, WHERE comes before it. The order is the
explanation.
Tracing a full query
Watch every clause take its turn on this query. Each step hands its result to the next:
Trace it in execution order:
- FROM
orders— start with all rows. - WHERE
amount > 10— drop the small individual orders. - GROUP BY
customer_id— bundle rows into one group per customer. - HAVING
SUM(amount) > 50— drop groups whose total is too small. - SELECT
customer_id, SUM(amount) AS total— compute the output, naming the sumtotal. - ORDER BY
total DESC— sort the surviving groups. - LIMIT 2 — keep the top two.
This is the query transformation pipeline: each stage reshapes a table and passes it on. Every query you write — however complex — flows through these same steps.
WHERE vs. HAVING, settled
The order also makes the WHERE/HAVING distinction obvious.
WHERE (step 2) runs before grouping, so it filters individual
rows. HAVING (step 4) runs after grouping, so it filters
whole groups:
You cannot filter on SUM(amount) in WHERE, because the sum does
not exist until grouping happens — which is step 3, after WHERE.
You must use HAVING. Once again, the order is the reason.
Check your understanding
Which clause is evaluated first in a SQL query's logical execution order?
SELECT, because it is written first.
FROM (and its joins), which gathers the source rows before anything else.
ORDER BY, because sorting must happen up front.
WHERE, before the tables are even chosen.
Why can a column alias defined in SELECT be used in ORDER BY but not in WHERE?
Because ORDER BY is optional and WHERE is required.
Because WHERE runs before SELECT (so the alias does not exist yet), while ORDER BY runs after SELECT.
Because aliases only work with numbers.
Because WHERE cannot reference any columns at all.
Why must you filter on SUM(amount) > 50 using HAVING rather than WHERE?
Because WHERE cannot contain numbers.
Because the group's SUM is only formed at the GROUP BY step, which runs after WHERE; HAVING runs after grouping.
Because HAVING is just a faster version of WHERE.
Because SUM cannot be used anywhere except HAVING.
In the execution order, where does LIMIT fall?
First, to reduce work before anything else.
Right after FROM.
Last, after ORDER BY, keeping the first N of the final sorted rows.
Before WHERE.