SQL 64 🛢️ Chaining Multiple CTEs
A single Common Table Expression is useful. Two CTEs that reference each other are a pipeline. The first CTE produces an intermediate result, the second consumes it and produces another, and the main query consumes the second. This chaining is what turns a CTE from a readability convenience into a query-organization tool. Each CTE is a named step, and the steps compose like functions in a program. The result is a query that reads as a sequence of transformations, each one building on the last.
The chaining is not just syntactic. The order of the CTEs determines the evaluation order, and the dependency graph determines which CTEs can reference which. A CTE can reference any CTE defined before it, but not any defined after it. This is the same forward-reference rule that applies to variables in a sequential program. The first CTE in the WITH clause has no dependencies; the second depends on the first; the third may depend on the first, the second, or both. The main query depends on all of them.
The performance implications are more subtle than the syntax. When a database evaluates a chain of CTEs, it may materialize each one as a temporary table, or it may inline them into the main query. The choice determines how many times the base tables are scanned and how much intermediate data is stored. In PostgreSQL 12 and later, a CTE referenced once is inlined by default, and a CTE referenced multiple times is materialized. This means a chain where each CTE is referenced exactly once behaves like a series of subqueries, and a chain where one CTE is referenced by two later CTEs is materialized once and reused. The distinction matters for performance, and the MATERIALIZED and NOT MATERIALIZED keywords give explicit control .
This chapter covers three areas. First, why chaining exists — the problem of a query with many steps and the pipeline model that CTEs provide. Second, how chaining works — the forward-reference rule, the dependency order, and the common patterns for multi-step transformations. Third, how to chain with performance in mind — the materialization behavior, the cases where chaining helps and where it hurts, and the patterns for avoiding repeated scans. The chapter ends with a complete example session, a quick reference, best practices, common pitfalls, real-world examples, and diagrams showing the CTE dependency graph.
Key point: A chain of CTEs is a sequence of named steps. Each CTE can reference the ones defined before it. The order of definition is the dependency order. The database may materialize or inline each CTE, and the behavior affects performance. Use MATERIALIZED or NOT MATERIALIZED to control it explicitly when the default is not optimal .
Why chaining exists
The multi-step problem. A complex query often requires several transformations: filter the raw data, aggregate it by one dimension, join the aggregates with a lookup table, apply a window function, and filter the windowed result. Each step is a distinct logical operation, and each step could be a subquery. But a query with five nested subqueries is hard to read and hard to debug. A chain of five CTEs is a sequence of named steps, and each step can be examined and tested independently. The query reads as a pipeline: “take the raw data, filter it, aggregate it, join it, window it, and select the final result.” This is the same decomposition that a programmer would use when breaking a large function into smaller ones .
The reuse problem. When a CTE is referenced by more than one later CTE, the chain provides reuse. The expensive computation is defined once and used many times. Without the chain, the computation would be duplicated in each subquery, and the database would scan the base table multiple times. The CTE is a named intermediate result that can be consumed by multiple downstream steps. This is the same argument for named functions in procedural code: define once, use many times .
The dependency problem. The order of the CTEs is the order of the transformations. The first CTE has no dependencies; the second depends on the first; the third may depend on both. The dependency graph is explicit in the WITH clause, and the forward-reference rule makes the order unambiguous. A reader can follow the chain from the first CTE to the main query, and each step is defined in terms of the steps before it. This is the same structure as a data pipeline, and it is why CTE chains are the standard tool for organizing complex queries .
The testing problem. Each CTE can be tested independently. A developer can run SELECT * FROM cte_name for any CTE in the chain and inspect the intermediate result. This is not possible with nested subqueries, because the intermediate results are not named. The ability to inspect each step is what makes CTE chains easier to debug. When the final result is wrong, the developer can check each step in order to find where the data diverges from expectations.
The materialization problem. When a CTE is materialized, it is computed once and stored in a temporary table. When it is inlined, it is substituted into the consuming query and computed as part of it. The choice determines performance. A materialized CTE is faster when it is expensive and referenced multiple times, because the computation is done once. An inlined CTE is faster when it is cheap and the consuming query can push down filters or joins into it. The default behavior depends on the database and the version, and the MATERIALIZED/NOT MATERIALIZED keywords give explicit control .
The trade-off. A chain of CTEs is more code than a single nested query. Each CTE has a name, a SELECT clause, and a definition. The overhead is small for a chain of two or three, but it grows with the number of steps. The chain is also a dependency graph, and a long chain can be difficult to follow if the steps are not well-named. The trade-off is between the readability of named steps and the compactness of a single expression. For queries with more than two steps, the named version is usually easier to maintain.
a. The syntax of multiple CTEs
Multiple CTEs are defined in a single WITH clause, separated by commas. The main query follows the WITH clause and references the CTEs by name .
WITH
first_cte AS (
SELECT ... FROM base_table WHERE ...
),
second_cte AS (
SELECT ... FROM first_cte WHERE ...
),
third_cte AS (
SELECT ... FROM first_cte JOIN second_cte ON ...
)
SELECT ... FROM third_cte;The first_cte has no dependencies. The second_cte references first_cte. The third_cte references both first_cte and second_cte. The main query references third_cte. The dependency graph is a directed acyclic graph, and the order of the definitions is a topological sort of that graph .
The forward-reference rule is strict: a CTE can reference any CTE defined before it, but not any defined after it. The following is an error:
WITH
first_cte AS (
SELECT * FROM second_cte -- ERROR: second_cte is not defined yet
),
second_cte AS (
SELECT ... FROM base_table
)
SELECT * FROM first_cte;The error is reported at parse time, and the fix is to reorder the CTEs so that the referenced CTE is defined first.
The main query can reference any of the CTEs, not just the last one. A chain does not have to be linear; the main query can join two CTEs directly:
WITH
a AS (SELECT ... FROM base),
b AS (SELECT ... FROM base)
SELECT a.*, b.*
FROM a JOIN b ON a.id = b.id;This is a fork rather than a chain, but it uses the same WITH clause. The a and b CTEs are independent, and the main query combines them.
b. Common chaining patterns
A chain of CTEs follows a small number of patterns. The pattern depends on the transformations and the dependencies between them.
The linear pipeline. Each CTE references the previous one, and the main query references the last one. This is the simplest pattern.
WITH
filtered AS (
SELECT * FROM orders WHERE order_date >= '2024-01-01'
),
aggregated AS (
SELECT customer_id, SUM(amount) AS total
FROM filtered
GROUP BY customer_id
),
ranked AS (
SELECT customer_id, total,
RANK() OVER (ORDER BY total DESC) AS rnk
FROM aggregated
)
SELECT customer_id, total, rnk
FROM ranked
WHERE rnk <= 10;The filtered CTE filters the orders. The aggregated CTE computes the total per customer. The ranked CTE adds a rank. The main query selects the top 10. Each step is a distinct transformation, and the chain is linear.
The fan-out pipeline. One CTE is referenced by two or more later CTEs. This is the pattern that benefits from materialization: the first CTE is computed once and reused.
WITH
monthly_sales AS (
SELECT DATE_TRUNC('month', order_date) AS month,
region,
SUM(amount) AS total
FROM orders
GROUP BY month, region
),
by_region AS (
SELECT region, SUM(total) AS region_total
FROM monthly_sales
GROUP BY region
),
by_month AS (
SELECT month, SUM(total) AS month_total
FROM monthly_sales
GROUP BY month
)
SELECT r.region, r.region_total, m.month, m.month_total
FROM by_region r
CROSS JOIN by_month m;The monthly_sales CTE is referenced by both by_region and by_month. Without materialization, the aggregation would be computed twice. With materialization, it is computed once and reused.
The join pipeline. Two CTEs are joined in a later CTE or in the main query. This is the pattern for combining data from multiple sources.
WITH
orders_summary AS (
SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS total
FROM orders
GROUP BY customer_id
),
customer_info AS (
SELECT id, name, email
FROM customers
)
SELECT c.name, c.email, o.order_count, o.total
FROM customer_info c
JOIN orders_summary o ON c.id = o.customer_id
ORDER BY o.total DESC;The two CTEs are independent, and the main query joins them. The join is explicit, and each CTE is a named source.
The recursive chain. A recursive CTE can be combined with non-recursive CTEs. The recursive CTE produces a hierarchy, and a non-recursive CTE joins it with other data.
WITH RECURSIVE
org_tree AS (
SELECT id, name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, t.level + 1
FROM employees e
JOIN org_tree t ON e.manager_id = t.id
),
employee_sales AS (
SELECT employee_id, SUM(amount) AS total_sales
FROM sales
GROUP BY employee_id
)
SELECT t.name, t.level, COALESCE(s.total_sales, 0) AS total_sales
FROM org_tree t
LEFT JOIN employee_sales s ON t.id = s.employee_id
ORDER BY t.level, t.name;The org_tree CTE is recursive and produces the hierarchy. The employee_sales CTE is non-recursive and produces the sales totals. The main query joins them. This is the standard pattern for combining hierarchical data with flat data.
c. Materialization in a chain
The materialization behavior of a chain depends on how many times each CTE is referenced and on the database’s default behavior.
Referenced once — inlined by default. In PostgreSQL 12+, a CTE referenced exactly once is inlined into the consuming query. This means the base table scan is pushed into the consuming query, and filters or joins in the consuming query can be applied earlier. The result is usually faster than materialization, because the intermediate result is not stored.
Referenced multiple times — materialized by default. A CTE referenced more than once is materialized. The result is computed once and stored, and each reference reads from the stored result. This avoids recomputing the CTE for each reference, which is usually faster when the CTE is expensive.
Explicit control. The MATERIALIZED keyword forces materialization, and the NOT MATERIALIZED keyword forces inlining. These are useful when the default behavior is not optimal.
WITH
expensive AS MATERIALIZED (
SELECT ... FROM large_table WHERE ...
),
cheap AS NOT MATERIALIZED (
SELECT ... FROM small_table
)
SELECT ... FROM expensive JOIN cheap ON ...;The expensive CTE is computed once and stored, because it is referenced multiple times or because the developer knows it is expensive. The cheap CTE is inlined, because it is cheap and the join can be pushed into it.
The chain performance problem. A long chain of inlined CTEs can produce a large query plan, because each CTE is substituted into the consuming query. If the chain is five levels deep and each level doubles the size of the plan, the final plan can be very large. Materializing one or more of the intermediate CTEs can reduce the plan size, at the cost of storing the intermediate result. The choice depends on the query and the data .
The recursive CTE is always materialized. A recursive CTE is evaluated iteratively, and the intermediate results are stored in a working table. The MATERIALIZED keyword is not needed, and the behavior is the same in every database that supports recursive CTEs.
Complete Example Session
-- ============================================
-- PART 1: A LINEAR CHAIN
-- ============================================
WITH
filtered AS (
SELECT * FROM orders WHERE order_date >= '2024-01-01'
),
aggregated AS (
SELECT customer_id, SUM(amount) AS total
FROM filtered
GROUP BY customer_id
),
ranked AS (
SELECT customer_id, total,
RANK() OVER (ORDER BY total DESC) AS rnk
FROM aggregated
)
SELECT customer_id, total, rnk
FROM ranked
WHERE rnk <= 10;
-- filtered → aggregated → ranked → main query
-- Each CTE references the previous one.
-- The chain is linear.
-- ============================================
-- PART 2: A FAN-OUT CHAIN
-- ============================================
WITH
monthly_sales AS (
SELECT DATE_TRUNC('month', order_date) AS month,
region,
SUM(amount) AS total
FROM orders
GROUP BY month, region
),
by_region AS (
SELECT region, SUM(total) AS region_total
FROM monthly_sales
GROUP BY region
),
by_month AS (
SELECT month, SUM(total) AS month_total
FROM monthly_sales
GROUP BY month
)
SELECT r.region, r.region_total, m.month, m.month_total
FROM by_region r
CROSS JOIN by_month m;
-- monthly_sales is referenced by both by_region and by_month.
-- It is materialized by default in PostgreSQL.
-- ============================================
-- PART 3: A JOIN CHAIN
-- ============================================
WITH
orders_summary AS (
SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS total
FROM orders
GROUP BY customer_id
),
customer_info AS (
SELECT id, name, email FROM customers
)
SELECT c.name, c.email, o.order_count, o.total
FROM customer_info c
JOIN orders_summary o ON c.id = o.customer_id
ORDER BY o.total DESC;
-- Two independent CTEs joined in the main query.
-- ============================================
-- PART 4: A RECURSIVE CHAIN
-- ============================================
WITH RECURSIVE
org_tree AS (
SELECT id, name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, t.level + 1
FROM employees e
JOIN org_tree t ON e.manager_id = t.id
),
employee_sales AS (
SELECT employee_id, SUM(amount) AS total_sales
FROM sales
GROUP BY employee_id
)
SELECT t.name, t.level, COALESCE(s.total_sales, 0) AS total_sales
FROM org_tree t
LEFT JOIN employee_sales s ON t.id = s.employee_id
ORDER BY t.level, t.name;
-- org_tree is recursive.
-- employee_sales is non-recursive.
-- The main query joins them.
-- ============================================
-- PART 5: THE FORWARD-REFERENCE RULE
-- ============================================
-- This is an ERROR:
-- WITH
-- a AS (SELECT * FROM b),
-- b AS (SELECT * FROM base)
-- SELECT * FROM a;
-- a references b before b is defined.
-- Fix: reorder so b is defined first.
-- ============================================
-- PART 6: THE MAIN QUERY REFERENCES EARLIER CTEs
-- ============================================
WITH
a AS (SELECT 1 AS x),
b AS (SELECT 2 AS y)
SELECT a.x, b.y
FROM a CROSS JOIN b;
-- The main query references both a and b.
-- Not every CTE must be in a linear chain.
-- ============================================
-- PART 7: EXPLICIT MATERIALIZATION
-- ============================================
WITH
expensive AS MATERIALIZED (
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
),
cheap AS NOT MATERIALIZED (
SELECT id, name FROM customers
)
SELECT c.name, e.total
FROM cheap c
JOIN expensive e ON c.id = e.customer_id;
-- expensive is computed once and stored.
-- cheap is inlined into the main query.
-- ============================================
-- PART 8: TESTING EACH CTE
-- ============================================
-- Each CTE can be tested independently:
-- SELECT * FROM filtered;
-- SELECT * FROM aggregated;
-- SELECT * FROM ranked;
-- This is not possible with nested subqueries.
-- ============================================
-- PART 9: THE DEPENDENCY GRAPH
-- ============================================
-- A chain of CTEs forms a DAG:
-- a → b → c → main
-- a → d → main
-- b → d
-- The order of definition must be a topological sort:
-- a, b, c, d, main
-- A CTE can reference any CTE defined before it.
-- ============================================
-- PART 10: THE COMPLETE PIPELINE
-- ============================================
WITH
raw_orders AS (
SELECT order_id, customer_id, order_date, amount
FROM orders
WHERE status = 'completed'
),
monthly_totals AS (
SELECT
customer_id,
DATE_TRUNC('month', order_date) AS month,
SUM(amount) AS monthly_total
FROM raw_orders
GROUP BY customer_id, month
),
customer_totals AS (
SELECT customer_id, SUM(monthly_total) AS lifetime_total
FROM monthly_totals
GROUP BY customer_id
),
top_customers AS (
SELECT customer_id, lifetime_total
FROM customer_totals
WHERE lifetime_total > 10000
)
SELECT
c.name,
t.lifetime_total,
m.month,
m.monthly_total
FROM top_customers t
JOIN customers c ON c.id = t.customer_id
JOIN monthly_totals m ON m.customer_id = t.customer_id
ORDER BY t.lifetime_total DESC, m.month;
-- raw_orders → monthly_totals → customer_totals → top_customers → main
-- monthly_totals is referenced by both customer_totals and the main query.
-- It is materialized once and reused.The ten parts show a linear chain, a fan-out chain, a join chain, a recursive chain, the forward-reference rule, the main query referencing earlier CTEs, explicit materialization, testing each CTE, the dependency graph, and the complete pipeline.
Quick Reference
Multiple CTE Syntax
| Form | Meaning |
|---|---|
WITH a AS (...), b AS (...) | Two CTEs |
WITH a AS (...), b AS (...), c AS (...) | Three CTEs |
WITH RECURSIVE a AS (...), b AS (...) | Recursive and non-recursive |
WITH a AS MATERIALIZED (...) | Force materialization |
WITH a AS NOT MATERIALIZED (...) | Force inlining |
Dependency Rules
| Rule | Behavior |
|---|---|
| Forward reference | Error; CTE must be defined before use |
| Backward reference | Allowed; a CTE can reference any earlier CTE |
| Main query | Can reference any CTE |
| Definition order | Must be a topological sort of the dependency graph |
Chaining Patterns
| Pattern | Description | Materialization |
|---|---|---|
| Linear | Each CTE references the previous | Inlined by default |
| Fan-out | One CTE referenced by multiple later CTEs | Materialized by default |
| Join | Two independent CTEs joined | Depends on references |
| Recursive | A recursive CTE combined with non-recursive | Recursive is always materialized |
Materialization Defaults (PostgreSQL 12+)
| References | Default behavior |
|---|---|
| Once | Inlined |
| Multiple times | Materialized |
MATERIALIZED | Always materialized |
NOT MATERIALIZED | Always inlined |
| Recursive | Always materialized |
Best Practices
✅ Do This:
-- Name CTEs after what they produce
WITH raw_orders AS (...), monthly_totals AS (...), top_customers AS (...) -- ✅
-- Order CTEs by dependency
WITH a AS (...), b AS (... FROM a), c AS (... FROM b) -- ✅
-- Use explicit materialization when needed
WITH expensive AS MATERIALIZED (...) -- ✅
-- Test each CTE independently
SELECT * FROM raw_orders; -- ✅
-- Keep chains focused
-- Each CTE should do one thing -- ✅
-- Use fan-out for reuse
-- One CTE, multiple consumers -- ✅❌ Don’t Do This:
-- Don't forward-reference
WITH a AS (SELECT * FROM b), b AS (...) -- ERROR -- ❌
-- Don't create a chain of trivial CTEs
WITH a AS (SELECT 1), b AS (SELECT 2), c AS (SELECT 3) -- ❌
-- Don't assume the default materialization is optimal
-- Check the execution plan -- ❌
-- Don't use a CTE for a one-time simple subquery
WITH x AS (SELECT * FROM t WHERE id = 1) SELECT * FROM x -- ❌
-- Don't name CTEs vaguely
WITH data AS (...), data2 AS (...) -- use descriptive names -- ❌Common Pitfalls
| Pitfall | Why It Happens | Fix |
|---|---|---|
| Forward reference error | CTE defined after use | Reorder definitions |
| Wrong results from inlining | Filter pushed down incorrectly | Use MATERIALIZED |
| Slow query from materialization | Large intermediate result stored | Use NOT MATERIALIZED |
| CTE referenced but not defined | Typo in name | Check the name |
| Chain too long to read | Too many CTEs | Combine or simplify |
| Recursive CTE never terminates | Cycle in data | Add depth limit or CYCLE |
| Plan too large | Too many inlined CTEs | Materialize an intermediate |
Real-World Examples
1. Linear Chain
WITH a AS (...), b AS (... FROM a), c AS (... FROM b) SELECT * FROM c;2. Fan-Out Chain
WITH base AS (...), x AS (... FROM base), y AS (... FROM base) SELECT ... FROM x JOIN y;3. Join Chain
WITH a AS (...), b AS (...) SELECT * FROM a JOIN b ON ...;4. Recursive Chain
WITH RECURSIVE tree AS (...), totals AS (...) SELECT * FROM tree LEFT JOIN totals ON ...;5. Explicit Materialization
WITH expensive AS MATERIALIZED (...) SELECT * FROM expensive;6. Explicit Inlining
WITH cheap AS NOT MATERIALIZED (...) SELECT * FROM cheap;7. Reuse via Fan-Out
WITH monthly AS (...), by_region AS (... FROM monthly), by_month AS (... FROM monthly) SELECT ...;8. Testing a CTE
SELECT * FROM intermediate_cte LIMIT 10;9. Pipeline with Window Function
WITH raw AS (...), aggregated AS (... FROM raw), windowed AS (SELECT *, ROW_NUMBER() OVER (...) FROM aggregated) SELECT * FROM windowed;10. Complete Analytics Pipeline
WITH raw AS (...), monthly AS (... FROM raw), lifetime AS (... FROM monthly), top AS (... FROM lifetime) SELECT ... FROM top JOIN monthly ON ...;Visual
The CTE Dependency Graph
┌──────────────────────────────────────────────────────────────┐
│ CTE DEPENDENCY GRAPH │
│ │
│ WITH │
│ a AS (SELECT ... FROM base), │
│ b AS (SELECT ... FROM a), │
│ c AS (SELECT ... FROM a JOIN b ON ...), │
│ d AS (SELECT ... FROM c) │
│ SELECT * FROM d; │
│ │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ base │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ a ──────────┐ │ │
│ │ │ │ │ │
│ │ ▼ ▼ │ │
│ │ b c (joins a and b) │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ d │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ main query │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ The order of definition must be a topological sort: │
│ a, b, c, d │
│ │
│ Each CTE can reference the ones before it. │
│ The forward-reference rule makes the order unambiguous. │
│ │
└──────────────────────────────────────────────────────────────┘Linear vs Fan-Out
┌──────────────────────────────────────────────────────────────┐
│ LINEAR vs FAN-OUT │
│ │
│ LINEAR: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ a ──► b ──► c ──► main │ │
│ │ │ │
│ │ Each CTE referenced once. │ │
│ │ Inlined by default in PostgreSQL 12+. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ FAN-OUT: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ ┌──► b ──┐ │ │
│ │ a ─────┤ ├──► main │ │
│ │ └──► c ──┘ │ │
│ │ │ │
│ │ a is referenced twice. │ │
│ │ Materialized by default in PostgreSQL 12+. │ │
│ │ The computation is done once and reused. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ The fan-out is where chaining provides reuse. │
│ The linear chain provides readability. │
│ │
└──────────────────────────────────────────────────────────────┘Materialization in a Chain
┌──────────────────────────────────────────────────────────────┐
│ MATERIALIZATION IN A CHAIN │
│ │
│ WITHOUT MATERIALIZATION (inlined): │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ a ──► b ──► c ──► main │ │
│ │ │ │
│ │ Each CTE is substituted into the next. │ │
│ │ The base table is scanned once. │ │
│ │ The plan is a single large query. │ │
│ │ Filters can be pushed down. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ WITH MATERIALIZATION: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ a ──► [temp table] ──► b ──► c ──► main │ │
│ │ │ │
│ │ a is computed once and stored. │ │
│ │ b reads from the temp table. │ │
│ │ The intermediate result is stored in memory or disk.│ │
│ │ Reuse is efficient when a is referenced multiple │ │
│ │ times. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ The default depends on the number of references. │
│ MATERIALIZED / NOT MATERIALIZED gives explicit control. │
│ │
└──────────────────────────────────────────────────────────────┘The Complete Pipeline
┌──────────────────────────────────────────────────────────────┐
│ THE COMPLETE PIPELINE │
│ │
│ raw_orders │
│ │ Filter: status = 'completed' │
│ ▼ │
│ monthly_totals │
│ │ Aggregate: SUM by customer, month │
│ ├──────────────┐ │
│ ▼ ▼ │
│ customer_totals (main query uses monthly_totals) │
│ │ Aggregate: SUM by customer │
│ ▼ │
│ top_customers │
│ │ Filter: lifetime_total > 10000 │
│ ▼ │
│ main query │
│ Join: customers, top_customers, monthly_totals │
│ Order: lifetime_total DESC, month │
│ │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ monthly_totals is referenced twice. │ │
│ │ It is materialized once and reused. │ │
│ │ This is the fan-out pattern. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
└──────────────────────────────────────────────────────────────┘Summary
| Item | Value |
|---|---|
| Multiple CTEs | Separated by commas in a single WITH clause |
| Forward reference | Error; CTE must be defined before use |
| Backward reference | Allowed; a CTE can reference any earlier CTE |
| Dependency order | Must be a topological sort of the DAG |
| Linear chain | Each CTE referenced once; inlined by default |
| Fan-out | One CTE referenced multiple times; materialized by default |
MATERIALIZED | Forces materialization |
NOT MATERIALIZED | Forces inlining |
| Recursive CTE | Always materialized; evaluated iteratively |
| Testing | Each CTE can be tested independently |
Key takeaways:
- A chain of CTEs is a pipeline of named steps. Each CTE is a distinct transformation, and the chain reads as a sequence of operations. The order of definition is the dependency order, and the forward-reference rule makes it unambiguous .
- A CTE can reference any CTE defined before it. The forward-reference rule is strict: a CTE cannot reference a CTE defined after it. The definition order must be a topological sort of the dependency graph .
- The linear chain is the simplest pattern. Each CTE references the previous one, and the main query references the last one. In PostgreSQL 12+, each CTE is inlined by default, and the plan is a single large query.
- The fan-out pattern provides reuse. One CTE is referenced by multiple later CTEs. The CTE is materialized by default, so the computation is done once and reused. This is where chaining provides the most value .
- Materialization behavior depends on the database and the version. In PostgreSQL 12+, a CTE referenced once is inlined, and a CTE referenced multiple times is materialized. The
MATERIALIZEDandNOT MATERIALIZEDkeywords give explicit control . - Recursive CTEs are always materialized. The recursion is evaluated iteratively, and the intermediate results are stored in a working table. This is the same in every database that supports recursive CTEs.
- Each CTE can be tested independently. Running
SELECT * FROM cte_namefor any CTE in the chain shows the intermediate result. This is not possible with nested subqueries, and it is a significant debugging advantage . - A chain that is too long becomes hard to follow. The chain is a tool for organizing complex queries, but a chain of ten CTEs is not better than a chain of five. Keep each CTE focused, and combine steps that do not need to be separate.
Remember: A chain of CTEs is a sequence of named steps. Each step is a CTE, and each CTE can reference the ones defined before it. The order of definition is the dependency order, and the forward-reference rule makes the structure unambiguous. The linear chain is the simplest pattern; the fan-out is where reuse happens. Materialization behavior affects performance, and the MATERIALIZED/NOT MATERIALIZED keywords give explicit control. Each CTE can be tested independently, which makes the chain easier to debug than a nested subquery. For queries with multiple steps, the CTE chain is the standard tool for organizing the logic.
Stop using slow, ad-bloated tool sites! 🤮
🔎 Search “KandZ Tools” on Google to use many professional utilities for free.
KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)
⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free
🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!