Recursive CTEs

A recursive CTE repeatedly applies a query to a previous step. It is useful for hierarchies, paths and sequences.

Anchor member

The anchor selects the starting rows. In employees, manager_id IS NULL identifies the top of this organizational tree.

Recursive member

The recursive member joins employees to the rows found in the previous step. Carry depth or a path if you need to describe where a row sits in the hierarchy.

Termination and cycles

Stop when no new rows match, and guard against cycles or runaway depth. Real graph data is not always a clean tree. A depth bound is a useful safety limit, not a complete cycle-detection strategy.

Dialect differences

SQLite and PostgreSQL use WITH RECURSIVE. SQL Server recursive CTEs use WITH without the RECURSIVE keyword and offer MAXRECURSION. Recursive-member restrictions also vary.