Query Structure: Intermediate

A common table expression, written with WITH, gives a result set a name at the top of a statement so every clause below can read it by that name. The database treats the name as a table that lives for one statement: defined before the main query starts, readable by it, gone when it finishes. A 300-line report that computes 6 intermediate results becomes 6 named steps and one short final SELECT, and any step can be checked on its own by selecting from its name.

The shape is WITH name AS (query) followed by the main query. Everything inside the parentheses is an ordinary SELECT, and everything after the closing parenthesis reads name as if it were a table in the schema. The animation below runs 2 filters in sequence over a 5-row users table: the CTE active_set keeps the rows where active = 1, and the outer query keeps the rows whose id is above 100.

Watch the row counts. The CTE scans 5 users and keeps 3: Ava (98), Cleo (101) and Eli (103). The outer WHERE id > 100 never sees the original table; it's applied to those 3 rows and keeps Cleo and Eli. Ben (99) and Dev (102) were already gone before the second filter ran, and that sequence, inner query complete before the outer query starts, is the whole mechanism.

Several CTEs can share one WITH with commas between them, and each can read any CTE declared above it. That turns a transformation with stages into a query with named stages. Below, active_users is the first stage, user_totals sums each user's orders (a GROUP BY, which has its own lesson), and the final SELECT joins the 2 on the user id. Run it, then replace the final SELECT with SELECT * FROM user_totals to inspect one stage alone.

The result holds 3 rows because only 3 users are active, and Ava leads with 130 across 2 orders. Ben spent 75 but is inactive, so user_totals computed his row and the join dropped it. That's the debugging pattern CTEs make possible: when a number looks wrong, select from the stage where it was born. The fault is in that stage or one it reads from, so check each upstream stage by selecting from it.

A CTE exists only inside the statement that declares it; the next statement in the same session can't see it. Declaration order is binding: a CTE may read the ones above it and never the ones below. One name may be referenced as many times as the query needs, which is where CTEs beat a copied subquery. The query below reads active_users twice to compute 2 counts (the subqueries in its SELECT list are the subject of section 2), and a change to the definition of active reaches both.

Whether the engine computes a twice-referenced CTE once or inlines it at each reference depends on the product. PostgreSQL 12 and later inline a CTE referenced once and materialize one referenced twice; MATERIALIZED and NOT MATERIALIZED override the default. BigQuery and Snowflake make their own choice per query. The rows are the same either way, so the writing rule stays: name a step once, read it wherever it's needed.

A CTE's name is the only documentation most readers will get, so it names what one row of it represents. Names like active_users and monthly_revenue say what a row is, and so does late_shipments. With cte1 or temp the reader has to go back into the parentheses to find out, and the same goes for data.

SQL is written in one order and evaluated in another. The text reads SELECT, FROM, WHERE, ORDER BY, LIMIT from top to bottom; the engine evaluates FROM first and SELECT close to last. The CTE from the previous section already showed the gap: active_set was complete before the outer query read its first row, even though the outer SELECT sat below it on the page. Alias errors in a long query trace back to that gap, and so does a surprising HAVING or a filter sitting in the wrong clause.

The order is a contract about results, not a description of the machine. An optimizer is free to push a WHERE predicate into an index scan, stop a sort early to satisfy a LIMIT, or flatten a CTE into the query that reads it, as long as the rows that come out match what the logical order would have produced. Reasoning about correctness uses the table above; reasoning about speed uses the query plan.

A column alias is created when SELECT evaluates its expression list, which is step 6. Any clause that runs earlier cannot reference it, because at that moment the alias does not exist. WHERE runs at step 3, so SELECT total * 1.2 AS gross ... WHERE gross > 100 fails with an unknown-column error in PostgreSQL and MySQL, and in SQL Server and BigQuery as well. An alias that shares its name with a stored column fails more quietly: WHERE reads the stored column instead. ORDER BY runs at step 7 and can sort by gross without restating the arithmetic.

The same rule decides where an aggregate may appear. SUM(total) is computed when groups are folded at step 4, so a filter on the sum belongs in HAVING at step 5, and WHERE SUM(total) > 100 is rejected because there are no groups yet when WHERE runs. SQLite and DuckDB are among the engines that accept an alias in WHERE; they silently substitute its expression. The query still evaluates in the standard order; the engine has only saved the typing.

WHERE total >= 50 ran before tax existed, so it filtered on the stored column and kept 3 of the 6 orders. SELECT then computed tax for those 3 rows only, and ORDER BY tax DESC sorted the finished rows: 18.0, 15.0, 12.0. Change ORDER BY tax to ORDER BY total and the order is identical, because tax is a fixed multiple of total; only one of those 2 spellings is available to WHERE.

When a long query returns the wrong number of rows, read it in the order the engine does rather than the order on the page. Start at FROM and name the row set. Apply WHERE and count the rows that survive; once the groups are folded, look at what SELECT has to work with. The wrong count is found at the step where the row set changed size in a way the author did not expect. That step is rarely SELECT, which changes columns rather than rows. The exception is DISTINCT, which collapses identical output rows at step 6 and belongs on the checklist whenever a count comes back too small.

A subquery is a SELECT nested inside another statement, and it predates the WITH clause by 2 decades of SQL. It appears in 3 positions, and the position decides what shape its result must have. In a comparison it must return 1 value. In an IN list it returns 1 column of any length. In FROM it returns a whole table, which the outer query reads under an alias. All 3 run to completion before the clause that contains them uses the result, exactly as a CTE does.

A parenthesised subquery that returns 1 row and 1 column can stand anywhere a single value can: compared in WHERE, or as a column in SELECT. The classic use is a comparison against an aggregate of the same table, which a plain WHERE can't write because the aggregate does not exist yet when WHERE runs. The subquery computes it in its own pass first.

Across the 6 orders the average is 50.0, so the inner query returns that single number and the outer WHERE becomes total > 50.0, keeping orders 1002 (75), 1005 (60) and 1006 (90). If the inner query returned 2 rows, the comparison would be between 1 value and 2, and the engine raises an error rather than guessing which to use. The blank below is the same construction over the practice database.

IN (subquery) tests a value against every row of a 1-column result. It's the inline form of the active-users filter from section 0: the inner query lists the ids of active users, and the outer WHERE keeps orders whose user_id appears in that list. The animation runs it over the same users and orders rows the CTE examples used.

The inner query returns user ids 98, 101 and 103. Orders 1001 and 1003 match, as do 1005 and 1006, so 4 of the 6 survive. Order 1002 belongs to Ben (99, inactive) and 1004 to Dev (102, inactive), so both drop. IN is safe with NULLs in the list; its negation NOT IN returns no rows at all if the list holds even 1 NULL, a trap the three-valued logic lesson shows on rows.

Both forms sit in one query here. The scalar subquery in the SELECT list is evaluated once and its value, 50.0, is repeated on all 4 output rows, while the IN subquery narrows the rows. Change the inner filter to active = 0 and the result flips to the 2 orders from Ben and Dev, with avg_total unchanged, because the scalar subquery reads all 6 orders regardless of the outer filter.

A subquery in FROM is a table with no name, so SQL requires an alias for it. Everything an outer query can do to a real table it can do to the derived table: filter it, sort it, join it. This is how a filter on an aggregate was written before HAVING and WITH were common, and it still reads well for a single stage.

The inner query folds 6 orders into 5 per-user rows, the alias t names that 5-row set, and the outer WHERE spent > 50 keeps 3 of them. The outer WHERE can reference spent because, to the outer query, spent is a stored column of t: it was computed in the inner query's own SELECT step, which finished before the outer FROM began.

The 2 forms always return identical rows, and their plans are often the same but not always. A subquery is planned inline with the query around it. A CTE may be materialized instead: PostgreSQL 12 and later do this for a CTE referenced twice, as section 0 described, and PostgreSQL before 12 materialized every CTE, so a filter in the outer query was never pushed into it. The choice is mostly about reading. One nested filter in WHERE reads better inline than as a 3-line block above the query; 2 stages, or 1 stage read twice, read better as CTEs. A subquery that references a column of the outer query is correlated, re-evaluated once per outer row, and belongs to the advanced tier of this track.

UNION stacks the result of 1 query on top of another into a single result set. The 2 queries must return the same number of columns, which are matched by position. Each position must hold compatible types. PostgreSQL and SQL Server reject an INTEGER over a TEXT, but MySQL and SQLite coerce it silently and hide the mistake. An INTEGER over a DECIMAL is widened. The output takes its column names from the first query, so names in the second are ignored. What the operator does with a row that appears in both inputs is the choice between UNION and UNION ALL.

Plain UNION is shorthand for UNION DISTINCT: after stacking, the engine removes any output row identical to another across every column. The animation stacks 3 customers on 3 suppliers, and Cara, id 3, is in both tables.

The union takes 6 input rows and returns 5. Cara appeared once in each input and survives once, because the duplicate check compares whole rows and (3, Cara) matches (3, Cara). A supplier named Cara with id 6 would have survived alongside her, since the rows would differ in a column. The removal is also not limited to rows from different inputs: 2 identical rows inside customers alone would collapse to 1.

UNION ALL stacks and stops. No row is compared with any other, so the output row count is the sum of the inputs.

All 6 rows come out, and Cara appears twice. The difference in cost is larger than the difference in rows suggests. To remove duplicates the engine sorts or hashes the full width of the result, so it has to see every row before it emits any; UNION ALL streams rows through as they arrive. On 2 inputs of 50 million rows each, the distinct pass is the most expensive step in the query, and it's paid even when the inputs share no rows at all.

Once rows are stacked, nothing in them records which query they came from. A literal in each SELECT list fixes that, and because columns match by position the literal must sit in the same slot in both queries.

The ORDER BY at the end sorts the stacked result and applies to the whole union, never to the second SELECT alone; a query that needs each input kept together sorts the final result by party_type first. LIMIT behaves the same way and caps the combined result. The practice database splits notification sends between push_notifs and push_notifs_2fa. Both tables have the same columns, which is the shape the next widget stacks.

A query without ORDER BY returns rows in whatever order the engine found cheapest, and that order isn't stable: it can change when someone adds an index or the plan is rewritten, and another replica may return a different one. ORDER BY makes the order part of the result. LIMIT keeps the first N rows of that order, and the pair answers every question of the form 'the top 5' or 'the 20 most recent', which is a large share of what a dashboard runs.

ORDER BY takes a list of expressions, each with its own direction. ASC is the default and sorts smallest first, DESC largest first. With 2 columns the second decides the order only among rows that tie on the first: ORDER BY country, total_spent DESC keeps each country's customers together and ranks them by spend inside the country. Any expression works, including an alias from SELECT, which is available because sorting is step 7 and aliases are born at step 6.

The animation sorts 6 customers by total_spent descending and keeps 3.

Eve (1500), Ben (1250) and Cara (980) come out, in that order. The engine does not need to finish sorting the other 3 to produce them: a LIMIT above a sort lets the planner keep only the 3 largest values seen so far in a bounded heap, which is why ORDER BY ... LIMIT 10 over 100 million rows finishes in seconds while a bare ORDER BY spills the whole table to disk. The switcher below runs 3 formulations over the same rows.

The second tab drops the ORDER BY, and the 3 rows it returns are whichever 3 the engine read first, the stored order on a table this small. Eve, the top spender, is absent. On a production table the set changes from run to run, and a report built on it goes wrong silently, because 3 rows did come back. LIMIT without ORDER BY is a sample, never a ranking.

The third tab adds OFFSET 3: skip 3 rows of the sorted result, then take 3, so page 2 holds Finn (760), Ann (420) and Dev (310). Page k of size n is LIMIT n OFFSET (k - 1) * n. The engine still sorts and then discards the skipped rows, so page 1,000 at 50 rows a page is a 50,000-row sort and discard, and every later page costs more. Ties break the pages too: with 2 customers both at 760 and only ORDER BY total_spent, the engine may put either first on each run, so a customer can appear on page 2 and again on page 3, or on neither.

The fix for ties is a unique tiebreaker column, almost always the primary key: ORDER BY total_spent DESC, id. The fix for deep pages is keyset pagination, which remembers the last row of the previous page and asks for the rows after it, so the engine seeks to that row instead of sorting and discarding everything above it.

NULL has no natural position in an ordering, and engines disagree: PostgreSQL sorts NULLs last ascending and first descending. MySQL and SQLite do the reverse. NULLS FIRST and NULLS LAST pin it where the engine supports them.