Recursive CTE

Recursive CTE is a SQL statement in the CTE (WITH) category. A CTE that references itself, allowing hierarchical or recursive queries (org charts, tree structures). The syntax is WITH RECURSIVE cte AS (anchor UNION ALL recursive) SELECT * FROM cte;. It returns recursive hierarchy. A typical example: WITH RECURSIVE org_tree AS ( SELECT id, name, manager_id, 1 AS level FROM employees WHERE manager_id IS NULL -- Root UNION ALL SELECT e.id, e.name, e.manager_id, ot.level + 1 FROM employees e JOIN org_tree ot ON e.manager_id = ot.id ) SELECT * FROM org_tree ORDER BY level; -- Hierarchical org chart with depth levels A close relative is WITH (CTE), which common Table Expression — define a named temporary result set that can be referenced in the main query. A close relative is Multiple CTEs, which define multiple CTEs in one WITH clause, comma-separated. Each can reference previous CTEs. A close relative is CTE in INSERT, which use a CTE to compute data before inserting it into a table.