CTE ricorsive: gerarchie a profondità arbitraria

Un self-JOIN (Modulo 5) trova un solo livello di gerarchia: figlio → padre. Ma cosa succede se la gerarchia è profonda 3, 5, n livelli, e non sai a priori quanto? Servono le CTE ricorsive: una CTE che fa riferimento a se stessa per "espandere" iterativamente i risultati.

Nel dataset ecommerce, la tabella categories ha 3 radici (Elettronica, Libri, Casa) e 2 figli ciascuna — un albero a 2 livelli. È un caso semplice ma sufficiente per imparare il pattern.

La sintassi

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;

Come funziona in pratica:

PostgreSQL esegue il termine base una volta.
Esegue il termine ricorsivo usando come "input" le righe appena prodotte (non l'intera CTE: solo l'incremento).
Ripete il passo 2 finché il termine ricorsivo non produce 0 nuove righe.
Il risultato finale è l'UNION ALL di tutti gli step.

Esempio 1: albero con livello e percorso

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;

Il primo giro produce 3 righe (le radici a livello 1). Il secondo giro trova le 6 figlie (livello 2). Il terzo giro non trova nulla → fine.

Esempio 2: dato un nodo, trovarne la radice

Variante "top-down → bottom-up": propaghiamo l'id della radice giù lungo l'albero.

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;

Prova tu

Esercizio#sql.m7.l4.e1

Tentativi: 0Caricamento…

Usa una CTE ricorsiva chiamata 'tree' per appiattire la gerarchia delle categorie. Mostra id, name, depth (1 per le radici, 2 per le figlie, ecc.) e path (es. 'Elettronica > Audio'). Ordina per path.

Caricamento editor…

Mostra suggerimento

Il JOIN del termine ricorsivo lega c.parent_id alla riga già presente in tree (t.id).

Soluzione disponibile dopo 3 tentativi

Esercizio di ripasso

Esercizio#sql.m7.l4.e2

Tentativi: 0Caricamento…

Per ogni categoria, mostra id, name e root_name (il nome della categoria-radice in cima all'albero a cui appartiene). Le radici hanno root_name = name. Ordina per id.

Caricamento editor…

Mostra suggerimento

Propaga root_name dal parent (t.root_name) al figlio (c).

Soluzione disponibile dopo 3 tentativi