Definition
- CTE – a named, temporary result set defined with
WITH name AS (...)that exists for the duration of a single statement. - Readability – replaces deeply nested subqueries with named stages that read top-to-bottom, each testable on its own.
- Chaining – multiple CTEs separated by commas, where later CTEs reference earlier ones.
- Recursive CTE – uses
WITH RECURSIVE name AS (anchor UNION ALL recursive_member)to traverse hierarchical data. - Anchor member – runs once to produce the starting rows (for example, the root of a tree).
- Recursive member – references the CTE itself to find the next level; the engine repeats it until no new rows are produced.
Imagine solving a complex report at the National Bank of India in steps. Instead of cramming every step inside nested brackets, a CTE lets you name each step like "first, define high_value_accounts" and then use that name later, reading top to bottom like a recipe. A recursive CTE is like tracing a reporting chain: start with the branch manager (the anchor), then find everyone who reports to them, then everyone who reports to those people, and keep going down until nobody is left.
Understanding the Concepts
What is a CTE?
A CTE (Common Table Expression) is introduced with WITH name AS (SELECT ...) and then referenced by that name in the main query as if it were a table. It gives a readable name to an intermediate result and scopes it to a single statement.
-- CTE: named intermediate result
WITH high_value_accounts AS (
SELECT account_id, balance
FROM accounts
WHERE balance > 500000
)
SELECT a.account_id, a.holder_name, h.balance
FROM high_value_accounts h
JOIN accounts a ON a.account_id = h.account_id;
How does a CTE improve readability?
Nested subqueries read inside-out and are hard to follow. CTEs read top-to-bottom as named stages, each testable on its own.
-- HARD TO READ: three-level nested subquery (inside-out)
SELECT branch_id, SUM(total) AS branch_total
FROM (
SELECT account_id, branch_id, SUM(amount) AS total
FROM (
SELECT t.account_id, a.branch_id, t.amount
FROM transactions t
JOIN accounts a ON a.account_id = t.account_id
WHERE t.status = 'SUCCESS'
) base
GROUP BY account_id, branch_id
) acct_totals
GROUP BY branch_id;
-- EASY TO READ: three chained CTEs (top-to-bottom)
WITH
base AS (
SELECT t.account_id, a.branch_id, t.amount
FROM transactions t
JOIN accounts a ON a.account_id = t.account_id
WHERE t.status = 'SUCCESS'
),
acct_totals AS (
SELECT account_id, branch_id, SUM(amount) AS total
FROM base
GROUP BY account_id, branch_id
)
SELECT branch_id, SUM(total) AS branch_total
FROM acct_totals
GROUP BY branch_id;
How do you chain multiple CTEs?
Separate CTEs with commas. Later CTEs can reference earlier ones, building a pipeline.
WITH
active_accounts AS (
SELECT account_id, holder_name, branch_id
FROM accounts
WHERE status = 'ACTIVE'
),
account_totals AS (
SELECT a.account_id, a.holder_name, a.branch_id,
SUM(t.amount) AS total_spent
FROM active_accounts a
JOIN transactions t ON t.account_id = a.account_id
GROUP BY a.account_id, a.holder_name, a.branch_id
),
branch_summary AS (
SELECT branch_id,
COUNT(*) AS active_accounts,
SUM(total_spent) AS branch_total
FROM account_totals
GROUP BY branch_id
)
SELECT * FROM branch_summary
ORDER BY branch_total DESC;
What is a recursive CTE?
A recursive CTE uses WITH RECURSIVE name AS (anchor UNION ALL recursive_member). The anchor member runs once to produce the starting rows. The recursive member references name itself and joins it to the base table to find the next level. Recursion stops when an iteration produces no new rows.
-- Org chart at National Bank of India
CREATE TABLE employees (
emp_id INTEGER PRIMARY KEY,
name VARCHAR(100),
manager_id INTEGER REFERENCES employees(emp_id)
);
INSERT INTO employees VALUES
(1, 'Pankaj (Branch Manager)', NULL),
(2, 'Rajesh (Team Lead)', 1),
(3, 'Priya (Developer)', 2),
(4, 'Amit (Developer)', 2),
(5, 'Neha (Intern)', 3);
-- Recursive CTE: traverse the org chart
WITH RECURSIVE org_chart AS (
-- Anchor: the top manager (manager_id IS NULL)
SELECT emp_id, name, manager_id, 0 AS depth,
name::TEXT AS path
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive: find employees who report to the current level
SELECT e.emp_id, e.name, e.manager_id, oc.depth + 1,
oc.path || ' → ' || e.name
FROM employees e
JOIN org_chart oc ON e.manager_id = oc.emp_id
)
SELECT depth, REPEAT(' ', depth) || name AS hierarchy, path
FROM org_chart
ORDER BY path;
depth | hierarchy | path
-------+-------------------------------+--------------------------------------
0 | Pankaj (Branch Manager) | Pankaj (Branch Manager)
1 | Rajesh (Team Lead) | Pankaj → Rajesh (Team Lead)
2 | Priya (Developer) | Pankaj → Rajesh → Priya (Developer)
2 | Amit (Developer) | Pankaj → Rajesh → Amit (Developer)
3 | Neha (Intern) | Pankaj → Rajesh → Priya → Neha
How does the recursion work step by step?
The engine processes the recursive CTE iteratively:
- Run the anchor: produces Pankaj (depth 0).
- Run the recursive member with the anchor result: finds Rajesh (depth 1).
- Run the recursive member with the previous result: finds Priya, Amit (depth 2).
- Run the recursive member with the previous result: finds Neha (depth 3).
- Run the recursive member: no new rows produced. Recursion stops.
- UNION ALL accumulates all levels into the final result.
Why use UNION ALL instead of UNION?
UNION ALL is used for performance because UNION de-duplicates each iteration, which is expensive. Cycle protection is handled explicitly with a path or visited set instead.
-- UNION ALL: no deduplication (fast)
WITH RECURSIVE cte AS (
SELECT ... UNION ALL SELECT ...
)
-- UNION: deduplicates every iteration (slow)
WITH RECURSIVE cte AS (
SELECT ... UNION SELECT ...
)
How do you detect and prevent cycles?
Graphs with loops (not trees) can cause infinite recursion. Carry an accumulated path and check for cycles, or use PostgreSQL's CYCLE clause.
-- Manual cycle detection with a path array
WITH RECURSIVE traversal AS (
SELECT emp_id, name, ARRAY[emp_id] AS visited
FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.emp_id, e.name, t.visited || e.emp_id
FROM employees e
JOIN traversal t ON e.manager_id = t.emp_id
WHERE e.emp_id <> ALL(t.visited) -- cycle guard
)
SELECT * FROM traversal;
-- PostgreSQL 14+ CYCLE clause
WITH RECURSIVE traversal AS (
SELECT emp_id, name, manager_id FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.emp_id, e.name, e.manager_id
FROM employees e JOIN traversal t ON e.manager_id = t.emp_id
)
CYCLE emp_id SET is_cycle USING cycle_path
SELECT * FROM traversal WHERE NOT is_cycle;
How do you limit the depth of recursion?
Carry a depth counter and add a WHERE condition in the recursive member to stop at a maximum depth.
-- Limit to 3 levels deep
WITH RECURSIVE org_chart AS (
SELECT emp_id, name, 0 AS depth
FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.emp_id, e.name, oc.depth + 1
FROM employees e
JOIN org_chart oc ON e.manager_id = oc.emp_id
WHERE oc.depth < 3 -- stop at depth 3
)
SELECT * FROM org_chart;
What other hierarchies can recursive CTEs traverse?
Recursive CTEs handle any hierarchical or graph structure:
- Org charts – who reports to whom at any depth.
- Category trees – product categories with parent-child relationships.
- Account linkage graphs – find all accounts linked to a suspicious account.
- Bill of materials – parts that contain sub-parts that contain sub-sub-parts.
-- Category hierarchy at National Bank of India's product catalog
WITH RECURSIVE category_tree AS (
SELECT id, name, parent_id, 0 AS depth
FROM categories WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.name, c.parent_id, ct.depth + 1
FROM categories c
JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT REPEAT(' ', depth) || name AS category
FROM category_tree
ORDER BY depth, name;
What should a senior engineer know?
- Whether an engine materializes or inlines a non-recursive CTE affects performance (see Q1163 on materialization fences).
- An index on the join column (e.g.,
manager_id) is essential for recursive CTE performance. - PostgreSQL limits recursive CTE iterations with
max_recursive_iterations(default: no limit, but you should add a depth guard). - In MySQL 8.0+,
WITH RECURSIVEis supported. In older MySQL, you must use stored procedures with loops. - Data-modifying CTEs (
WITH deleted AS (DELETE FROM ... RETURNING *)) execute the modification once and provide the affected rows to the outer query.
What are common interview follow-up questions?
- What is the difference between a CTE and a view? (A CTE is statement-scoped and temporary; a view is a persistent named query stored in the catalog.)
- Can you have multiple recursive CTEs in one statement? (Yes, but each recursive CTE can only reference itself, not another recursive CTE.)
- How do you convert a recursive CTE into iterative code in Java? (Use a queue or stack to process each level, querying children of the current batch.)
Visual Overview
Recursive CTE: Org Chart Traversal
Anchor (depth 0)
┌──────────────────────────┐
│ Pankaj (Branch Manager) │
└────────────┬─────────────┘
│
Iteration 1 (depth 1)
┌────────────────────────┐
│ Rajesh (Team Lead) │
└────────┬───────────────┘
│
Iteration 2 (depth 2)
┌──────────────────┐ ┌──────────────────┐
│ Priya (Dev) │ │ Amit (Dev) │
└────────┬─────────┘ └──────────────────┘
│
Iteration 3 (depth 3)
┌──────────────────┐
│ Neha (Intern) │
└──────────────────┘
│
Iteration 4: no new rows → STOP
UNION ALL accumulates all levels into one result.
Key Takeaway
A CTE is a named temporary result set defined with WITH name AS (...) that lives for one statement. It replaces deeply nested subqueries with named stages that read top-to-bottom, each testable on its own. Multiple CTEs can be chained with commas, where later ones reference earlier ones, building a readable pipeline.
A recursive CTE, written WITH RECURSIVE, traverses hierarchical data. It has an anchor member that produces the starting rows and a recursive member that references the CTE itself to find the next level. The engine repeats the recursive member until an iteration produces no new rows, and UNION ALL accumulates all levels. At the National Bank of India, Rajesh uses recursive CTEs for the branch org chart, category trees, and account linkage graphs.
The senior considerations are: use UNION ALL (not UNION) for performance, carry a depth counter to limit recursion, carry a path array to detect cycles in graphs, and index the join column (like manager_id) for fast lookups. Whether the engine materializes or inlines a non-recursive CTE affects performance, so Pankaj checks the EXPLAIN plan for hot queries.
Remember one sentence: a CTE is a named WITH block that makes queries read top-to-bottom like steps, and a recursive CTE walks hierarchies with an anchor plus a self-referencing recursive step.
---