Most SQL bugs aren't logic errors. They're sequence errors — the result of writing a query in one order and the database executing it in another.
Consider this query:
SELECT department, COUNT(*) AS headcount
FROM employees
WHERE headcount > 5
GROUP BY department;
It looks reasonable. It will not run. The error you get back — something like column "headcount" does not exist — feels like a bug in the database. It isn't. The database is being completely consistent. headcount doesn't exist yet when WHERE runs, because SELECT hasn't run yet. You defined the alias in step 5. You tried to use it in step 2.
This is the trap: SQL reads like a sentence, so we write it like one. Give me the department and count, from employees, where the count is greater than five. That sentence has a natural English order, and SQL's syntax follows it. But the database doesn't execute it in that order — it executes it in the order that's logically necessary to produce a correct result.
FROM runs first, because you can't filter rows from a table you haven't opened yet. WHERE runs before GROUP BY, because grouping a million rows is expensive and you want to throw out the ones you don't need first. SELECT runs near the end, because it's not retrieving data — it's describing the shape of the output. LIMIT runs last, because you can't cut the top 10 rows of a result set that hasn't been sorted yet.
Once you see the execution order, a whole class of confusing query behaviour stops being mysterious. The alias that doesn't exist. The HAVING filter that's slower than expected. The LIMIT 10 that doesn't feel fast. These aren't quirks. They're the same fact, seen from different angles.
The execution order is this: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.
That's the sequence the rest of this article is built around.
The Execution Order, Explained
The seven steps aren't arbitrary. Each one sits where it does because it depends on the output of the step before it. Work through them once and the order becomes obvious — even inevitable.
Step 1 · The FROM/JOIN World Building
Before the database can do anything, it needs a dataset to work with. FROM identifies the source tables; JOIN combines them. Everything else in your query operates on the result of this step. If you're joining three tables, the database assembles that combined row set first — all of it, before a single filter is applied.
This is worth sitting with. The full Cartesian product of your joins exists, conceptually, before WHERE runs. The optimizer will often reorder things under the hood, but the logical model is: FROM builds the world, everything else narrows it.
Step 2 · The Great WHERE Filter
Now the database discards rows that don't meet your conditions. This is your primary cost-reduction lever — every row eliminated here is a row that never reaches grouping, aggregation, or sorting.
Two constraints follow directly from position. First, you can't reference SELECT aliases here, because those don't exist yet. And here is why the query above is failing.
Second, you can't filter on aggregate values like COUNT(*) — aggregation hasn't happened yet either.
If you find yourself wanting either of those things, you need HAVING or a CTE, not WHERE.
Step 3 · The GROUP BY Bucket Brigade
Rows that survived WHERE are now collapsed into groups. If you ask for GROUP BY department, every row belonging to Engineering becomes one bucket, every row belonging to Product becomes another.
Here's the caveat: once you've grouped, individual rows are gone. The only things you can reference in subsequent steps are the columns you grouped by, and aggregate functions over the groups.
This is why SELECT name, COUNT(*) without name in GROUP BY is an error in strict SQL — after grouping, there's no single name to return, only a group of them.
Step 4 · The HAVING Bouncer
HAVING is WHERE for groups. It runs after aggregation, which means two things: it can filter on aggregate values (HAVING COUNT(*) > 5), and it's more expensive than WHERE because the grouping work has already been done before anything gets discarded.
This is the most commonly misused step. If a condition doesn't depend on an aggregate, it belongs in WHERE. Putting it in HAVING isn't wrong — you'll get the right answer — but you're doing extra work for no reason. Group first, then filter, when you could have filtered first, then grouped on far fewer rows.
Step 5 · The SELECT Stylist
This is the step people mentally treat as first. It isn't. By the time SELECT runs, the database already knows exactly which rows and groups exist in the result — it's now computing what to show you about them.
Column aliases are created here. Expressions are evaluated here. Window functions run here, between SELECT and ORDER BY, which is why they can reference column aliases but still can't be filtered on in WHERE or HAVING — by the time they exist, both of those steps have already finished.
Step 6 · The ORDER BY Sorter
Sorting happens near the end because it's expensive and you want to sort as few rows as possible. It's placed after SELECT for a practical reason: it's the first step that can reference column aliases, because SELECT has already run.
One trap here: ORDER BY sorts the entire result set before LIMIT slices it. A query with ORDER BY salary DESC LIMIT 10 doesn't find the top 10 and stop — it sorts everything, then hands the first 10 rows to the next step. Without an index that supports the sort, you're paying full sorting cost regardless of how small your LIMIT is.
Step 7 · The LIMIT/OFFSET Director's Cut
The last thing that happens. Everything upstream has already run to completion — the joins, the filters, the grouping, the sorting. LIMIT just decides how much of the finished result set to hand back to you.
This makes LIMIT nearly useless as a performance tool on its own. It reduces network transfer and client-side processing, but it doesn't reduce database work unless the optimizer can prove it's safe to short-circuit earlier steps — which generally requires an index on the sort column.
The picture that emerges: SQL is a narrowing pipeline. FROM produces the maximum possible dataset. Every subsequent step either filters it, reshapes it, or describes it. The earlier you reduce rows, the less work every downstream step has to do. That's not a tip — it's the structure of the language.
Four Rules That Follow Directly From The Order
Understanding the execution order is only useful if it changes how you write queries. These four rules aren't best practices abstracted from experience — they fall out of the sequence mechanically. If you know the order, you can derive them yourself.
Rule 1 · Filter Early, Filter in WHERE
If a condition doesn't depend on an aggregate, it belongs in
WHERE, notHAVING.
-- Doing unnecessary work
SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department
HAVING department = 'Engineering';
-- Correct
SELECT department, COUNT(*) AS headcount
FROM employees
WHERE department = 'Engineering'
GROUP BY department;
Both return the same result. But the first version groups every department in the table, then throws away every group except Engineering. The second version discards non-Engineering rows before grouping, so the grouping step only ever sees one department's worth of rows.
The rule is mechanical: if your HAVING clause contains no aggregate function, it should be a WHERE clause.
Rule 2 · HAVING Is For Aggregates, Nothing Else
HAVINGexists for exactly one purpose — filtering on values that don't exist until afterGROUP BYruns.
-- This is what HAVING is for
SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;
You can't move this into WHERE — COUNT(*) doesn't exist at step 2. HAVING is the only place it can go. Use it for that, and only that. When you see a HAVING clause filtering on a plain column value, that's a signal someone misunderstood the execution order.
Rule 3 · LIMIT Is Not A Performance Tool On Its Own
This one surprises people. A query with ORDER BY salary DESC LIMIT 10 feels like it should be fast — you only want ten rows. But because ORDER BY runs before LIMIT, the database sorts the entire result set first, then hands you the top ten.
-- Sorts every row in the table, then returns 10
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 10;
LIMIT reduces how much data travels over the wire and how much your application has to process. It does not reduce how much work the database does — unless the optimizer can use an index to satisfy the sort without a full scan, which requires an index on the sort column.
If you're relying on LIMIT for performance, the real question is whether your WHERE clause is doing enough work earlier in the pipeline.
Rule 4 · When You Need To Filter On An Alias, Use A CTE
Because SELECT aliases are created in step 5, neither WHERE (step 2) nor HAVING (step 4) can see them. This is the source of the error in the opening example. The fix isn't to find a workaround inside the same query — it's to wrap the query in a CTE, promoting its output into a new FROM clause that subsequent steps can filter against.
-- Fails: headcount alias doesn't exist at WHERE
SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department
WHERE headcount > 5;
-- Correct: CTE makes headcount available as a column
WITH dept_counts AS (
SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department
)
SELECT department, headcount
FROM dept_counts
WHERE headcount > 5;
The CTE isn't syntactic sugar here — it's a structural necessity. The inner query runs to completion, aliases and all, and its output becomes a new dataset that the outer query's FROM picks up at step 1. The outer WHERE is now filtering a real column, not a future alias.
The same pattern applies to window functions. You can't filter on a window function result in WHERE or HAVING — wrap the query in a CTE and filter on the outer query.
These four rules share a single root: the database processes your query in a fixed sequence, and every constraint follows from knowing where in that sequence each clause lives. You don't need to memorise edge cases. You need to know the order.
A Worked Example: How Long Does It Take To Acknowledge A Factory Event?
Let's make this concrete. You're working with factory floor data. Events get raised — a machine fault, a threshold breach, a safety alert — and an operator eventually acknowledges them. You want to know: how long does acknowledgement take, on average, broken down by line and shift?
The tables:
-
machine_events— one row per event raised, with an event ID, machine ID, line, shift, and the timestamp it was raised -
event_transitions— one row per state transition per event; each row records the old state, the new state, and when the transition happened -
operator_audit— one row per operator action; records who did what and when, keyed on event ID
The query we want to end up with:
WITH acknowledgements AS (
SELECT
et.event_id,
et.transitioned_at AS acknowledged_at
FROM event_transitions et
WHERE et.new_state = 'Acknowledged'
AND et.previous_state = 'Unacknowledged'
),
ack_times AS (
SELECT
me.event_id,
me.line,
me.shift,
oa.operator_id,
EXTRACT(EPOCH FROM (a.acknowledged_at - me.raised_at)) / 60 AS minutes_to_ack
FROM machine_events me
JOIN acknowledgements a ON me.event_id = a.event_id
JOIN operator_audit oa ON me.event_id = oa.event_id
WHERE me.line IN ('Line A', 'Line B', 'Line C')
AND me.raised_at >= '2024-01-01'
)
SELECT
line,
shift,
COUNT(*) AS total_events,
ROUND(AVG(minutes_to_ack), 1) AS mean_ack_minutes,
PERCENTILE_CONT(0.5)
WITHIN GROUP (ORDER BY minutes_to_ack) AS median_ack_minutes,
ROUND(MIN(minutes_to_ack), 1) AS fastest_ack,
ROUND(MAX(minutes_to_ack), 1) AS slowest_ack
FROM ack_times
WHERE minutes_to_ack IS NOT NULL
GROUP BY line, shift
HAVING COUNT(*) >= 10
ORDER BY line, shift;
Now let's walk it through the execution order and see exactly what the database does — and why the query is structured the way it is.
Step 1 · FROM / JOIN — The Database Builds The World
The first CTE, acknowledgements, opens event_transitions and immediately has access to every state transition ever recorded — potentially millions of rows across every event, every machine, every year of data. Nothing has been filtered yet.
The second CTE, ack_times, then joins three tables: machine_events, the acknowledgements CTE, and operator_audit. At this point, conceptually, the database is assembling a combined row set across all three sources. Every event, joined to its acknowledgement record, joined to its audit record. This is the most expensive moment in the query — the joined dataset is at its largest before a single row has been discarded.
This is why the acknowledgements CTE exists. Rather than joining raw event_transitions directly and filtering it in the outer WHERE, we pre-filter it inside the CTE — keeping only the Unacknowledged → Acknowledged transitions before the join happens. The join is cheaper because the right-hand side of it is already small.
Step 2 · WHERE — Cut Early, Cut Hard
Inside ack_times, WHERE runs immediately after the joins:
WHERE me.line IN ('Line A', 'Line B', 'Line C')
AND me.raised_at >= '2024-01-01'
These two conditions discard every event outside our three lines and everything before 2024 before any grouping or aggregation happens. If machine_events holds five years of data across twelve lines, this single WHERE clause might eliminate 80% of the joined rows before the database does any further work.
Notice what is not here: there is no filter on minutes_to_ack at this point. That column doesn't exist yet — it's a computed expression defined in the SELECT of ack_times, which means it belongs to step 5, three steps from now. To filter on it, we need the outer query's WHERE:
WHERE minutes_to_ack IS NOT NULL
This is the CTE pattern from rule 4 in action. The inner query computes minutes_to_ack and promotes it to a real column. The outer query's WHERE can then filter on it cleanly, at step 2 of the outer query's own execution cycle.
Step 3 · GROUP BY — Collapsing Into Buckets
GROUP BY line, shift
Every row that survived WHERE is now collapsed into a (line, shift) bucket. A row that was previously o_ne specific event on Line A during the night shift is now part of the Line A / night shift group_. Individual event IDs, operator IDs, and raw timestamps are gone — only the group identity and aggregate functions over the group remain.
This is why the outer SELECT can reference line and shift directly, but has to use AVG(), COUNT(), MIN(), and MAX() for everything else. After grouping, there is no single minutes_to_ack to return — only a distribution of them, which aggregation summarises.
Step 4 · HAVING — Filtering Groups, Not Rows
HAVING COUNT(*) >= 10
This discards any (line, shift) combination with fewer than ten events. We can't move this into WHERE — COUNT(*) doesn't exist until after grouping. This is the legitimate use of HAVING: a filter that genuinely depends on an aggregate.
What we deliberately avoided putting in HAVING: the line and date filters from step 2. Those conditions don't depend on aggregates — they belong in WHERE, and that's where they are.
Putting them in HAVING instead would mean grouping all lines and all years of data, computing aggregates for all of them, and then discarding the ones we didn't want. With five years of factory data across twelve lines, that's a significant amount of unnecessary work.
Step 5 · SELECT — Describing The Output
SELECT
line,
shift,
COUNT(*) AS total_events,
ROUND(AVG(minutes_to_ack), 1) AS mean_ack_minutes,
PERCENTILE_CONT(0.5)
WITHIN GROUP (ORDER BY minutes_to_ack) AS median_ack_minutes,
ROUND(MIN(minutes_to_ack), 1) AS fastest_ack,
ROUND(MAX(minutes_to_ack), 1) AS slowest_ack
Only now does the database compute the actual output values. The aliases mean_ack_minutes, median_ack_minutes, and so on are born here. This is why none of the earlier steps could reference them — they didn't exist yet.
PERCENTILE_CONT is worth a note. Unlike AVG or COUNT, it's an ordered-set aggregate — it needs to know the distribution of values within each group to find the midpoint. The database computes this per group, after grouping is complete, entirely within this step.
Step 6 · ORDER BY — Sorting The Finished Result
ORDER BY line, shift
The full result set — however many (line, shift) combinations survived HAVING — is sorted here. Because this runs after SELECT, it can reference the output column names directly. If we'd wanted to sort by mean_ack_minutes DESC to surface the slowest lines first, that would work here and nowhere else in the query.
Step 7 · LIMIT — The Exit That Doesn't Help Here
This query has no LIMIT, and deliberately so. We want all (line, shift) combinations — there are only a handful, so there's nothing to cut. But if this query fed a dashboard endpoint and someone added LIMIT 20 thinking it would speed things up: it wouldn't. The joins, the filtering, the grouping, the aggregation, and the sort all run to completion first. LIMIT only reduces the payload sent back to the application. The real performance work happens in steps 1 through 4.
What The Execution Order Reveals About This Query
Tracing the query through all seven steps surfaces something that code review alone might miss: the two-CTE structure isn't stylistic — it's load-bearing. The first CTE pre-filters event_transitions to only acknowledgement transitions before the join. The second CTE computes minutes_to_ack so the outer query can filter on it in WHERE. Both decisions reduce the dataset earlier in the pipeline than a flat query would allow. Without them, the same result would require filtering in HAVING, sorting computed values that should have been discarded, and joining against a much larger intermediate dataset.
The execution order is the reason those CTEs are there.
Quick Reference
| Clause | Execution step | Can use SELECT aliases? |
Runs before or after aggregation? | Typical mistake |
|---|---|---|---|---|
FROM / JOIN
|
1 | No | Before | Joining large tables without pre-filtering in a CTE |
WHERE |
2 | No | Before | Filtering on an alias or aggregate that doesn't exist yet |
GROUP BY |
3 | No | Before | Referencing ungrouped columns in subsequent SELECT
|
HAVING |
4 | No | After | Using it for non-aggregate conditions that belong in WHERE
|
SELECT |
5 | Born here | After | Expecting aliases to be available in earlier clauses |
ORDER BY |
6 | Yes | After | Assuming ORDER BY + LIMIT avoids a full sort |
LIMIT |
7 | Yes | After | Using it as a performance tool without an index-backed sort |
Conclusion
SQL's syntax is designed to be readable. The execution order is designed to be correct. Those two goals produce different sequences, and the gap between them is where most query bugs live.
The alias that doesn't exist. The HAVING clause doing work that WHERE should have done three steps earlier. The LIMIT 10 on a query that's still sorting a million rows. None of these are edge cases or database quirks — they're the same misunderstanding, wearing different clothes.
Once the execution order is internalised it stops being a rule to remember and becomes a lens. You read a query differently. You spot the HAVING with no aggregate and know immediately it's in the wrong place. You see a CTE and understand it's not there for readability — it's there because the alternative would require filtering on a value that doesn't exist yet. You add a WHERE clause early and feel the weight of everything it saves downstream.
That's the shift this article is trying to produce. Not a checklist, not a set of rules to apply mechanically — a mental model that makes the right structure feel obvious.
Write queries in the order the database executes them, not the order English suggests. Everything else follows.
Top comments (0)