SQL CTEs: Common Table Expressions Explained

A Common Table Expression (CTE) gives a temporary result set a name, and you can use it only for the query it’s defined in. Introduced in SQL:1999, CTEs let you split a complicated query into named blocks, reference the same intermediate result more than once, and express recursion for trees and graphs. PostgreSQL, SQL Server, MySQL 8+, Oracle, and SQLite 3.8.3+ all support them.

I’ve spent years writing and reviewing SQL, and the moment a query crosses three nested subqueries someone on the team will ask “can we rewrite this with a CTE?” That’s usually the right instinct. CTEs don’t make queries faster by magic, but they make them readable, testable, and reusable within a single statement. This guide covers the syntax, the recursive variant, performance trade-offs across engines, and the cases where you’re better off with a temp table or a plain subquery. If you’re also working with window functions, the two complement each other: CTEs organize the pipeline, window functions compute across rows.

When to Use

• A query has several nested subqueries and you keep losing track of parentheses.
• The same intermediate result is needed in more than one place (a CTE avoids duplicating the subquery).
• You’re walking a hierarchy such as an org chart, a bill of materials, or threaded comments.
• You want the query to read like a sequence of named steps so a reviewer can follow the logic.
• You’d like to build and test one piece of the query at a time by selecting from the CTE directly.
• You’re writing a data migration and want to stage transforms in a readable order.

When NOT to Use

• A plain SELECT or a single inline subquery is already fast and clear. Don’t wrap it in a CTE just for style.
• Your database engine doesn’t support CTEs and you can’t upgrade (MySQL 5.7 or older, SQLite < 3.8.3).
• You assume a CTE will automatically run faster. It usually doesn’t. In PostgreSQL before 12, CTEs were always materialized; since 12 they’re inlined by default, which can change performance in either direction.
• You’re in an OLTP hot path with sub-millisecond budgets and the planner inlines the CTE into a plan you didn’t benchmark. Measure first.
• A recursive CTE walks a graph with cycles and you can’t guarantee a depth guard. A runaway recursive CTE will hit max_recursion_depth or cte_max_recursion_depth and fail, or worse, run until the statement times out.

Basic Syntax

WITH cte_name AS (
    SELECT ...
)
SELECT * FROM cte_name;

The WITH clause declares one or more CTEs. The final SELECT treats them like regular tables in scope. The CTE exists only for the duration of that single statement, not for the session.

Non-Recursive CTE Example

A reporting pattern I keep coming back to: compute monthly revenue, then compare each month to the average.

WITH monthly_sales AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(total) AS revenue,
        COUNT(*) AS order_count
    FROM orders
    WHERE order_date >= '2024-01-01'
    GROUP BY DATE_TRUNC('month', order_date)
),
avg_sales AS (
    SELECT AVG(revenue) AS avg_revenue FROM monthly_sales
)
SELECT
    ms.month,
    ms.revenue,
    ms.order_count,
    a.avg_revenue,
    ms.revenue - a.avg_revenue AS variance
FROM monthly_sales ms
CROSS JOIN avg_sales a
ORDER BY ms.month;

The first CTE aggregates orders by month. The second CTE computes the average. The final query joins the two without repeating any aggregation. If you later need to filter months above average, you extend the final SELECT without touching the CTEs.

Multiple CTEs and Chaining

You can declare several CTEs and chain them. Each CTE can reference any CTE declared before it.

WITH
    active_users AS (
        SELECT user_id, last_login
        FROM users
        WHERE last_login >= CURRENT_DATE - INTERVAL '30 days'
    ),
    user_orders AS (
        SELECT user_id,
               COUNT(*) AS order_count,
               SUM(total) AS lifetime_value
        FROM orders
        WHERE user_id IN (SELECT user_id FROM active_users)
        GROUP BY user_id
    )
SELECT
    u.user_id,
    u.last_login,
    COALESCE(o.order_count, 0) AS order_count,
    COALESCE(o.lifetime_value, 0) AS lifetime_value
FROM active_users u
LEFT JOIN user_orders o ON u.user_id = o.user_id;

user_orders depends on active_users. Writing the query this way makes the dependency explicit, which helps when you’re debugging a plan or reviewing a colleague’s pull request. I prefer this shape over a single nested subquery because you can test each CTE in isolation: comment out the final SELECT, run SELECT * FROM active_users, and verify the intermediate result.

Recursive CTE for Hierarchies

A recursive CTE has an anchor member and a recursive member joined by UNION ALL. The anchor seeds the query; the recursive member references the CTE itself and repeats until no new rows are produced or a limit is hit.

-- Org chart: find all reports under the CEO
WITH RECURSIVE org_tree AS (
    -- Anchor
    SELECT id, name, manager_id, 1 AS depth
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive step
    SELECT e.id, e.name, e.manager_id, ot.depth + 1
    FROM employees e
    INNER JOIN org_tree ot ON e.manager_id = ot.id
    WHERE ot.depth < 10
)
SELECT id, name, depth
FROM org_tree
ORDER BY depth, name;

To climb from an employee up to the CEO, reverse the join direction:

WITH RECURSIVE chain_of_command AS (
    SELECT id, name, manager_id, 1 AS steps_to_ceo
    FROM employees
    WHERE id = 42

    UNION ALL

    SELECT e.id, e.name, e.manager_id, coc.steps_to_ceo + 1
    FROM employees e
    INNER JOIN chain_of_command coc ON e.id = coc.manager_id
)
SELECT name, steps_to_ceo
FROM chain_of_command
ORDER BY steps_to_ceo;

The execution flow of a recursive CTE is straightforward once you’ve seen it: the anchor runs once, the recursive member runs against the anchor’s output, then against its own output, and so on until no new rows appear. The diagram below shows that cycle.

I once debugged a recursive CTE that ran for 90 seconds before timing out. The anchor was correct, but the hierarchy had a cycle: employee A reported to B, B reported to C, and C reported to A. The depth < 10 guard saved the database, but the real fix was a data cleanup and a CHECK constraint that prevented the cycle. Always add a termination guard, and if you’re walking user-editable hierarchies, consider a separate cycle-detection query.

Recursive CTE with Aggregation

Recursive CTEs can carry running aggregates down a tree. Here’s a pattern I’ve used in manufacturing schemas: computing the total cost of a part including every sub-component in a bill of materials.

WITH RECURSIVE bom AS (
    -- Anchor: top-level assembly
    SELECT
        part_id,
        part_name,
        quantity,
        unit_cost,
        quantity * unit_cost AS line_cost,
        1 AS depth,
        CAST('/' || part_id AS TEXT) AS path
    FROM parts
    WHERE parent_id IS NULL

    UNION ALL

    -- Recursive: each child part
    SELECT
        c.part_id,
        c.part_name,
        c.quantity * p.quantity AS quantity,
        c.unit_cost,
        c.quantity * p.quantity * c.unit_cost AS line_cost,
        p.depth + 1,
        p.path || '/' || c.part_id
    FROM parts c
    INNER JOIN bom p ON c.parent_id = p.part_id
    WHERE p.depth < 20
)
SELECT
    part_name,
    quantity,
    line_cost,
    depth,
    path
FROM bom
ORDER BY path;

The path column is a materialized path that makes the output sortable by tree position. For very deep hierarchies, a stored materialized path column outperforms recursion; see the recursive CTE recipe for a deeper treatment.

CTEs in UPDATE and DELETE

CTEs aren’t limited to SELECT. PostgreSQL and SQL Server let you use them in data-modifying statements, which I find handy when the target rows come from a subquery and I want the logic readable.

-- Delete order items for expired orders
WITH expired AS (
    SELECT id FROM orders WHERE status = 'expired'
)
DELETE FROM order_items
WHERE order_id IN (SELECT id FROM expired);

-- Update customer tier based on lifetime spend
WITH spend AS (
    SELECT customer_id, SUM(total) AS lifetime_value
    FROM orders
    GROUP BY customer_id
)
UPDATE customers c
SET tier = CASE
    WHEN s.lifetime_value >= 10000 THEN 'platinum'
    WHEN s.lifetime_value >= 5000  THEN 'gold'
    WHEN s.lifetime_value >= 1000  THEN 'silver'
    ELSE 'bronze'
END
FROM spend s
WHERE c.id = s.customer_id;

In MySQL 8.0+, the WITH clause is allowed in UPDATE and DELETE but the syntax differs; check the MySQL WITH documentation for your version.

Materialized CTEs in PostgreSQL

By default, PostgreSQL 12+ may inline a CTE. Add MATERIALIZED to force the engine to compute it once and store the result, or NOT MATERIALIZED to force inlining.

WITH regional_sales AS MATERIALIZED (
    SELECT region, SUM(total) AS total_sales
    FROM orders
    GROUP BY region
    HAVING SUM(total) > 1000000
)
SELECT * FROM regional_sales;

Use MATERIALIZED when the CTE is expensive and referenced several times, or when EXPLAIN shows the planner picking a bad plan. SQL Server and MySQL handle materialization differently and usually don’t expose the keyword.

Performance Considerations

CTE performance is the part most developers get wrong. The intuition is “named subquery = cached result = faster.” That’s rarely true.

PostgreSQL

• Before 12: CTEs were always materialized (computed once, stored, read two or more times). This helped when the CTE was expensive and referenced twice, but hurt when the planner couldn’t push filters into the CTE.
• 12+: CTEs are inlined by default, behaving like a macro. The planner can push predicates down and choose better join orders. Use MATERIALIZED to restore the old behavior when you need it.
• Recursive CTEs: always materialized. The working table is built iteratively. Index the join column (manager_id, parent_id) or the recursion will table-scan at every step.

SQL Server

• CTEs are always inlined; there’s no MATERIALIZED keyword. The planner treats them as syntactic sugar.
• A CTE referenced two or more times in the same statement is evaluated each time. When I need caching across references, I reach for a temp table (SELECT ... INTO #temp) instead.

MySQL

• CTEs are inlined. WITH RECURSIVE uses an iterative evaluation with cte_max_recursion_depth (default 1000) as the safety limit.
• No MATERIALIZED hint. For expensive shared results, use a temp table.

EXPLAIN ANALYZE

Always check the plan before assuming a CTE helps. In PostgreSQL:

EXPLAIN ANALYZE
WITH monthly_sales AS (
    SELECT DATE_TRUNC('month', order_date) AS month, SUM(total) AS revenue
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date)
)
SELECT * FROM monthly_sales WHERE revenue > 10000;

If you see CTE Scan on monthly_sales with Storage: Materialized and the filter is applied after materialization, you’re computing the full CTE and then filtering. Try NOT MATERIALIZED or push the filter into the CTE. For a full treatment of query plans, see the PostgreSQL tuning guide.

CTE vs Temp Table

Aspect	CTE	Temp Table
Scope	Single statement	Session or transaction
Persistence	None	Lives until dropped or session ends
Indexes	None	Can add indexes
Statistics	Planner estimates	Can ANALYZE for accurate stats
Reuse	Within one query	Across queries in the session
Best for	Readability, single-statement logic	Large intermediate results reused across queries

If a CTE is referenced more than once and the intermediate result is large, a temp table with an index often outperforms. I reach for a temp table when the CTE scan dominates the plan and the result is needed in a second query.

CTE vs Subquery vs View

Aspect	CTE	Subquery	View
Readability	Named, reusable	Inline, anonymous	Named, persistent
Reusability	Can reference more than once in a statement	Must duplicate if used again	Referenced from any query
Recursion	Supported	Not supported	Not supported
Materialization	Optional in PostgreSQL	Evaluated each time by default	Materialized view optional
Persistence	Statement scope	Statement scope	Persistent schema object
Best for	Multi-step logic in one query	One-off filtering	Shared logic across many queries

CTE vs Subquery

Aspect	CTE	Subquery
Readability	Named, reusable	Inline, anonymous
Reusability	Can reference more than once	Must duplicate if used again
Recursion	Supported	Not supported
Materialization	Optional in PostgreSQL	Evaluated each time by default

Best Practices

• Name CTEs after the business concept (monthly_sales, active_users, expired_orders), not after the SQL operation (cte1, subquery, step2).
• Keep one logical step per CTE. If you’re joining and aggregating in the same CTE, split it so each step is testable.
• Always add a WHERE depth < N guard to a recursive CTE. Pick N based on your data; 10 is fine for an org chart, 100 for a deep product tree.
• Use MATERIALIZED in PostgreSQL only after checking the query plan with EXPLAIN ANALYZE. Don’t guess.
• Test a CTE in isolation by running SELECT * FROM cte_name before you wire up the final query. I’ve caught aggregation bugs this way that would have been invisible inside a 40-line statement.
• Add a path column to recursive CTEs on trees so the output is sortable and debuggable.

Common Mistakes

• Infinite recursion: forgetting the termination guard or having a cycle in the hierarchy data. The query will hit cte_max_recursion_depth (MySQL) or run until the statement times out (PostgreSQL). Always add WHERE depth < N and consider a cycle-detection constraint on the source table.

• Treating CTEs as temp tables: they live only for the query. For persistence across statements, use CREATE TEMP TABLE or a real table. I’ve seen developers wrap a CTE in a transaction and expect it to persist across SELECT statements, which doesn’t work.

• Performance assumptions: some engines inline CTEs, others materialize. A CTE referenced twice in SQL Server is evaluated twice. Always measure with EXPLAIN ANALYZE before claiming a CTE “improves” anything.

• Over-nesting CTEs: ten chained CTEs can be harder to read than the original subqueries, especially when each CTE references three others. If the chain is long, consider a temp table pipeline or a view.

• Mutual recursion: two CTEs referencing each other isn’t supported in standard SQL. You’ll get a syntax error or an “undefined CTE” message. Restructure with a single recursive CTE or a procedural approach.

• Pushing filters too late: if you materialize a CTE and then filter in the final SELECT, the engine computes the full CTE first. Push the filter into the CTE’s WHERE clause or use NOT MATERIALIZED in PostgreSQL 12+.

Engine Differences at a Glance

Feature	PostgreSQL	SQL Server	MySQL	Oracle	SQLite
Non-recursive CTE	8.4+	2008+	8.0+	9i+	3.8.3+
Recursive CTE	8.4+	2008+	8.0+	11gR2+	3.8.3+
MATERIALIZED hint	12+	No	No	No	No
WITH in UPDATE/DELETE	9.1+	2008+	8.0+ (limited)	No	No
Default behavior	Inlined (12+)	Inlined	Inlined	Inlined	Inlined
Recursion limit	None (guard manually)	None (guard manually)	cte_max_recursion_depth (1000)	None	SQLITE_MAX_EXPR_DEPTH

See Also

• PostgreSQL WITH clause documentation
• SQL Server CTE documentation
• MySQL WITH (Common Table Expressions) documentation
• SQLite recursive CTE documentation
• SQL:1999 standard (ISO/IEC 9075-2) — the original CTE specification
• SQL recursive CTE recipe — a focused recipe on recursive queries
• SQL window functions guide — complements CTEs for analytics
• PostgreSQL tuning guide — EXPLAIN ANALYZE and planner behavior