CTE Ricorsivi: che cosa sono e perché servono

Oggi facciamo un altro passo avanti e ci occupiamo della magia della ricorsione. Se hai già programmato in un linguaggio che supporta la ricorsione (tipo Python), più o meno sai di cosa si parla. Ma non preoccuparti se ti sembra una roba misteriosa — spiegheremo tutto per bene.

I CTE ricorsivi sono uno strumento super per lavorare con strutture di dati gerarchiche, ad albero, come le strutture organizzative delle aziende, gli alberi genealogici o le directory dei file.

In parole povere, sono delle espressioni che possono "chiamare se stesse", per attraversare e processare tutti i livelli dei dati passo dopo passo.

Caratteristiche chiave dei CTE ricorsivi:

1. Usano la parola chiave WITH RECURSIVE.
2. I CTE ricorsivi sono fatti da due parti:
   • Query base: definisce il punto di partenza (o "radice") della ricorsione.
   • Query ricorsiva: processa i dati rimanenti usando il risultato dello step precedente.

L’algoritmo di un CTE ricorsivo è tipo quando sali una scala:

• Prima sali sul primo gradino (questa è la query base).
• Poi sali sul secondo gradino usando il risultato del primo (query ricorsiva).
• Ripeti il processo finché non finiscono i gradini (raggiungi la condizione di stop).

Sintassi di un CTE ricorsivo

Dai, guardiamo subito un esempio base:

WITH RECURSIVE cte_name AS (
    -- Query base
    SELECT column1, column2
    FROM table_name
    WHERE condizione_per_il_caso_base

    UNION ALL

    -- Query ricorsiva
    SELECT column1, column2
    FROM table_name
    JOIN cte_name ON qualche_condizione
    WHERE condizione_di_stop
)
SELECT * FROM cte_name;

Il ruolo di UNION e UNION ALL nei CTE ricorsivi

Ogni CTE ricorsivo deve usare gli operatori UNION o UNION ALL tra la parte base e quella ricorsiva.

Quale operatore scegliere: UNION o UNION ALL?

Se non sei sicuro di cosa usare — quasi sempre scegli UNION ALL. Perché? Perché va più veloce: unisce i risultati senza controllare se ci sono duplicati. Quindi — meno calcoli, meno risorse e risultato più rapido.

Questo è super importante nei CTE ricorsivi. Quando costruisci gerarchie — tipo un albero di commenti o la struttura dei dipendenti in azienda — UNION ALL serve quasi sempre. Se usi solo UNION, il database può pensare che certi step ci sono già stati e “tagliare” parte del risultato. E questo ti rompe tutta la logica dell’attraversamento.

Usa UNION solo se sei sicuro che i duplicati fanno casino e vanno tolti. Ma ricorda: è sempre un compromesso tra pulizia e velocità.

Esempio di approcci diversi

-- UNION: i duplicati vengono esclusi
SELECT 'A'
UNION
SELECT 'A';     -- Risultato: una riga 'A'

-- UNION ALL: i duplicati restano
SELECT 'A'
UNION ALL
SELECT 'A';     -- Risultato: due righe 'A'

Nei CTE ricorsivi è più sicuro usare sempre UNION ALL, così non perdi step importanti mentre attraversi la struttura.

Vediamo un caso tipico: abbiamo una tabella dei dipendenti con le colonne employee_id, manager_id e name. Dobbiamo costruire la gerarchia a partire dal direttore — cioè chi non ha capo (manager_id = NULL).

Supponiamo di avere la tabella dei dipendenti: employees

Dobbiamo capire chi risponde a chi, e sapere il livello di ogni dipendente nella struttura. È comodo quando vuoi, per esempio, mostrare l’albero dei dipendenti in un’interfaccia o preparare un report sulla struttura del team.

WITH RECURSIVE employee_hierarchy AS (
    -- Partiamo da chi non ha capo
    SELECT
        employee_id,
        name,
        manager_id,
        1 AS livello
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Aggiungiamo i subordinati e aumentiamo il livello
    SELECT
        e.employee_id,
        e.name,
        e.manager_id,
        eh.livello + 1
    FROM employees e
    INNER JOIN employee_hierarchy eh
    ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;

Il risultato sarà così:

Questa query mostra chiaramente come puoi “percorrere” la gerarchia dei dipendenti — dal direttore fino ai più giovani nella struttura. Il livello (livello) è comodo per formattare o visualizzare l’albero.

Esempio: categorie di prodotti

Ora immagina che lavoriamo con una tabella delle categorie di prodotti, dove ogni categoria può avere delle sottocategorie, e queste a loro volta altre sottocategorie. Come costruiamo l’albero delle categorie?

Tabella categories

Query ricorsiva:

WITH RECURSIVE category_tree AS (
    -- Caso base: trova le categorie radice
    SELECT
        category_id,
        name,
        parent_id,
        1 AS profondità
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- Parte ricorsiva: trova le sottocategorie delle categorie attuali
    SELECT
        c.category_id,
        c.name,
        c.parent_id,
        ct.profondità + 1
    FROM categories c
    INNER JOIN category_tree ct
    ON c.parent_id = ct.category_id
)
SELECT * FROM category_tree;

Risultato:

Ora vediamo l’albero delle categorie con i livelli di profondità.

Perché i CTE ricorsivi sono una figata?

I CTE ricorsivi sono uno degli strumenti più espressivi e potenti di SQL. Invece di scrivere logiche annidate complicate, descrivi solo da dove partire (caso base) e come andare avanti (parte ricorsiva) — il resto lo fa PostgreSQL.

Di solito queste query si usano per attraversare gerarchie: dipendenti, categorie di prodotti, directory su disco, grafi nei social. Sono facili da estendere: se aggiungi nuovi dati nella tabella, la query li prende da sola. È comodo e scalabile.

Ma occhio alle trappole. Controlla sempre le condizioni di stop — senza di loro la query può andare in loop infinito. Non dimenticare gli indici: su tabelle grandi le query ricorsive senza indici possono rallentare un sacco. E UNION ALL — quasi sempre è la scelta migliore, soprattutto nei casi gerarchici, altrimenti rischi di perdere step della ricorsione per colpa della rimozione dei duplicati.

Un CTE ricorsivo ben fatto ti permette di esprimere logiche di business complesse in poche righe — senza procedure, cicli o codice extra. È uno di quei casi in cui SQL funziona non solo bene, ma anche in modo elegante.

Errori tipici con i CTE ricorsivi

• Ricorsione infinita: se non metti una condizione di stop corretta (WHERE), la query va in loop.
• Dati ridondanti: usare male UNION ALL aggiunge duplicati.
• Performance: le query ricorsive possono essere pesanti su grandi quantità di dati. Indici sulle colonne chiave (tipo manager_id) aiutano a velocizzare.

Quando non puoi fare a meno delle query ricorsive

A volte sembra che le query ricorsive siano roba da teoria, ma in realtà le trovi spesso nello sviluppo di tutti i giorni. Per esempio:

• per costruire report sulla struttura aziendale o sulla classificazione dei prodotti;
• per attraversare l’albero delle cartelle e raccogliere la lista di tutte le directory annidate;
• per analizzare grafi — connessioni social, percorsi, dipendenze tra task;
• per semplicemente rappresentare relazioni complesse tra oggetti in modo leggibile.

Se devi attraversare una struttura dove una cosa dipende da un’altra — quasi sicuramente ti servirà WITH RECURSIVE.