A view gives a query a name forever and for everybody. Often what you need is the opposite: to name an intermediate step here, now and only inside this query, without creating any object or asking anyone for permission. That's a common table expression —CTE— and you write it by putting WITH in front of the SELECT.

With them you'll keep two of the course's promises. The first comes from 07-04 and 07-05: readability breaks down past three levels of derived tables, and here you'll see the same query written both ways, side by side. The second comes from 03-06: a SELF JOIN walks one level of the hierarchy, and to walk a tree of unknown depth you need WITH RECURSIVE — with which you'll finally get GreenStore's full org chart with its level and its path, the three-hop referral chain that runs from Lucía to Núria, and a twelve-month series generated out of thin air.

1. WITH: the named subquery, moved to the front
2. Readability: three levels of derived tables versus three CTEs
3. Several chained CTEs: the query as a sequence of steps
4. CTE, view and derived table: which to use
5. Materialization: what changed in PostgreSQL 12
6. CTEs in INSERT, UPDATE and DELETE; the "move rows" pattern
7. WITH RECURSIVE: the anatomy
8. The three GreenStore cases
9. Infinite loops and how to protect yourself
10. Common Mistakes and Tips
11. Exercises
12. Conclusion

A CTE is a subquery given a name before the main query. It lives only for the duration of that statement and is then used as if it were a table.

It's exactly 07-04's canonical derived table, with the same figures, but read from top to bottom: first order_totals is defined, then it's used. The minimal syntax is WITH name1 AS ( SELECT ... ), name2 AS ( SELECT ... ) SELECT ... FROM name1 JOIN name2 ..., where name2 can use name1.

Four rules worth fixing from the start: every CTE needs a name (and can optionally rename its columns, WITH t (order_id, total) AS (...)); they're separated by commas, and WITH is written only once even if there are five of them; a CTE can refer to earlier ones, never to later ones (except with RECURSIVE, section 7); and a CTE can be used several times in the same query, which a derived table doesn't allow.

This is where 07-04's promise gets kept. The question: of the customers who have bought, which ones beat the average revenue per customer, and by how much? That's three steps —total per order, total per customer, average of those totals— and with derived tables they end up nested:

Count the parentheses, and notice that the same per-order totals computation appears twice, copied out, because a derived table can't be reused. Now the same thing with CTEs:

Five of the twelve customers with purchases beat the average of €60.66 (€727.95 across 12). The result is identical to the previous version; what's changed is that now it can be read, reviewed in a pull request and debugged step by step: just swap the final SELECT for SELECT * FROM customer_totals to see the intermediate result without touching anything else.

And there's a gain you can't see: order_totals is written once and used twice. In the nested version it was duplicated, with everything that means the day the amount formula changes.

That's the natural way to write complex analysis: you don't think in terms of one query, but of a sequence of transformations.

Each CTE reduces or transforms the granularity and has a name that says what it holds. If the final result doesn't add up, the diagnosis is mechanical: run SELECT COUNT(*) FROM order_totals (you should get 20), then customer_totals (12), and the level where the number isn't what you expected is where the bug is. It's 07-04's advice —"count the rows at each level"— now with the levels named. And name them after what they hold, not c1, c2, c3: a query with WITH sales_2025 AS ..., returns_2025 AS ..., net AS ... can be understood without reading the SQL.

The decision rule fits in three sentences: one level and throwaway → derived table; two or more steps, or the same step used twice → CTE; something other queries, other people or the BI team will use → view. And they're not mutually exclusive: a view can be defined with a WITH inside it.

This section corrects what almost every old tutorial says. Up to PostgreSQL 11, a CTE was an optimization barrier: the engine ran it whole, kept its result in memory and only then carried on, so a filter in the outer query couldn't be pushed inside. Writing WITH t AS (SELECT * FROM order_lines) SELECT * FROM t WHERE product_id = 15 computed all 47 rows only to throw nearly all of them away.

Since PostgreSQL 12 that's no longer the case. A CTE used only once and with no side effects is inlined into the main query, exactly like a view or a derived table, and the planner can push filters inwards. The penalty for writing readable queries is gone. The two keywords that force each behaviour:

Written as WITH order_totals AS MATERIALIZED ( ... ), the step is computed exactly once no matter how many times the main statement queries it — which is what you want when that step walks 47 lines and you use it in two different subqueries.

The way to know what's happening is to look, not to assume: if a CTE Scan node over a CTE name shows up in the EXPLAIN, it's been materialized; if it appears nowhere and you see the table scans directly, it's been inlined. The technique is 08-05's.

The inherited mistake. For years people were taught "use a CTE to force the execution order" and "don't use CTEs, they're slow". Both statements expired in PostgreSQL 12. If you need the barrier, ask for it explicitly with MATERIALIZED; if you don't, write the CTE because it reads better and pay nothing for it. In MySQL 8 and SQL Server CTEs are always inlined and the keyword doesn't exist; in Oracle there are equivalent hints (/*+ MATERIALIZE */).

A WITH can precede any statement, not just a SELECT. And there's more: a CTE can itself be an INSERT, UPDATE or DELETE with RETURNING, and its result can feed the next step. That's the pattern that solves something 05-04 made impossible in a single statement: moving rows from one table to another.

GreenStore wants to archive cancelled orders —today, only number 6— in a historical table:

One statement, one transaction, no window in which the row doesn't exist anywhere. The three properties that make it possible: every sub-statement sees the same snapshot of the database, the one from the start of the statement: the deleted CTE doesn't see the INSERT's effect, and the INSERT doesn't see the DELETE's effect on orders. The execution order isn't guaranteed, so writing to the same table from two branches of the same statement gives unpredictable results. And a CTE that writes always runs, even if the main query doesn't use it: it's the only exception to section 5's inlining.

A second, more everyday use: compute with WITH and update with the result. Bringing the catalogue price in line with the average price actually sold:

Seventeen products, the ones that have been sold at least once. And notice the advantage over 07-04's version: there you needed a WHERE EXISTS so the three never-sold products didn't get NULL; here the implicit JOIN with the CTE already leaves them out. (Reload the script after trying it.)

This is where 03-06's promise gets kept. A recursive CTE is a CTE that references itself, and it always has the same shape:

How it runs, which is the only thing you really need to understand:

Iteration by iteration, with GreenStore's org chart: the base produces Rosa; the first iteration looks for Rosa's children and produces Andrés, Beatriz and Daniel; the second looks for those three's children and produces Óscar, Laia, Marc and Irene; the third looks for those four's children, finds none, the working table comes out empty and the process ends. Total: 1 + 3 + 4 = 8 rows, the eight employees.

Three syntax details with traps in them: RECURSIVE goes immediately after WITH, once only, even if there are several CTEs and only one is recursive; UNION ALL doesn't remove duplicates and is the normal choice, whereas plain UNION removes them at every step —crude protection against cycles, but more expensive—; and the recursive term can reference the CTE only once, with no aggregates, no ORDER BY and no LIMIT inside.

The eight employees, with their depth and their chain of command, laid out as a tree thanks to ORDER BY path. Compare it with 03-06: there you needed two chained LEFT JOINs, the number of levels was hard-coded into the query and even so you only got as far as the "grandparent". Here the query doesn't know how many levels there are, and it would work just the same with fifteen.

Two one-line variants that are worth a lot: one person's subtree —change the base term's WHERE e.manager_id IS NULL to WHERE e.id = 2 and you get Andrés and his three subordinates, 4 rows—; and a stable ordering, because a path made of names depends on how the language sorts accents: accumulate a second column with padded ids, t.path_id || '.' || lpad(e.id::text, 5, '0'), and sort by that.

Recursion also works in the opposite direction: instead of going down from parents to children, going up from child to parent. Marketing's question is "who does Núria Bosch Ferrer ultimately come from?".

The three-hop chain 03-06 couldn't walk: 1 → 2 → 5 → 13. The row with hop = 3 is the origin of the branch, and you spot it because its referred_by_id is NULL. Flipping the JOIN around —ON ch.referred_by_id = d.id— and starting from customer 1, you get the opposite: all of Lucía's descendants, of which there are 5 (Carlos, Marta and Inés at level 1; Ana at 2; Núria at 3).

Recursion isn't only for trees: it also serves to produce rows out of nothing — the report's twelve months, with no table involved:

It returns twelve rows, from 2025-03 to 2026-02: the exact skeleton of the monthly report. In PostgreSQL this is written far better with generate_series (03-06), but generate_series doesn't exist in MySQL or SQLite, and this is the portable way to get the same thing. Notice where the stopping condition lives: inside the recursive term, in its own WHERE. Take it out and the query never ends.

The danger of recursion is a cycle in the data: if a data-entry error made customer 1 appear as referred by 13, query 8.2 would go round forever generating rows until it exhausted the temporary disk. Four defences, from the most handmade to the cleanest:

1. The path column with = ANY(...). You accumulate the path travelled in an array and reject anyone already in it:

2. CYCLE, from PostgreSQL 14 on, which is the same thing written by the engine. It goes after the CTE's closing parenthesis:

It means: watch the id column, mark TRUE in is_cycle on the row where repetition is detected and stop expanding down that branch; cycle_path holds the route travelled. It's shorter, faster and harder to get wrong.

3. A depth counter, handy when you also want to cap the level: WHERE t.level < 10 in the recursive term. 4. The emergency LIMIT: SELECT * FROM chain LIMIT 1000 stops execution once a thousand rows are reached, because PostgreSQL evaluates recursion lazily. It's a safety net for experimenting, not a solution: use it while developing a recursive query over data you don't know.

Dialect note:

Engine	Keyword	Detail
PostgreSQL	WITH RECURSIVE mandatory	CYCLE and SEARCH from 14 on. Without RECURSIVE, a "relation does not exist" error
SQL Server	plain WITH	RECURSIVE doesn't exist; MAXRECURSION caps it at 100 levels by default
MySQL 8 / MariaDB	WITH RECURSIVE	Limited by cte_max_recursion_depth (1000 by default)
SQLite	WITH RECURSIVE (the word is optional)	Full support, including UNION
Oracle	WITH RECURSIVE, or the classic CONNECT BY	CONNECT BY PRIOR ... START WITH ... predates the standard and is still very much alive, with LEVEL, SYS_CONNECT_BY_PATH and NOCYCLE

• Forgetting RECURSIVE. Without it, the CTE can't refer to itself: ERROR: relation "tree" does not exist. And it goes after WITH, not in front of the recursive CTE's name.
• Repeating WITH on every CTE (it's written once; the rest are separated by commas) or referring to a CTE defined further down (only the earlier ones are visible, apart from RECURSIVE's self-reference).
• Writing a recursive term with no stopping condition. The query never ends. In a tree, the stop is implicit (you run out of children); in a generated series, you have to write it yourself in the WHERE.
• Ignoring cycles in the data. A hierarchy with a loop hangs the query. CYCLE (PG 14+) or the path column with = ANY(...).
• Believing a CTE is always an optimization barrier. It was, up to PostgreSQL 11. From 12 on it's inlined if used once; if you want the barrier, ask for it with MATERIALIZED.
• Using UNION instead of UNION ALL "just in case". It removes duplicates at every step and costs quite a bit more. And writing to the same table from two branches of the same statement: the order isn't guaranteed and the result is unpredictable.
• Tip: write the query step by step and run it step by step. Replace the final SELECT with SELECT * FROM intermediate_step and check the row counts at each level: 47 → 20 → 12 → 1.
• Tip: in a recursive query, always start with the base term alone, check that it returns exactly the roots you expect and only then add the UNION ALL. And always accumulate level and path: they cost nothing and they're half the diagnosis when something goes wrong.

Rewrite 07-04's "two aggregates at different granularities" report using CTEs: per customer, number of orders, product revenue, shipping and total. It has to add up to €727.95 + €118.25 = €846.20. (1) Write it with two CTEs (per_order and per_customer) instead of two derived tables. (2) Add a third one computing the grand totals and show them alongside each customer's. (3) Will the CTEs be inlined or materialized? How would you check?

HR wants, for each employee, how many people they have below them in total (direct and indirect). (1) Write a recursive CTE returning every (manager, subordinate at any depth) pair. (2) Aggregate it to get the count per manager, including with 0 those who have nobody. (3) Check that Rosa comes out with 7 and Andrés with 3.

A colleague has written this to archive the reviews of discontinued products and doesn't understand the result:

1. What does the COUNT return, 12 or some other number? Why?
2. Has anything actually been deleted? How many rows?
3. Rewrite it so it really archives into a reviews_archive table and returns how many rows it moved.

Solution 1

1 and 2:

(First 2 of 12 rows.) The columns add up: 727.95 + 118.25 = €846.20. What solves the problem is the same as in 07-04 —aggregate each thing at its own granularity before joining— but now the steps have names and per_order is defined once and used twice (from per_customer and, through it, from grand_total).

3. per_order is used only once (from per_customer), so it will be inlined; per_customer is used twice, so PostgreSQL will materialize it — and here that's what you want, because computing it twice would mean walking 47 lines twice. You check it with EXPLAIN (ANALYZE, COSTS OFF): if a CTE Scan on per_customer node shows up, it's been materialized.

Solution 2

(First 4 of 8 rows; employees 5, 6, 7 and 8 also close with 0.) Rosa with 7 and Andrés with 3, as the exercise asked. Two ideas make it work: the base term starts from every employee at once, not just from the root, so each one builds their own subtree; and the COUNT(*) - 1 discounts the (x, x) row in which each employee counts themselves — which is precisely what lets the four with no subordinates show up with 0 instead of disappearing.

Solution 3

1. It returns 12, i.e. the count from before the delete. It's property 1 from section 6: every part of the statement sees the same snapshot, the one from the initial instant. The main SELECT doesn't see the effect of the CTE's DELETE.

2. Something was deleted, but zero rows. The only product with active = FALSE is 20 (Spirulina capsules) and it has no reviews, so the CTE returns the empty set. If the discontinued product were number 1, its 2 reviews would have been deleted and the COUNT would still have said 12 — which is where the exercise's real trap lies. 3. The correct version:

Now it works: the DELETE feeds the INSERT, the INSERT returns what it inserted and the final SELECT counts the result of the operation, not the state of a table. It's the complete "move rows" pattern, in one statement and one transaction.

WITH is the tool that turns SQL into something you can read:

• A CTE is a named subquery placed up front. It lives only for the statement, can be reused within it —which a derived table doesn't allow— and needs no permissions and leaves no trace. Against three levels of nested derived tables, three chained CTEs say the same thing with half the parentheses and are debugged step by step: 47 lines → 20 orders → 12 customers → 1 average, with 5 customers above the €60.66 average.
• CTE, view or derived table: a throwaway step, derived table; two or more steps or reuse, CTE; something other queries and other people will use, view.
• Since PostgreSQL 12 a CTE is no longer an optimization barrier: it's inlined if used once and materialized if used several times. AS MATERIALIZED and AS NOT MATERIALIZED force each behaviour, and the EXPLAIN tells you which is happening. Whatever pre-2019 tutorials say about this no longer holds.
• A WITH can precede an INSERT, UPDATE or DELETE, and a CTE can itself be a write with RETURNING: hence the "move rows" pattern, which deletes from one table and inserts what was deleted into another in a single atomic statement. Every branch sees the same snapshot.
• WITH RECURSIVE = base term UNION ALL recursive term, iterating until no new rows come out. With it: the full org chart with level and path (8 employees, 3 levels), the three-hop referral chain 1 → 2 → 5 → 13, and a 12-month series generated without generate_series. And cycles in the data, which hang it, are avoided with a path column and = ANY(...), with CYCLE ... SET ... USING ... from PostgreSQL 14 on, with a depth limit or, while you're experimenting, with an emergency LIMIT.

With CTEs you now know how to decompose a query and how to walk a structure. What's left is the gap 04-05 left wide open: aggregating without collapsing the rows. When you want the order total next to each of its lines, the percentage each product represents of overall revenue, each customer's position in a ranking, how much a month changed against the previous one or a quarter's moving average, a GROUP BY is no use: it collapses exactly what you want to keep. In the next lesson, window functions, you'll see the OVER clause that solves all of that at once, the anatomy of PARTITION BY / ORDER BY / frame, why you can't filter by a window function in the WHERE —the most frequent mistake of all— and GreenStore's rankings, running totals and moving averages computed without losing a single one of the 47 lines.