SQL CTEs and Recursive Queries — WITH Clause, Recursion, and Performance
Fast recall
Quick revision
Core answer
Whether you can read and write CTEs comfortably, recognize when recursion is the right tool for hierarchical data, and understand the materialization vs inlining behavior that affects performance.
Treating CTEs as universally faster than subqueries (they're not), or writing recursive CTEs without a termination guard, causing infinite recursion and database OOM.
Discuss CTE materialization fences (Postgres pre-12 always materialized; 12+ inlines unless MATERIALIZED is specified), the role of recursive CTEs in graph traversal, and when LATERAL joins or window functions are better alternatives.
Interview-ready guide
Interview answer
CTEs are one of those SQL features that improves quality of life enormously but where the performance story is often misunderstood — interviewers test both whether you can use them and whether you understand their actual cost.
Non-recursive CTEs — the readability use case
- The
WITHclause lets you name a subquery so the rest of the query reads top-to-bottom — instead of nesting four levels of subqueries, you definerecent_orders, theneligible_customers, then join them in the final SELECT - Performance is essentially identical to subqueries in modern databases — Postgres 12+ and MySQL 8 inline simple CTEs into the main query, so the planner optimizes across the boundary; the value is purely human readability
- A common myth is that CTEs are universally faster — they're not, and in older Postgres versions they were sometimes slower because they acted as optimization fences
Recursive CTEs — the killer feature for hierarchical data
- Recursive CTEs use
WITH RECURSIVEto traverse hierarchies — org charts, category trees, threaded comments, bill-of-materials — that would otherwise require application-level recursion with N+1 queries - Two parts separated by
UNION ALL: the anchor produces the starting rows; the recursive term joins back to the CTE itself to find the next level; iterates until no new rows are produced - The classic pattern: find all employees reporting to manager 42, including transitively — anchor is
WHERE id = 42, recursive term joinsemployeesagainst the CTE onmanager_id = oc.id
The infinite loop trap
- Recursive CTEs on cyclic data run forever — if Bob reports to Alice and Alice reports to Bob due to a data bug, each iteration produces new rows by re-traversing the cycle
- Three guards: depth limit (
WHERE depth < 10), cycle detection (track visited IDs in a path array, checkNOT (id = ANY(path))), and a statement timeout as the last line of defense
For unbounded graph traversal — social networks, recommendation engines — recursive CTEs hit performance walls quickly; that's when you reach for a graph database like Neo4j or precomputed transitive closure tables.
SQL CTEs and Recursive Queries — WITH Clause, Recursion, and Performance
Direct Answer
A Common Table Expression (CTE) is a named temporary result set defined with the WITH clause that exists only for the duration of a single query. Non-recursive CTEs improve readability by breaking complex queries into named steps; they have no execution advantage over subqueries in modern databases. Recursive CTEs use WITH RECURSIVE to traverse hierarchical or graph data — organizational charts, category trees, bill-of-materials — by repeatedly applying a query to its own previous results until a termination condition is reached.
Code Example
-- Without CTE: nested subqueries are hard to follow
SELECT region, total_revenue, customer_count
FROM (
SELECT c.region,
SUM(o.total) AS total_revenue,
COUNT(DISTINCT c.id) AS customer_count
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.created_at >= NOW() - INTERVAL '90 days'
AND c.id NOT IN (SELECT user_id FROM blocked_users)
GROUP BY c.region
) revenue_by_region
WHERE total_revenue > 100000
ORDER BY total_revenue DESC;
-- With CTE: each step has a clear name and purpose
WITH recent_orders AS (
SELECT customer_id, total
FROM orders
WHERE created_at >= NOW() - INTERVAL '90 days'
),
eligible_customers AS (
SELECT id, region FROM customers
WHERE id NOT IN (SELECT user_id FROM blocked_users)
),
revenue_by_region AS (
SELECT c.region,
SUM(o.total) AS total_revenue,
COUNT(DISTINCT c.id) AS customer_count
FROM eligible_customers c
JOIN recent_orders o ON o.customer_id = c.id
GROUP BY c.region
)
SELECT region, total_revenue, customer_count
FROM revenue_by_region
WHERE total_revenue > 100000
ORDER BY total_revenue DESC;
Common Mistakes & Pitfalls
Treating CTEs as universally faster than subqueries (they're not), or writing recursive CTEs without a termination guard, causing infinite recursion and database OOM.
Check your understanding
Answer these without looking back at the article.
- What's the difference between WITH and WITH RECURSIVE?
- How do you detect cycles in a recursive CTE?
- When would a recursive CTE be slower than application-level traversal?
- What's a CTE materialization fence and how does it affect query plans?
Related interview prep categories
2Looking for a structured roadmap?
Every module on this page is also part of the full Java backend interview track.