Recursive CTEs: trees and hierarchies in SQL

In short: A recursive CTE (“common table expression”) calls itself in SQL and so walks through trees and hierarchies, such as categories, org charts or replies in forum threads.

In more detail: Normal queries know a fixed number of joins. Hierarchies have an unknown depth. The recursive CTE repeats a step until nothing new is added.

In Depth

• Structure: A starting part (anchor), UNION ALL, and a recursive part that refers to the CTE itself.

WITH RECURSIVE tree AS (
  SELECT id, name, parent_id, 0 AS depth
  FROM category WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, t.depth + 1
  FROM category c JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree ORDER BY depth;

• Flow: The anchor yields the roots, the recursive part fetches the children of the rows found last.
• Protection against endless loops: With cycles (A → B → A) you must remember visited nodes or limit the depth.
• Other uses: Generating number series, path search in graphs, resolving bills of materials.
• Alternatives: A column with the path (“materialized path”), nested sets, ltree in PostgreSQL.

See also: SQL window functions