SQL Academy · Lesson

Recursive CTEs for Hierarchies

Walk hierarchical data (org charts, threaded comments, graph traversal) with WITH RECURSIVE and stop conditions.

Recursive CTEs for Hierarchies is a free SQL Academy lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why Recursive?

Plain SQL can't walk a tree of unknown depth: parents of parents, children of children. Recursive CTEs are the standard-SQL solution.

Anatomy

A recursive CTE has two parts joined by UNION ALL:

WITH RECURSIVE name AS (
  -- 1. Anchor query: seed rows
  SELECT ...
  UNION ALL
  -- 2. Recursive step: references the CTE itself
  SELECT ...
  FROM name JOIN ...
)
SELECT * FROM name;

Walking an Org Chart

Find all employees reporting (directly or indirectly) to a given manager:

WITH RECURSIVE reports AS (
  -- anchor: the manager themself
  SELECT id, full_name, manager_id, 0 AS depth
  FROM employees WHERE id = 42

  UNION ALL

  -- recurse: people whose manager is in reports
  SELECT e.id, e.full_name, e.manager_id, r.depth + 1
  FROM employees e
  JOIN reports r ON r.id = e.manager_id
)
SELECT * FROM reports ORDER BY depth, full_name;

Threaded Comments

Walk a discussion tree from a root:

WITH RECURSIVE thread AS (
  SELECT id, parent_id, body, 0 AS depth, ARRAY[id] AS path
  FROM comments WHERE id = $1
  UNION ALL
  SELECT c.id, c.parent_id, c.body, t.depth + 1, t.path || c.id
  FROM comments c
  JOIN thread t ON c.parent_id = t.id
)
SELECT * FROM thread ORDER BY path;

Termination

Recursion stops when the recursive step returns no new rows.

Avoiding Infinite Loops

If your graph has cycles, track visited nodes:

WITH RECURSIVE walk AS (
  SELECT id, ARRAY[id] AS path FROM nodes WHERE id = $1
  UNION ALL
  SELECT e.target_id, w.path || e.target_id
  FROM edges e
  JOIN walk w ON e.source_id = w.id
  WHERE e.target_id <> ALL(w.path)
)
SELECT * FROM walk;

Numeric Series

Recursive CTEs can also generate sequences:

WITH RECURSIVE n(i) AS (
  VALUES (1)
  UNION ALL
  SELECT i + 1 FROM n WHERE i < 100
)
SELECT i, i*i AS square FROM n;

Bill of Materials

Explode a product into all components, including sub-assemblies:

WITH RECURSIVE bom AS (
  SELECT part_id, sub_part_id, qty FROM parts WHERE part_id = $1
  UNION ALL
  SELECT p.part_id, p.sub_part_id, p.qty * bom.qty
  FROM parts p
  JOIN bom ON bom.sub_part_id = p.part_id
)
SELECT sub_part_id, SUM(qty) AS total_qty FROM bom GROUP BY sub_part_id;

Depth Limits

For safety, cap the recursion depth:

WITH RECURSIVE tree AS (
  SELECT id, parent_id, 0 AS depth FROM nodes WHERE id = $1
  UNION ALL
  SELECT n.id, n.parent_id, t.depth + 1
  FROM nodes n JOIN tree t ON n.parent_id = t.id
  WHERE t.depth < 10
)
SELECT * FROM tree;

UNION vs UNION ALL

UNION ALL is the usual choice. UNION deduplicates — useful when a node can be reached multiple ways.

Performance

Recursive CTEs are evaluated iteratively. Each step's "working table" is the rows produced by the previous step. Index the join columns.

Recap

Recursive CTEs traverse hierarchies and graphs.

Anchor + UNION ALL + recursive step
Stops when recursive step returns no rows
Use a path array to break cycles

Quick Check

Which keyword turns a CTE into a recursive one?

Frequently asked questions

Is the “Recursive CTEs for Hierarchies” lesson free?

Yes — the full text of “Recursive CTEs for Hierarchies” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Recursive CTEs for Hierarchies”?

Walk hierarchical data (org charts, threaded comments, graph traversal) with WITH RECURSIVE and stop conditions. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Recursive CTEs for Hierarchies” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Academy