SQL / RELATIONAL FOUNDATIONS
How a SELECT is really evaluated, step by step
Trace any SELECT through its real evaluation order (FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT) and predict what each clause can see.
What you will learn
- Trace a SELECT through FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT in order
- Predict whether a clause can see a SELECT alias from which stage runs first
- Put row filters in WHERE and aggregate filters in HAVING, for the right reason
- Explain why a bare column in SELECT is rejected once GROUP BY has collapsed rows
Understanding How a SELECT is really evaluated, step by step
A SELECT is written SELECT-first but evaluated FROM-first. The engine works as a pipeline of stages, and each stage receives a table from the stage before it and hands a new table to the stage after it: FROM assembles the source rows, WHERE throws rows away, GROUP BY collapses the survivors into one row per distinct grouping key, HAVING throws groups away, SELECT computes the output columns, DISTINCT removes duplicate output rows, ORDER BY sorts them, and LIMIT cuts the sorted list short. Nothing in that chain can reach forward; a stage only knows the table it was handed.
That single fact explains most of the rules people memorise separately. WHERE runs before SELECT, so a column alias invented in SELECT does not exist yet when WHERE is checked, while ORDER BY runs after SELECT and can use the alias freely. WHERE also cannot mention SUM(amount) or COUNT(*), because at that point there are no groups to aggregate over, which is exactly the gap HAVING fills. And once GROUP BY has run, the table it produced holds only the grouping keys plus aggregates, so a bare column in SELECT is rejected: the group contains many values for it and the engine will not silently pick one.
This is the logical order, not the physical plan. A planner is free to evaluate a filter inside an index scan, reorder joins, or stop reading early for a LIMIT, as long as the answer matches what the logical order would produce, so EXPLAIN output will rarely look like this list. Keeping the logical order in mind is still what lets you read an error message and know which stage complained, and it is why LIMIT without ORDER BY has no defined meaning: LIMIT slices whatever order the previous stage happened to emit.
CREATE TABLE sale (
id integer PRIMARY KEY,
region text NOT NULL,
amount integer NOT NULL
);
INSERT INTO sale (id, region, amount) VALUES
(1, 'north', 100),
(2, 'north', 250),
(3, 'south', 40),
(4, 'south', 60),
(5, 'east', 900),
(6, 'east', 10),
(7, 'west', 500);
SELECT region, SUM(amount) AS total
FROM sale
WHERE amount >= 50
GROUP BY region
HAVING SUM(amount) > 150
ORDER BY total DESC
LIMIT 2;The clause order you type is not the evaluation order, and each stage of a SELECT can only see names that an earlier stage already produced.
Worked examples
The alias exists for ORDER BY but not for WHERE
Shows that the same alias is invisible to WHERE and visible to ORDER BY, purely because of when each clause runs.
SELECT id, amount * 2 AS doubled
FROM sale
WHERE doubled > 500;
SELECT id, amount * 2 AS doubled
FROM sale
WHERE amount * 2 > 500
ORDER BY doubled;Example explained
Line 1WHERE doubled > 500 fails because WHERE is evaluated before SELECT, so no column named doubled has been created yet.
Line 2Writing the expression out again as WHERE amount * 2 > 500 works: WHERE can evaluate expressions, it just cannot reference output names.
Line 3ORDER BY doubled succeeds in the same query because sorting happens after SELECT has built the output columns.
Line 4Both queries scan the same rows; only id 5 (900) and id 7 (500) have amount * 2 above 500.
HAVING filters groups, not rows
Demonstrates that a condition on COUNT(*) can only be checked after GROUP BY has collapsed rows into groups.
SELECT region, COUNT(*) AS n, MIN(amount) AS smallest
FROM sale
GROUP BY region
HAVING COUNT(*) = 2
ORDER BY region;Example explained
Line 1With no WHERE clause, GROUP BY region turns all seven rows into four group rows: east, north, south, west.
Line 2HAVING COUNT(*) = 2 drops west, which has a single row; the same condition in WHERE would be an error because counts do not exist before grouping.
Line 3MIN(amount) for east is 10, proving the group still had access to both of its rows when the aggregate was computed.
Line 4ORDER BY region runs last, so it sorts the three surviving group rows, not the original seven.
Sorting by a column you did not select
Shows that ORDER BY reads from the pipeline built by FROM and WHERE, not only from the SELECT list.
SELECT region
FROM sale
WHERE amount >= 50
ORDER BY amount DESC;Example explained
Line 1WHERE amount >= 50 removes id 3 (40) and id 6 (10), leaving five rows that still carry every column of sale.
Line 2ORDER BY amount DESC is legal even though amount is not in the SELECT list, because the rows being sorted still hold it.
Line 3north appears twice: SELECT projects one column but does not remove duplicates on its own.
Line 4Adding DISTINCT would break this query, since DISTINCT runs before ORDER BY and leaves only the region values to sort by.
LIMIT cuts whatever the previous stage produced
Illustrates that LIMIT is the last stage, so it can only ever slice an already-sorted or already-unsorted result.
SELECT region, amount
FROM sale
WHERE amount < 300
ORDER BY amount DESC
LIMIT 2;Example explained
Line 1WHERE keeps the four rows under 300: 100, 250, 40, 60 — the 900 and 500 rows never reach the sort.
Line 2ORDER BY amount DESC arranges those four as 250, 100, 60, 40.
Line 3LIMIT 2 then takes the first two of that sorted list; remove the ORDER BY and the two rows you get are whatever order the scan happened to produce.
Line 4Because LIMIT runs after WHERE, it cannot pull in the 900 row to fill the quota.
Important notes
The order described here is the logical definition of the result, not the execution plan; a planner may push a filter into a scan or reorder joins as long as the final rows match.
MySQL and SQLite accept output aliases in WHERE, GROUP BY, and HAVING as a vendor extension, so a query that works there can fail on PostgreSQL or another standard-conforming engine.
Common mistakes
Reusing a SELECT alias in WHERE, as in WHERE total > 150: PostgreSQL answers column "total" does not exist, because WHERE is evaluated before SELECT creates that name.
Writing WHERE SUM(amount) > 150 instead of HAVING SUM(amount) > 150: the server reports that aggregate functions are not allowed in WHERE, since no groups exist that early in the pipeline.
Selecting a column that is neither grouped nor aggregated, such as SELECT region, amount ... GROUP BY region: the group holds several amounts and SELECT runs after grouping, so the query is rejected rather than guessing one.
Try it yourself
Change, predict, then run
Using the sale table from the main example, write a query that returns each region whose largest single amount is at least 100, aliasing that maximum as top_sale and sorting by it descending; you should get east 900, west 500, north 250. Then move the condition into WHERE top_sale >= 100 and read the error you get.
Open the SQL workspaceCheck your understanding
SELECT price * 1.2 AS gross FROM item WHERE gross > 100 fails, but changing the last line to ORDER BY gross works. What explains the difference?
- WHERE is evaluated before SELECT, so the alias does not exist yet, while ORDER BY is evaluated after SELECT and can use it
- WHERE accepts only plain column names, never computed expressions
- The alias is only assigned once rows have been sorted, and WHERE does no sorting
- gross is a reserved word and must be double-quoted when used as a condition
Show answer
Names are visible only to stages that run after the stage creating them; SELECT builds gross, so WHERE (earlier) cannot see it and ORDER BY (later) can. The claim that WHERE rejects expressions is tempting but false: WHERE price * 1.2 > 100 is perfectly valid, which is exactly the workaround for this error.