Advanced SQL

Recursive Queries

A recursive CTE grows a result from an anchor through repeated recursive steps until no new step rows appear; safe traversal makes depth, path, cycles, duplicates, and output ordering explicit.

The anchor seeds the working relation.
The recursive term advances exactly one relationship step.
UNION ALL preserves distinct paths; UNION removes duplicate result rows.
Cycle prevention belongs in the traversal state.
Depth limits are safety bounds, not cycle detection.
Traversal order and display order are separate.

Working-table flow

WITH RECURSIVE category_tree AS (
  SELECT c.category_id, c.parent_category_id, c.name,
         0 AS depth, ARRAY[c.category_id] AS path
  FROM categories AS c
  WHERE c.category_id = :root_id
  UNION ALL
  SELECT child.category_id, child.parent_category_id, child.name,
         parent.depth + 1, parent.path || child.category_id
  FROM category_tree AS parent
  JOIN categories AS child
    ON child.parent_category_id = parent.category_id
  WHERE parent.depth < :max_depth
    AND NOT child.category_id = ANY(parent.path)
)
SELECT category_id, parent_category_id, name, depth, path
FROM category_tree
ORDER BY path;

Traversal state for A → B → C with corrupt C → A
Iteration	Candidate	Path	Decision
Anchor	A	`[A]`	Emit depth 0
1	B	`[A,B]`	Emit depth 1
2	C	`[A,B,C]`	Emit depth 2
3	A	Already in path	Reject cycle edge