0Pricing
SQL Academy · Lezione

Come funzionano le CTE ricorsive

Caso base più passaggio ricorsivo

Come funzionano le CTE ricorsive è una lezione SQL Academy gratuita su CoddyKit. Questa è la lezione 1 di 4. Puoi leggere la lezione completa qui gratuitamente — poi esercitati direttamente nel browser con un editor di codice integrato e un tutor IA disponibile 24/7. Fa parte del percorso di apprendimento SQL Academy, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso SQL Academy include 4 lezioni in totale.

Che cos'è una CTE ricorsiva?

Una CTE ricorsiva è una Common Table Expression che fa riferimento a se stessa. Consente di scrivere query che ripetono un passaggio fino al verificarsi di una condizione, in modo simile a un ciclo, ma utilizzando esclusivamente SQL.

Le CTE ricorsive vengono definite con la parola chiave WITH RECURSIVE e sono ideali per attraversare dati gerarchici o strutturati come grafi, ad esempio organigrammi, alberi di cartelle e strutture di distinta base.

La struttura in due parti

Ogni CTE ricorsiva ha esattamente due parti separate da UNION ALL:

1. Caso base — una SELECT non ricorsiva che restituisce le righe iniziali.

2. Passaggio ricorsivo — una SELECT che unisce la CTE a se stessa, producendo il livello successivo di righe.

Il motore continua a eseguire il passaggio ricorsivo e ad accumulare i risultati finché non produce nuove righe.

WITH RECURSIVE cte_name AS (
  -- Base case
  SELECT ...
  UNION ALL
  -- Recursive step (references cte_name)
  SELECT ... FROM source JOIN cte_name ON ...
)
SELECT * FROM cte_name;

Conteggio da 1 a 5

La CTE ricorsiva più semplice conta i numeri. Il caso base imposta il valore 1. A ogni iterazione, il passaggio ricorsivo aggiunge 1. La clausola WHERE all'interno del passaggio ricorsivo funge da condizione di terminazione: senza di essa, la query verrebbe eseguita all'infinito.

WITH RECURSIVE counter(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM counter WHERE n < 5
)
SELECT n FROM counter;

Esecuzione passo dopo passo

Ecco come il motore elabora la CTE del contatore, iterazione dopo iterazione:

Iterazione 0 (caso base): restituisce {1}.

Iterazione 1: applica il passaggio ricorsivo a {1} e restituisce {2}.

Iterazione 2: applica il passaggio ricorsivo a {2} e restituisce {3}.

Iterazioni 3 e 4: restituisce prima {4}, poi {5}.

Iterazione 5: WHERE n < 5 è falso per n=5, quindi non viene restituita alcuna riga. La query termina.

Tutte le righe accumulate — 1, 2, 3, 4, 5 — costituiscono il risultato finale.

Configurazione di una tabella gerarchica

Le CTE ricorsive sono particolarmente efficaci con le tabelle autoreferenziali. Creiamo una tabella employees in cui ogni dipendente ha un campo manager_id opzionale che punta alla stessa tabella.

CREATE TABLE employees (
  id       INTEGER PRIMARY KEY,
  name     VARCHAR(50),
  manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees VALUES
  (1, 'Alice',   NULL),
  (2, 'Bob',     1),
  (3, 'Carol',   1),
  (4, 'Dave',    2),
  (5, 'Eve',     2),
  (6, 'Frank',   3);

Attraversamento della gerarchia

Ora possiamo percorrere l'intera catena gerarchica a partire dal CEO (Alice, id=1). Il caso base seleziona Alice; il passaggio ricorsivo trova tutti i dipendenti il cui manager_id corrisponde a un id già presente nella CTE.

Il risultato include ogni dipendente raggiungibile da Alice, indipendentemente dalla profondità dell'albero.

WITH RECURSIVE org_tree AS (
  SELECT id, name, manager_id, 0 AS depth
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, ot.depth + 1
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT depth, name FROM org_tree ORDER BY depth, name;

Tracciamento del percorso

Un miglioramento comune consiste nel creare una stringa del percorso che mostri l'intera catena dalla radice a ogni nodo. Durante la ricorsione verso livelli più profondi, concateniamo i nomi separandoli con ' -> '.

In questo modo è facile visualizzare una navigazione in stile breadcrumb o eseguire il debug di gerarchie profonde.

WITH RECURSIVE org_tree AS (
  SELECT id, name, name AS path
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, ot.path || ' -> ' || e.name
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT name, path FROM org_tree ORDER BY path;

Limitazione della profondità della ricorsione

I dati profondi o circolari possono far eseguire una CTE ricorsiva per un tempo molto lungo. Ecco due pratiche sicure:

1. Tenga traccia della profondità e aggiunga una clausola WHERE — WHERE depth < 10 garantisce che non si superino 10 livelli.

2. Utilizzi una colonna per il rilevamento dei cicli — alcuni database (PostgreSQL 14+) offrono la sintassi CYCLE per rilevare automaticamente le visite ripetute dei nodi.

WITH RECURSIVE org_tree AS (
  SELECT id, name, 0 AS depth
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, ot.depth + 1
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
  WHERE ot.depth < 10
)
SELECT depth, name FROM org_tree;

UNION e UNION ALL nelle CTE ricorsive

Il passaggio ricorsivo utilizza quasi sempre UNION ALL, non UNION. Ecco il motivo:

UNION elimina i duplicati dopo ogni iterazione confrontando l'intero insieme di risultati: è estremamente costoso e può modificare la semantica dei grafi in cui lo stesso nodo viene raggiunto legittimamente attraverso più percorsi.

UNION ALL conserva tutte le righe senza eliminare i duplicati, risultando più veloce e corretto per l'attraversamento degli alberi. Utilizzi UNION solo quando ha una necessità specifica di eliminare i duplicati e comprende il costo in termini di prestazioni.

Generazione di una serie di date

Le CTE ricorsive sono utili anche per generare sequenze di date. Questo esempio produce ogni giorno di una determinata settimana, un modello spesso utilizzato per creare report di calendario o colmare lacune nei dati delle serie temporali.

WITH RECURSIVE date_series AS (
  SELECT DATE '2024-01-01' AS day
  UNION ALL
  SELECT day + INTERVAL '1 day'
  FROM date_series
  WHERE day < DATE '2024-01-07'
)
SELECT day FROM date_series;

Ricerca di tutti i subordinati di un manager

È possibile inizializzare il caso base con qualsiasi nodo specifico, non solo con la radice. Qui partiamo da Bob (id=2) e troviamo tutte le persone che riportano a lui direttamente o indirettamente.

Questo modello è utile per i controlli delle autorizzazioni, le aggregazioni di sottoalberi o per limitare i dashboard a un singolo reparto.

WITH RECURSIVE subordinates AS (
  SELECT id, name
  FROM employees
  WHERE id = 2
  UNION ALL
  SELECT e.id, e.name
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT name FROM subordinates;

Verifica rapida

Verifichi la Sua comprensione del funzionamento delle CTE ricorsive.

Riepilogo della lezione

In questa lezione ha imparato come funzionano le CTE ricorsive:

Struttura: ogni CTE ricorsiva ha un caso base (le righe iniziali) unito a un passaggio ricorsivo (una SELECT che fa riferimento a se stessa) tramite UNION ALL.

Terminazione: il motore ripete il passaggio ricorsivo e accumula i risultati finché il passaggio non restituisce alcuna riga.

Usi comuni: percorrere organigrammi e alberi di cartelle, generare sequenze di numeri o date, calcolare percorsi e trovare tutti i nodi di un sottoalbero.

Suggerimenti per la sicurezza: includa sempre una condizione di terminazione (limite di profondità o controllo dei cicli) e preferisca UNION ALL a UNION per ottenere prestazioni migliori.

Domande Frequenti

La lezione «Come funzionano le CTE ricorsive» è gratuita?

Sì — il testo completo di «Come funzionano le CTE ricorsive» è gratuito qui sul web. Per esercitarvi in modo interattivo (un editor di codice integrato e un tutor IA 24/7) e sbloccare il resto del corso SQL Academy, passa a CoddyKit PRO. Il corso SQL Academy include 4 lezioni in totale.

Cosa imparerò in «Come funzionano le CTE ricorsive»?

Caso base più passaggio ricorsivo Eserciti SQL Academy con codice pratico che esegui direttamente nel browser, e un tutor IA 24/7 risponde alle tue domande mentre lavori sulla lezione.

Ho bisogno di esperienza per iniziare SQL Academy?

Non è richiesta alcuna esperienza precedente. SQL Academy su CoddyKit è strutturato per principianti e studenti avanzati, quindi puoi iniziare da qui o dall'inizio e procedere al tuo ritmo. Questa è la lezione 1 di 4.

Quanto tempo richiede la lezione «Come funzionano le CTE ricorsive»?

La maggior parte delle lezioni CoddyKit richiede circa 5–10 minuti. Ogni lezione è breve e interattiva, quindi fai progressi costanti e riprendi esattamente da dove hai lasciato su web e app.

Posso scrivere ed eseguire codice in questa lezione SQL Academy?

Sì. Ogni lezione SQL Academy include un editor di codice integrato, quindi scrivi ed esegui codice reale direttamente nel tuo browser e ricevi feedback istantaneo dall'IA — nessuna configurazione locale necessaria.

Tutte le lezioni di questo corso

  1. Come funzionano le CTE ricorsive
  2. Percorrere un albero di categorie
  3. Generare serie e sequenze
  4. Evitare i cicli infiniti
← Torna a SQL Academy