How do recursive queries work in SQL?

Recursive queries in SQL allow you to traverse hierarchical or nested data structures, such as organizational charts or category trees, by repeatedly executing a query that references its own output. They are implemented using a Common Table Expression (CTE) declared with the WITH RECURSIVE keyword (or equivalent syntax in some databases). A recursive CTE consists of two parts: the anchor member, which defines the initial result set, and the recursive member, which references the CTE itself to build subsequent result sets. The process iterates until no new rows are added, effectively handling multi-level relationships in a single query.

To illustrate, consider a table employees with columns employee_id, name, and manager_id. Suppose you want to list all subordinates under a specific manager. The anchor member selects the root manager (e.g., WHERE manager_id IS NULL). The recursive member then joins the CTE with the employees table to find employees whose manager_id matches the employee_id from the previous iteration. Each cycle appends new rows to the result, expanding the hierarchy level by level. For example, the first iteration returns direct reports, the second iteration returns their subordinates, and so on. The query stops when a join operation produces no new rows, ensuring termination.

Recursive queries are useful for scenarios like organizational hierarchies, category sub-tree traversal, or graph pathfinding. However, they require careful design to avoid infinite loops (e.g., cyclic manager-subordinate relationships). Performance can also degrade with deep hierarchies, so indexing key columns (like manager_id) is critical. Databases like PostgreSQL, SQL Server, and Oracle support recursive CTEs, though syntax details may vary. Always test with edge cases, such as deeply nested data or cycles, to ensure reliability.