SQL CTEs and Recursive Queries

Explain the purpose of CTEs and how recursive CTEs work.

CTE (Common Table Expression)

Defined with the WITH clause, CTEs create named temporary result sets that make complex queries more readable and reusable.

Recursive CTE

Used to query hierarchical or graph structures such as org charts and category trees. Uses WITH RECURSIVE and contains two parts:

1. Anchor query: Initial rows (e.g., root nodes).
2. Recursive query: References the CTE itself, expanding from previous iteration results.

Recursive CTE Example

WITH RECURSIVE org AS ( SELECT id, name, manager_id, 1 AS depth FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.name, e.manager_id, o.depth + 1 FROM employees e JOIN org o ON e.manager_id = o.id ) SELECT * FROM org ORDER BY depth;

Caution

Prevent infinite loops by adding a depth limit or ensuring data has no circular references.