0Pricing
SQL Academy · Lezione

Limiti dei self join

Scopra quando è invece necessaria la ricorsione

Limiti dei self join è una lezione SQL Academy gratuita su CoddyKit. Questa è la lezione 4 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'è un self join?

Un self join si verifica quando una tabella viene unita a se stessa. È utile per confrontare righe della stessa tabella, ad esempio per trovare i dipendenti e i relativi manager memorizzati in un'unica tabella employees.

Prima di esplorarne i limiti, ricordiamo come funziona in pratica un self join di base.

SELECT e.name AS employee, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.id;

Un livello di profondità

Un self join gestisce elegantemente un passaggio in una gerarchia. Se desidera associare ogni dipendente al proprio manager diretto, è sufficiente un self join.

Questa soluzione funziona perfettamente quando i dati hanno un solo livello di profondità o quando interessano solo le relazioni dirette tra padre e figlio.

SELECT child.name AS employee, parent.name AS direct_manager
FROM employees child
LEFT JOIN employees parent ON child.manager_id = parent.id;

Due livelli: la situazione si complica

Che cosa succede se deve includere i dipendenti, i loro manager e i manager dei loro manager? Deve aggiungere un secondo self join. La query cresce e diventa più difficile da leggere.

Ogni livello gerarchico aggiuntivo richiede un altro alias per il join e un'altra clausola JOIN.

SELECT e.name AS employee,
       m.name AS manager,
       gm.name AS grand_manager
FROM employees e
LEFT JOIN employees m  ON e.manager_id = m.id
LEFT JOIN employees gm ON m.manager_id = gm.id;

Tre livelli: il modello non regge

L'aggiunta di un terzo livello impone un ulteriore join. A questo punto la query è prolissa, fragile e difficile da mantenere. Se cambia la profondità della gerarchia, deve riscrivere l'intera query.

Questo è il primo grande limite dei self join: non sono scalabili rispetto alla profondità.

SELECT e.name AS employee,
       m.name AS manager,
       gm.name AS grand_manager,
       ggm.name AS great_grand_manager
FROM employees e
LEFT JOIN employees m   ON e.manager_id = m.id
LEFT JOIN employees gm  ON m.manager_id = gm.id
LEFT JOIN employees ggm ON gm.manager_id = ggm.id;

Profondità sconosciuta: i self join non bastano

Negli organigrammi o negli alberi di categorie reali, la profondità è spesso sconosciuta al momento dell'esecuzione della query. I self join richiedono di fissare nel codice il numero di livelli. Se domani la gerarchia avrà 10 livelli, la query con self join a 3 livelli perderà silenziosamente i dati.

Si tratta di una limitazione fondamentale: i self join non possono attraversare un numero arbitrario di livelli.

-- This only retrieves up to 3 levels deep.
-- Employees deeper than level 3 are simply missing from results.
SELECT e.name, m.name, gm.name
FROM employees e
LEFT JOIN employees m  ON e.manager_id = m.id
LEFT JOIN employees gm ON m.manager_id = gm.id;

I cicli rendono completamente inutilizzabili i self join

Un'altra seria limitazione: se i dati contengono un ciclo (A gestisce B, B gestisce C, C gestisce A), una query con self join non entra in un ciclo infinito, ma non rileva né segnala correttamente il ciclo.

Non è possibile proteggersi dai riferimenti circolari utilizzando semplici self join. Le query ricorsive dispongono di meccanismi integrati per il rilevamento dei cicli, completamente assenti nei self join.

-- Cyclic data: row 3 points back to row 1
-- id | name    | manager_id
--  1 | Alice   | 3   <-- cycle!
--  2 | Bob     | 1
--  3 | Charlie | 2

-- A self join just shows one hop; it cannot detect the loop
SELECT e.name, m.name AS reports_to
FROM employees e
JOIN employees m ON e.manager_id = m.id;

Introduzione alle CTE ricorsive

SQL offre una soluzione progettata appositamente per attraversare gerarchie di profondità sconosciuta: la Common Table Expression (CTE) ricorsiva. Utilizza la sintassi WITH RECURSIVE supportata da PostgreSQL, MySQL 8+, SQLite e SQL Server.

Una CTE ricorsiva ha due parti: un membro di ancoraggio (le righe iniziali) e un membro ricorsivo (il passaggio che segue ogni relazione).

WITH RECURSIVE org_tree AS (
  -- Anchor: start with the top-level CEO (no manager)
  SELECT id, name, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive: find each employee whose manager is already in org_tree
  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 name, depth FROM org_tree ORDER BY depth;

Tracciamento del percorso completo

Una caratteristica potente delle CTE ricorsive è la possibilità di accumulare il contesto durante la discesa nella gerarchia. Ad esempio, è possibile costruire il percorso completo dalla radice a ogni nodo, qualcosa di completamente impossibile con un self join statico.

WITH RECURSIVE org_tree AS (
  SELECT id, name, manager_id,
         name AS path
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.id, e.name, e.manager_id,
         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;

Self join o CTE ricorsiva: quale scegliere

Utilizzi un self join quando:

  • Ha bisogno esattamente di uno o due livelli gerarchici.
  • La profondità è fissa e nota in anticipo.
  • Desidera una soluzione semplice, senza l'overhead di una CTE.

Utilizzi una CTE ricorsiva quando:

  • La profondità è variabile o sconosciuta.
  • Ha bisogno del percorso completo degli antenati o dei discendenti.
  • Desidera rilevare i cicli tramite la clausola CYCLE o mediante controlli manuali.

Considerazioni sulle prestazioni

I self join su colonne indicizzate sono estremamente veloci per le query a profondità fissa. Ogni join è una singola ricerca e l'ottimizzatore del database li gestisce efficacemente.

Le CTE ricorsive sono più flessibili, ma possono essere costose su alberi profondi o molto ramificati. Aggiunga sempre un controllo del limite di profondità nel membro ricorsivo, per evitare query senza fine causate da dati errati o cicli imprevisti.

WITH RECURSIVE org_tree AS (
  SELECT id, name, manager_id, 1 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
  WHERE ot.depth < 10   -- safety guard: stop at depth 10
)
SELECT name, depth FROM org_tree;

Casi d'uso reali che richiedono la ricorsione

Molti modelli di dati comuni richiedono un attraversamento a profondità arbitraria, che i self join semplicemente non sono in grado di gestire:

  • Alberi di categorie — categorie di prodotti annidate in un catalogo e-commerce.
  • Distinte base — un prodotto composto da parti, a loro volta composte da sottoparti.
  • Thread di commenti — risposte a risposte a risposte.
  • Percorsi del file system — directory contenute in altre directory.

In tutti questi casi, scelga una CTE ricorsiva invece di concatenare self join.

WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id, name AS full_path
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  SELECT c.id, c.name, c.parent_id,
         ct.full_path || ' / ' || c.name
  FROM categories c
  JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT id, name, full_path FROM category_tree ORDER BY full_path;

Verifica delle conoscenze

Verifichi la Sua comprensione dei limiti dei self join e dei casi in cui utilizzare invece le CTE ricorsive.

Riepilogo della lezione

In questa lezione ha imparato i limiti dei self join nella gestione dei dati gerarchici:

  • I self join funzionano bene per uno o due livelli fissi di gerarchia.
  • Ogni livello aggiuntivo richiede un altro JOIN esplicito, rendendo le query fragili e difficili da mantenere.
  • I self join non gestiscono profondità sconosciute: le righe oltre i livelli fissati nel codice vengono escluse silenziosamente.
  • Non offrono alcuna protezione contro i riferimenti ciclici nei dati.
  • Quando la profondità è variabile o sconosciuta, utilizzi invece una CTE ricorsiva (WITH RECURSIVE).
  • Aggiunga sempre un limite di profondità nelle query ricorsive, per proteggersi da esecuzioni senza fine.

Sapere quando passare da un self join a una CTE ricorsiva è una competenza fondamentale per interrogare qualsiasi dato strutturato come un albero in SQL.

Domande Frequenti

La lezione «Limiti dei self join» è gratuita?

Sì — il testo completo di «Limiti dei self join» è 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 «Limiti dei self join»?

Scopra quando è invece necessaria la ricorsione 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 4 di 4.

Quanto tempo richiede la lezione «Limiti dei self join»?

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. Che cos'è un self join
  2. Dipendenti e responsabili
  3. Confrontare righe nella stessa tabella
  4. Limiti dei self join
← Torna a SQL Academy