Recursive CTEs: arbitrary-depth hierarchies

A self-JOIN (Module 5) finds only one level of a hierarchy: child → parent. But what happens if the hierarchy is 3, 5, or n levels deep, and you don't know in advance how many? You need recursive CTEs: a CTE that refers to itself to "expand" the results iteratively.

In the ecommerce dataset, the categories table has 3 roots (Elettronica, Libri, Casa) and 2 children each — a 2-level tree. It's a simple case but enough to learn the pattern.

The syntax

SQL

WITH RECURSIVE nome (col1, col2, …) AS (
  -- (1) Termine BASE: le righe di partenza
  SELECT …
  FROM   …
  WHERE  …

  UNION ALL

  -- (2) Termine RICORSIVO: si appoggia a "nome" stesso per espandere
  SELECT …
  FROM   tabella  t
  JOIN   nome    n  ON …
)
SELECT * FROM nome;

How it works in practice:

PostgreSQL executes the base term once.
It executes the recursive term using the rows just produced as "input" (not the whole CTE: only the increment).
It repeats step 2 until the recursive term produces 0 new rows.
The final result is the UNION ALL of all the steps.

Example 1: tree with depth and path

SQL

WITH RECURSIVE tree AS (
  -- base: le radici (parent_id IS NULL)
  SELECT id,
         name,
         parent_id,
         1                 AS depth,
         name              AS path
  FROM   categories
  WHERE  parent_id IS NULL

  UNION ALL

  -- ricorsivo: per ogni categoria che ha come parent una riga già in tree
  SELECT c.id,
         c.name,
         c.parent_id,
         t.depth + 1,
         t.path || ' > ' || c.name
  FROM   categories c
  JOIN   tree       t ON c.parent_id = t.id
)
SELECT id, name, depth, path
FROM   tree
ORDER BY path;

The first iteration produces 3 rows (the roots at level 1). The second iteration finds the 6 children (level 2). The third iteration finds nothing → done.

Example 2: given a node, find its root

A "top-down → bottom-up" variant: we propagate the root's id down along the tree.

SQL

WITH RECURSIVE tree AS (
  SELECT id,
         name,
         parent_id,
         id   AS root_id,
         name AS root_name
  FROM   categories
  WHERE  parent_id IS NULL

  UNION ALL

  SELECT c.id,
         c.name,
         c.parent_id,
         t.root_id,    -- propago lo stesso id_radice
         t.root_name   -- e nome
  FROM   categories c
  JOIN   tree       t ON c.parent_id = t.id
)
SELECT id, name, root_name
FROM   tree
ORDER BY id;

Try it

Exercise#sql.m7.l4.e1

Attempts: 0Loading…

Use a recursive CTE named 'tree' to flatten the category hierarchy. Show id, name, depth (1 for roots, 2 for children, etc.) and path (e.g. 'Elettronica > Audio'). Order by path.

Loading editor…

Show hint

The JOIN in the recursive term links c.parent_id to the row already present in tree (t.id).

Solution available after 3 attempts

Review exercise

Exercise#sql.m7.l4.e2

Attempts: 0Loading…

For each category, show id, name and root_name (the name of the root category at the top of the tree it belongs to). Roots have root_name = name. Order by id.

Loading editor…

Show hint

Propagate root_name from the parent (t.root_name) to the child (c).

Solution available after 3 attempts