SQL 63 🛢️ Common Table Expressions (CTE) with WITH
A Common Table Expression is a named temporary result set that exists for the duration of a single query. It is defined with the WITH keyword, and it can be referenced by name anywhere in the query that follows. The CTE is not a view — it is not stored in the database schema — and it is not a subquery in the traditional sense, because it is defined before the main query and can be referenced multiple times. The feature is part of the SQL standard, and it is supported by PostgreSQL, MySQL 8.0+, SQL Server, Oracle, and SQLite .
The primary value of a CTE is readability. A complex query with nested subqueries is read from the inside out: the innermost subquery is evaluated first, and the outer queries build on it. A CTE is read from the top down: the first CTE is defined, then the second, then the main query. The logic is the same, but the order of reading matches the order of computation. This is why CTEs are often described as “named subqueries that make a query easier to read.” For a query with two or three levels of nesting, the difference is significant .
The secondary value is reuse. A subquery that appears twice in a query is written twice. A CTE is written once and referenced by name. The database may or may not materialize the CTE — it may recompute it for each reference, or it may store the result in a temporary table — but the query is shorter and the logic is defined in one place. In some databases, the MATERIALIZED and NOT MATERIALIZED keywords control this behavior explicitly .
Recursive CTEs are a third capability. A recursive CTE references itself, and the database evaluates it iteratively until no new rows are produced. This is the standard way to query hierarchical data: an organizational chart, a bill of materials, a category tree, or a graph traversal. The recursion has two parts: the anchor member, which is the base case, and the recursive member, which references the CTE by name. The two are combined with UNION ALL or UNION .
This chapter covers three areas. First, why CTEs exist — the readability problem with nested subqueries and the reuse problem with repeated subqueries. Second, how non-recursive CTEs work — the WITH syntax, multiple CTEs, and the interaction with SELECT, INSERT, UPDATE, and DELETE. Third, how recursive CTEs work — the anchor and recursive members, the termination condition, and the standard hierarchical query patterns. The chapter ends with a complete example session, a quick reference, best practices, common pitfalls, real-world examples, and diagrams showing the CTE structure.
Key point: A Common Table Expression is a named temporary result set defined with WITH. It exists only for the duration of the query, and it can be referenced multiple times. A recursive CTE references itself and is evaluated iteratively. CTEs do not create permanent objects and do not change the database schema .
Why CTEs exist
The nested-subquery problem. A query with a subquery in the FROM clause, another subquery in the WHERE clause, and a third in the SELECT list is read from the inside out. The innermost subquery is evaluated first, and the reader must hold each intermediate result in their head while reading the outer query. The nesting is often several levels deep, and the indentation makes the structure hard to follow. A CTE flattens the structure. Each subquery becomes a named block at the top of the query, and the main query references the names. The reading order matches the evaluation order, and the intermediate results are named .
The repeated-subquery problem. A subquery that appears in two places is written twice. If the logic changes, both copies must be updated. If the copies diverge, the query produces incorrect results. A CTE is written once and referenced by name, so there is only one definition. This is the same argument for functions and variables in procedural code: define once, use many times. The database may or may not optimize away the duplication, but the source code no longer contains it .
The hierarchical-data problem. Some data is recursive: an employee table with a manager_id column that references the same table, a category table with a parent_id, a bill of materials with components that are themselves assemblies. Querying this data requires recursion: start with the roots, find their children, find the children of the children, and so on. Before recursive CTEs, this required a stored procedure or application-level iteration. The recursive CTE, introduced in SQL:1999, made the query possible in a single SQL statement. The anchor member defines the base case, and the recursive member defines the step. The database iterates until the recursion produces no new rows .
The readability problem. A query that uses CTEs is easier to read, test, and modify. Each CTE is a named unit that can be understood in isolation. The main query reads like a sequence of steps: “take the first CTE, join it with the second, filter by the third.” The alternative — a query with nested subqueries — reads like a single complex expression. The difference is the same as the difference between a function with named intermediate variables and a function with one giant expression. Both are correct; the named version is easier to debug .
The statement-scope problem. A CTE is not a view. It is not stored in the database schema, and it does not persist beyond the query. It is scoped to the statement that defines it, and it can be referenced only within that statement. This makes it safe to use for temporary transformations that do not need to be reused. A view would persist and could affect other queries; a CTE disappears when the query finishes .
The trade-off. CTEs are not always faster than subqueries. In some databases, a CTE is materialized as a temporary table, and the materialization costs time and memory. In PostgreSQL before version 12, CTEs were always materialized, which could make a query slower than the equivalent subquery. PostgreSQL 12 changed this: CTEs are now inlined by default when they are referenced once, and the MATERIALIZED keyword forces materialization. The trade-off is between the readability of the named blocks and the potential performance cost of materialization. For queries where performance is critical, the execution plan should be examined, and the MATERIALIZED/NOT MATERIALIZED keywords used explicitly .
a. Non-recursive CTEs
A non-recursive CTE is defined with the WITH keyword, followed by a name and a query in parentheses. The main query follows, and it references the CTE by name .
WITH regional_sales AS (
SELECT region, SUM(amount) AS total_sales
FROM orders
GROUP BY region
)
SELECT region, total_sales
FROM regional_sales
WHERE total_sales > 1000;The CTE regional_sales computes the total sales by region. The main query selects from the CTE and filters by the total. The CTE is a named result set that exists only for this query.
Multiple CTEs are separated by commas:
WITH regional_sales AS (
SELECT region, SUM(amount) AS total_sales
FROM orders
GROUP BY region
),
top_regions AS (
SELECT region
FROM regional_sales
WHERE total_sales > 1000
)
SELECT o.region, o.product, SUM(o.amount) AS product_sales
FROM orders o
JOIN top_regions t ON o.region = t.region
GROUP BY o.region, o.product;The second CTE references the first. The main query references the second. Each CTE can reference the CTEs defined before it, but not the ones defined after it. This is the dependency order: a CTE is defined before it is used .
A CTE can be used with INSERT, UPDATE, and DELETE:
WITH expensive_products AS (
SELECT id FROM products WHERE price > 100
)
DELETE FROM order_items
WHERE product_id IN (SELECT id FROM expensive_products);The CTE is defined, and the DELETE statement references it. The CTE is scoped to the entire statement, so it is available to the modifying clause.
A CTE can also be used with INSERT ... SELECT:
WITH new_orders AS (
SELECT customer_id, 'pending' AS status FROM customers WHERE active = true
)
INSERT INTO orders (customer_id, status)
SELECT customer_id, status FROM new_orders;The CTE produces the rows, and the INSERT writes them. This is the standard pattern for inserting derived data .
b. Recursive CTEs
A recursive CTE references itself. The syntax uses WITH RECURSIVE (or just WITH in some databases, where recursion is inferred), and the CTE body has two parts joined by UNION ALL or UNION :
WITH RECURSIVE employee_hierarchy AS (
-- Anchor member: the roots
SELECT id, name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive member: references the CTE
SELECT e.id, e.name, e.manager_id, eh.level + 1
FROM employees e
JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy ORDER BY level, name;The anchor member is the base case. It selects the roots of the hierarchy — the rows where manager_id is NULL. The recursive member references the CTE by name and joins the employees table to it. The join condition e.manager_id = eh.id finds the children of the rows already in the CTE. The level column is incremented at each step. The database evaluates the anchor member first, then the recursive member, then the recursive member again on the new rows, until the recursive member produces no new rows. The result is the full hierarchy with a level for each row .
The termination condition is implicit: the recursion stops when the recursive member produces no new rows. If the data has a cycle — a row that is its own ancestor — the recursion never terminates. The CYCLE clause (SQL:1999, supported in PostgreSQL 14+ and some other databases) detects cycles and stops the recursion. Without it, the query must include a depth limit or a path check .
A recursive CTE can be used for many hierarchical patterns:
Bill of materials:
WITH RECURSIVE bom AS (
SELECT part_id, component_id, quantity, 1 AS level
FROM parts
WHERE part_id = 'A'
UNION ALL
SELECT p.part_id, p.component_id, p.quantity, b.level + 1
FROM parts p
JOIN bom b ON p.part_id = b.component_id
)
SELECT * FROM bom;Graph traversal:
WITH RECURSIVE paths AS (
SELECT source, target, 1 AS depth
FROM edges
WHERE source = 'A'
UNION ALL
SELECT p.source, e.target, p.depth + 1
FROM paths p
JOIN edges e ON p.target = e.source
WHERE p.depth < 10
)
SELECT * FROM paths;The WHERE p.depth < 10 clause prevents infinite recursion in a graph with cycles. The depth limit is a common safeguard when the CYCLE clause is not available.
Date series generation:
WITH RECURSIVE dates AS (
SELECT DATE '2024-01-01' AS day
UNION ALL
SELECT day + INTERVAL '1 day'
FROM dates
WHERE day < DATE '2024-01-31'
)
SELECT * FROM dates;The anchor member is the start date. The recursive member adds one day, and the WHERE clause stops at the end date. This is the standard way to generate a series of dates or numbers without a numbers table .
c. Materialization and performance
A CTE can be materialized (computed once and stored in a temporary table) or inlined (substituted into the main query and computed as part of it). The behavior depends on the database and the version .
In PostgreSQL 12 and later, a CTE that is referenced once is inlined by default. A CTE that is referenced multiple times is materialized by default. The MATERIALIZED keyword forces materialization:
WITH expensive_cte AS MATERIALIZED (
SELECT ... FROM large_table WHERE ...
)
SELECT * FROM expensive_cte;The NOT MATERIALIZED keyword forces inlining:
WITH simple_cte AS NOT MATERIALIZED (
SELECT ... FROM small_table
)
SELECT * FROM simple_cte;Materialization is beneficial when the CTE is expensive to compute and is referenced multiple times, or when the CTE contains a VOLATILE function that must be evaluated once. Inlining is beneficial when the CTE is cheap and the main query can push down filters or joins into it. The default behavior is usually correct, but the keywords give explicit control .
In MySQL 8.0, CTEs are always materialized if they are referenced more than once, and inlined if referenced once. In SQL Server, CTEs are always inlined; they are not materialized unless the query plan chooses to do so. The behavior varies, and the execution plan is the definitive source of truth .
Recursive CTEs are always evaluated iteratively. The anchor member is evaluated once, and the recursive member is evaluated repeatedly until it produces no new rows. The intermediate results are stored in a working table, and each iteration reads from the previous result. This is the standard recursive evaluation strategy, and it is the same in every database that supports recursive CTEs .
Complete Example Session
-- ============================================
-- PART 1: A BASIC CTE
-- ============================================
WITH regional_sales AS (
SELECT region, SUM(amount) AS total_sales
FROM orders
GROUP BY region
)
SELECT region, total_sales
FROM regional_sales
WHERE total_sales > 1000;
-- The CTE computes the total by region.
-- The main query filters the result.
-- ============================================
-- PART 2: MULTIPLE CTEs
-- ============================================
WITH regional_sales AS (
SELECT region, SUM(amount) AS total_sales
FROM orders
GROUP BY region
),
top_regions AS (
SELECT region
FROM regional_sales
WHERE total_sales > 1000
)
SELECT o.region, o.product, SUM(o.amount) AS product_sales
FROM orders o
JOIN top_regions t ON o.region = t.region
GROUP BY o.region, o.product;
-- top_regions references regional_sales.
-- The main query references top_regions.
-- ============================================
-- PART 3: CTE WITH INSERT
-- ============================================
WITH new_orders AS (
SELECT customer_id, 'pending' AS status
FROM customers
WHERE active = true
)
INSERT INTO orders (customer_id, status)
SELECT customer_id, status FROM new_orders;
-- The CTE produces the rows.
-- The INSERT writes them.
-- ============================================
-- PART 4: CTE WITH UPDATE
-- ============================================
WITH expensive_products AS (
SELECT id FROM products WHERE price > 100
)
UPDATE order_items
SET priority = 'high'
WHERE product_id IN (SELECT id FROM expensive_products);
-- ============================================
-- PART 5: CTE WITH DELETE
-- ============================================
WITH inactive_customers AS (
SELECT id FROM customers WHERE last_order < NOW() - INTERVAL '1 year'
)
DELETE FROM orders
WHERE customer_id IN (SELECT id FROM inactive_customers);
-- ============================================
-- PART 6: RECURSIVE CTE — EMPLOYEE HIERARCHY
-- ============================================
WITH RECURSIVE employee_hierarchy AS (
-- Anchor: the roots
SELECT id, name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive: the children
SELECT e.id, e.name, e.manager_id, eh.level + 1
FROM employees e
JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT id, name, manager_id, level
FROM employee_hierarchy
ORDER BY level, name;
-- The anchor selects the CEO.
-- The recursive member selects direct reports.
-- The recursion continues until no more reports exist.
-- ============================================
-- PART 7: RECURSIVE CTE — BILL OF MATERIALS
-- ============================================
WITH RECURSIVE bom AS (
SELECT part_id, component_id, quantity, 1 AS level
FROM parts
WHERE part_id = 'A'
UNION ALL
SELECT p.part_id, p.component_id, p.quantity, b.level + 1
FROM parts p
JOIN bom b ON p.part_id = b.component_id
)
SELECT part_id, component_id, quantity, level
FROM bom
ORDER BY level, part_id;
-- ============================================
-- PART 8: RECURSIVE CTE — DATE SERIES
-- ============================================
WITH RECURSIVE dates AS (
SELECT DATE '2024-01-01' AS day
UNION ALL
SELECT day + INTERVAL '1 day'
FROM dates
WHERE day < DATE '2024-01-31'
)
SELECT day FROM dates;
-- Generates every day in January 2024.
-- ============================================
-- PART 9: MATERIALIZATION CONTROL
-- ============================================
-- Force materialization
WITH expensive_cte AS MATERIALIZED (
SELECT ... FROM large_table WHERE ...
)
SELECT * FROM expensive_cte;
-- Force inlining
WITH simple_cte AS NOT MATERIALIZED (
SELECT ... FROM small_table
)
SELECT * FROM simple_cte;
-- PostgreSQL 12+ inlines CTEs referenced once by default.
-- ============================================
-- PART 10: THE COMPLETE REPORT
-- ============================================
WITH monthly_sales AS (
SELECT
DATE_TRUNC('month', order_date) AS month,
region,
SUM(amount) AS total
FROM orders
GROUP BY month, region
),
running_totals AS (
SELECT
month,
region,
total,
SUM(total) OVER (PARTITION BY region ORDER BY month) AS running_total
FROM monthly_sales
)
SELECT
month,
region,
total,
running_total,
ROUND(total / running_total * 100, 2) AS pct_of_running
FROM running_totals
ORDER BY region, month;
-- monthly_sales computes the monthly totals.
-- running_totals adds the running total.
-- The main query computes the percentage.The ten parts show a basic CTE, multiple CTEs, CTE with INSERT, UPDATE, and DELETE, recursive CTE for hierarchy, bill of materials, date series, materialization control, and the complete report.
Quick Reference
Basic Syntax
| Form | Meaning |
|---|---|
WITH name AS (query) SELECT ... | Single CTE |
WITH a AS (...), b AS (...) SELECT ... | Multiple CTEs |
WITH RECURSIVE name AS (...) SELECT ... | Recursive CTE |
WITH name AS MATERIALIZED (...) | Force materialization |
WITH name AS NOT MATERIALIZED (...) | Force inlining |
Recursive CTE Structure
| Part | Meaning |
|---|---|
| Anchor member | Base case, no self-reference |
UNION ALL | Combines anchor and recursive |
| Recursive member | References the CTE by name |
| Termination | When recursive member produces no new rows |
CYCLE clause | Detects cycles (PostgreSQL 14+) |
Common Patterns
| Pattern | Use case |
|---|---|
| Multiple CTEs | Break complex query into steps |
| CTE with INSERT | Insert derived data |
| CTE with UPDATE | Update based on computed set |
| CTE with DELETE | Delete based on computed set |
| Recursive hierarchy | Employee, category, org chart |
| Recursive graph | Path traversal, shortest path |
| Date series | Generate missing dates |
Database Support
| Database | CTE | Recursive | MATERIALIZED |
|---|---|---|---|
| PostgreSQL | ✅ | ✅ | ✅ (12+) |
| MySQL | ✅ (8.0+) | ✅ (8.0+) | Inferred |
| SQL Server | ✅ | ✅ | Inferred |
| Oracle | ✅ | ✅ | Inline hints |
| SQLite | ✅ (3.8.3+) | ✅ | Inferred |
Best Practices
✅ Do This:
-- Name CTEs after what they produce
WITH monthly_totals AS (...) -- ✅
-- Use CTEs to break complex queries into steps
WITH step1 AS (...), step2 AS (...) -- ✅
-- Use recursive CTEs for hierarchical data
WITH RECURSIVE tree AS (...) -- ✅
-- Add a depth limit to prevent infinite recursion
WHERE depth < 10 -- ✅
-- Use MATERIALIZED when the CTE is expensive and referenced multiple times
WITH cte AS MATERIALIZED (...) -- ✅
-- Use CTEs with INSERT/UPDATE/DELETE for derived data
WITH new_rows AS (...) INSERT INTO ... SELECT ... FROM new_rows -- ✅❌ Don’t Do This:
-- Don't use a CTE for a one-time simple subquery
WITH x AS (SELECT 1) SELECT * FROM x -- unnecessary -- ❌
-- Don't reference a CTE defined later
WITH a AS (SELECT * FROM b), b AS (...) -- error: b not defined yet -- ❌
-- Don't forget the termination condition in recursive CTEs
-- (infinite recursion) -- ❌
-- Don't assume the CTE is materialized
-- (behavior depends on the database and version) -- ❌
-- Don't use CTEs for temporary tables that need to persist
-- (use CREATE TEMPORARY TABLE instead) -- ❌Common Pitfalls
| Pitfall | Why It Happens | Fix |
|---|---|---|
| CTE referenced later fails | Forward reference | Define CTEs in dependency order |
| Recursive CTE never terminates | No termination condition | Add a depth limit or CYCLE clause |
| Performance worse than subquery | CTE materialized unnecessarily | Try NOT MATERIALIZED |
| CTE not visible in subquery | Scope is the statement | CTE is visible everywhere in the statement |
WITH before INSERT syntax error | Wrong placement | WITH comes before the statement |
| Duplicate column names in CTE | No alias | Use explicit aliases |
Recursive CTE with UNION instead of UNION ALL | Deduplication overhead | Use UNION ALL unless dedup is needed |
Real-World Examples
1. Basic CTE
WITH regional_sales AS (SELECT region, SUM(amount) FROM orders GROUP BY region)
SELECT * FROM regional_sales;2. Multiple CTEs
WITH a AS (...), b AS (...)
SELECT * FROM a JOIN b ON ...;3. CTE with INSERT
WITH new_rows AS (...) INSERT INTO target SELECT * FROM new_rows;4. CTE with UPDATE
WITH computed AS (...) UPDATE target SET ... WHERE id IN (SELECT id FROM computed);5. Recursive Hierarchy
WITH RECURSIVE tree AS (
SELECT id, parent_id, 1 FROM nodes WHERE parent_id IS NULL
UNION ALL
SELECT n.id, n.parent_id, t.level + 1 FROM nodes n JOIN tree t ON n.parent_id = t.id
)
SELECT * FROM tree;6. Date Series
WITH RECURSIVE dates AS (
SELECT DATE '2024-01-01' AS d
UNION ALL
SELECT d + 1 FROM dates WHERE d < DATE '2024-01-31'
)
SELECT * FROM dates;7. Bill of Materials
WITH RECURSIVE bom AS (
SELECT part_id, component_id, 1 FROM parts WHERE part_id = 'A'
UNION ALL
SELECT p.part_id, p.component_id, b.level + 1 FROM parts p JOIN bom b ON p.part_id = b.component_id
)
SELECT * FROM bom;8. Graph Traversal
WITH RECURSIVE paths AS (
SELECT source, target, 1 FROM edges WHERE source = 'A'
UNION ALL
SELECT p.source, e.target, p.depth + 1 FROM paths p JOIN edges e ON p.target = e.source WHERE p.depth < 10
)
SELECT * FROM paths;9. Materialization Control
WITH cte AS MATERIALIZED (...) SELECT * FROM cte;10. CTE with Window Function
WITH monthly AS (...), running AS (SELECT *, SUM(total) OVER (...) FROM monthly)
SELECT * FROM running;Visual
CTE Structure
┌──────────────────────────────────────────────────────────────┐
│ CTE STRUCTURE │
│ │
│ WITH cte_name AS ( │
│ SELECT ... │
│ FROM ... │
│ WHERE ... │
│ ) │
│ SELECT ... │
│ FROM cte_name │
│ WHERE ...; │
│ │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ WITH clause Main query │ │
│ │ ┌──────────────┐ ┌──────────────────────┐ │ │
│ │ │ cte_name │ │ SELECT ... │ │ │
│ │ │ (query) │───►│ FROM cte_name │ │ │
│ │ └──────────────┘ └──────────────────────┘ │ │
│ │ │ │
│ │ The CTE is defined first, then referenced. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ Reading order = evaluation order. │
│ The CTE is a named temporary result set. │
│ │
└──────────────────────────────────────────────────────────────┘Multiple CTEs
┌──────────────────────────────────────────────────────────────┐
│ MULTIPLE CTEs │
│ │
│ WITH a AS ( │
│ SELECT ... FROM base │
│ ), │
│ b AS ( │
│ SELECT ... FROM a │
│ ), │
│ c AS ( │
│ SELECT ... FROM a JOIN b ON ... │
│ ) │
│ SELECT ... FROM c; │
│ │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ base │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ a ──────────┐ │ │
│ │ │ │ │ │
│ │ ▼ ▼ │ │
│ │ b c (joins a and b) │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ main query │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ Each CTE can reference the CTEs defined before it. │
│ The dependency order is top to bottom. │
│ │
└──────────────────────────────────────────────────────────────┘Recursive CTE
┌──────────────────────────────────────────────────────────────┐
│ RECURSIVE CTE │
│ │
│ WITH RECURSIVE tree AS ( │
│ SELECT id, parent_id, 1 AS level ← ANCHOR │
│ FROM nodes │
│ WHERE parent_id IS NULL │
│ │
│ UNION ALL │
│ │
│ SELECT n.id, n.parent_id, t.level + 1 ← RECURSIVE │
│ FROM nodes n │
│ JOIN tree t ON n.parent_id = t.id │
│ ) │
│ SELECT * FROM tree; │
│ │
│ Evaluation: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ Iteration 1: anchor → roots (level 1) │ │
│ │ Iteration 2: recursive on level 1 → level 2 │ │
│ │ Iteration 3: recursive on level 2 → level 3 │ │
│ │ ... │ │
│ │ Stops when recursive member produces no new rows │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ Anchor = base case. │
│ Recursive = step. │
│ UNION ALL combines them. │
│ │
└──────────────────────────────────────────────────────────────┘CTE vs Subquery
┌──────────────────────────────────────────────────────────────┐
│ CTE vs SUBQUERY │
│ │
│ NESTED SUBQUERY: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ SELECT ... │ │
│ │ FROM ( │ │
│ │ SELECT ... │ │
│ │ FROM ( │ │
│ │ SELECT ... │ │
│ │ FROM base │ │
│ │ WHERE ... │ │
│ │ ) AS inner │ │
│ │ WHERE ... │ │
│ │ ) AS outer │ │
│ │ WHERE ...; │ │
│ │ │ │
│ │ Read from inside out. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ CTE: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ WITH inner AS ( │ │
│ │ SELECT ... FROM base WHERE ... │ │
│ │ ), │ │
│ │ outer AS ( │ │
│ │ SELECT ... FROM inner WHERE ... │ │
│ │ ) │ │
│ │ SELECT ... FROM outer WHERE ...; │ │
│ │ │ │
│ │ Read from top to bottom. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
└──────────────────────────────────────────────────────────────┘Summary
| Item | Value |
|---|---|
| CTE | Named temporary result set defined with WITH |
| Scope | The single statement that defines it |
| Multiple CTEs | Separated by commas; each can reference earlier ones |
| Recursive CTE | WITH RECURSIVE, anchor + recursive members |
| Termination | Recursive member produces no new rows |
MATERIALIZED | Forces the CTE to be computed once |
NOT MATERIALIZED | Forces the CTE to be inlined |
| Use with | SELECT, INSERT, UPDATE, DELETE |
| Persistence | None; disappears when the query finishes |
Key takeaways:
- A CTE is a named temporary result set. It is defined with
WITH, and it exists only for the duration of the statement that defines it. It is not stored in the database schema, and it does not persist beyond the query . - CTEs improve readability. A query with nested subqueries is read from the inside out. A query with CTEs is read from the top down, and the reading order matches the evaluation order. Each CTE is a named unit that can be understood in isolation .
- Multiple CTEs are separated by commas. Each CTE can reference the CTEs defined before it, but not the ones defined after it. The dependency order is top to bottom .
- CTEs can be used with
INSERT,UPDATE, andDELETE. TheWITHclause comes before the statement, and the CTE is visible throughout the statement. This is the standard pattern for inserting, updating, or deleting derived data . - A recursive CTE references itself. The anchor member is the base case, and the recursive member references the CTE by name. The two are combined with
UNION ALL. The recursion terminates when the recursive member produces no new rows . - Recursive CTEs require a termination condition. Without one, a cycle in the data causes infinite recursion. The
CYCLEclause (PostgreSQL 14+) detects cycles, and a depth limit is the common safeguard when the clause is not available . - Materialization behavior varies by database and version. PostgreSQL 12+ inlines CTEs referenced once and materializes CTEs referenced multiple times. The
MATERIALIZEDandNOT MATERIALIZEDkeywords give explicit control. The execution plan is the definitive source of truth . - CTEs are not always faster than subqueries. A materialized CTE can be slower than an inlined subquery. The performance depends on the query, the database, and the version. For critical queries, examine the execution plan and choose the materialization strategy explicitly .
Remember: A Common Table Expression is a named temporary result set defined with WITH. It makes complex queries easier to read by replacing nested subqueries with named blocks, and it makes repeated logic easier to maintain by defining it once. Recursive CTEs extend the feature to hierarchical and graph data, with an anchor member for the base case and a recursive member for the step. The CTE exists only for the duration of the statement, and it does not persist. Materialization behavior varies by database, and the MATERIALIZED/NOT MATERIALIZED keywords give explicit control. For analytical queries with multiple steps, CTEs are 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!