Coding Interview Prep · Lezione

SELF JOIN per le gerarchie

Unire una tabella a se stessa per modellare relazioni dipendente-manager e padre-figlio

SELF JOIN per le gerarchie è una lezione Coding Interview Prep gratuita su CoddyKit. Questa è la lezione 2 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 Coding Interview Prep, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso Coding Interview Prep include 4 lezioni in totale.

Che cos'è davvero un SELF JOIN

Un self join è semplicemente un join in cui una tabella compare su entrambi i lati. Non esiste una parola chiave speciale SELF JOIN; si scrive un normale INNER o LEFT JOIN e si fa riferimento due volte alla stessa tabella.

Il meccanismo che lo rende possibile sono gli alias di tabella. Si assegna un alias diverso a ogni copia, così il motore le tratta come due tabelle indipendenti.

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

Perché gli alias sono obbligatori

Senza alias distinti, la query è ambigua: ogni nome di colonna compare due volte e il motore non può capire a quale copia ci si riferisca. Assegnare un alias a ogni istanza risolve il problema.

Legga il join come "abbinare ogni riga di dipendente alla riga del dipendente che ne è il responsabile". L'alias e rappresenta il collaboratore, m il responsabile, ed entrambi provengono dalla stessa tabella fisica.

-- e = the employee, m = that employee's manager
SELECT e.id, e.name, m.name AS reports_to
FROM employees AS e
JOIN employees AS m ON e.manager_id = m.id;

Il modello dipendente-responsabile

Lo scenario classico per un self join è un elenco di adiacenza: una singola tabella memorizza le righe e ogni riga fa riferimento al proprio elemento padre tramite una chiave esterna verso la stessa tabella.

Una tabella employees con un manager_id che fa riferimento a employees.id rappresenta un intero organigramma in una sola tabella. Ogni responsabile è semplicemente un'altra riga della tabella dei dipendenti.

-- One table holds the whole hierarchy
-- employees(id, name, manager_id)
-- manager_id -> employees.id

Elencare tutti con il proprio responsabile

La domanda più frequente sui self join: mostrare ogni dipendente accanto al nome del proprio responsabile. Si unisce la copia dei dipendenti alla copia dei responsabili tramite e.manager_id = m.id.

Questo restituisce una riga per ogni dipendente il cui responsabile esiste. Noti che il vertice dell'organigramma, cioè l'amministratore delegato, ha manager_id NULL e viene escluso da un inner join.

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

LEFT JOIN per mantenere il vertice dell'albero

Per includere l'amministratore delegato, il cui manager_id è NULL, passi a un LEFT JOIN. Il lato dei dipendenti viene preservato; le colonne del responsabile risultano NULL per le righe senza elemento padre.

Gli intervistatori usano questo caso per verificare che si ricordi che un self join interno elimina i nodi radice. La correzione è la stessa di qualsiasi situazione di outer join in cui occorre "mantenere le righe senza corrispondenza".

SELECT e.name AS employee,
       COALESCE(m.name, '(top level)') AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Contare i riporti diretti per ogni responsabile

Una domanda frequente di approfondimento: quante persone riportano direttamente a ciascun responsabile? Si esegue un self join e poi si raggruppa per responsabile.

Uniamo i collaboratori ai responsabili, raggruppiamo per l'identificativo del responsabile e contiamo i collaboratori. In questo modo si contano solo i riporti diretti, non l'intero sottoalbero che dipende da ciascun responsabile.

SELECT m.name AS manager, COUNT(*) AS direct_reports
FROM employees e
JOIN employees m ON e.manager_id = m.id
GROUP BY m.id, m.name
ORDER BY direct_reports DESC;

Scendere di due livelli

Per ottenere un dipendente, il suo responsabile e il responsabile del suo responsabile, si concatenano tre copie della tabella. Ogni livello aggiuntivo richiede un altro self join.

Questo funziona per una profondità fissa e nota. Se serve una profondità arbitraria, un self join non è sufficiente: è il momento di usare una CTE ricorsiva, che gli intervistatori si aspettano venga menzionata.

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

Self join rispetto a CTE ricorsiva

Distinzione fondamentale verificata dagli intervistatori:

Un self join gestisce un numero fisso di livelli. Tre copie corrispondono a tre livelli, non di più.
Una CTE ricorsiva gestisce una profondità illimitata rieseguendo il join della tabella con sé stessa finché non compaiono più righe nuove.

Quindi, per "mostrare ogni dipendente e il suo responsabile diretto" basta un self join, mentre per "elencare ogni antenato lungo la catena" serve la ricorsione.

Categorie padre-figlio

Lo stesso schema rappresenta qualsiasi albero: categorie di prodotti, discussioni di commenti, regioni geografiche. Una tabella categories con parent_id che fa riferimento al proprio id ha la stessa struttura del caso dipendente-responsabile.

Riconoscere che "una tabella con una chiave esterna che fa riferimento a sé stessa" equivale a "self join o ricorsione" è l'idea riutilizzabile.

SELECT c.name AS category,
       p.name AS parent_category
FROM categories c
LEFT JOIN categories p ON c.parent_id = p.id;

Errori comuni nei self join

Nei colloqui, presti attenzione a questi aspetti:

Dimenticare gli alias, causando errori di ambiguità delle colonne.
Usare INNER JOIN ed eliminare silenziosamente le righe radice, il cui padre è NULL.
Unire nella direzione sbagliata: e.id = m.manager_id invece di e.manager_id = m.id.

Prima di scrivere la clausola ON, dichiari sempre ad alta voce quale alias rappresenta il figlio e quale il padre.

Quando usare un self join

Ricorra a un self join ogni volta che una tabella mette in relazione le proprie righe con altre righe della stessa tabella:

Gerarchie con un solo livello fisso di ricerca, ad esempio dipendente e responsabile.
Abbinare o confrontare le righe di una tabella, come verrà illustrato nella lezione successiva.

Se la relazione è ricorsiva e non ha limiti, indichi una CTE ricorsiva come strumento più adatto. Questa precisazione distingue gli sviluppatori junior da quelli di livello intermedio.

Verifica rapida

Verifichi la propria comprensione dei self join nelle gerarchie.

Riepilogo: SELF JOIN per le gerarchie

Punti chiave:

Un self join è un join normale con la stessa tabella su entrambi i lati, distinta tramite gli alias.
Un elenco di adiacenza, cioè una chiave esterna che fa riferimento a sé stessa come manager_id, rappresenta un albero in un'unica tabella.
Usi INNER JOIN per le coppie con corrispondenza e LEFT JOIN per mantenere le righe radice con padri NULL.
I self join gestiscono una profondità fissa; per una navigazione senza limiti serve una CTE ricorsiva.

Domande Frequenti

La lezione «SELF JOIN per le gerarchie» è gratuita?

Sì — il testo completo di «SELF JOIN per le gerarchie» è 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 Coding Interview Prep, passa a CoddyKit PRO. Il corso Coding Interview Prep include 4 lezioni in totale.

Cosa imparerò in «SELF JOIN per le gerarchie»?

Unire una tabella a se stessa per modellare relazioni dipendente-manager e padre-figlio Eserciti Coding Interview Prep 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 Coding Interview Prep?

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

Quanto tempo richiede la lezione «SELF JOIN per le gerarchie»?

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 Coding Interview Prep?

Sì. Ogni lezione Coding Interview Prep 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

CROSS JOIN e prodotti cartesiani
SELF JOIN per le gerarchie
Confrontare le righe all'interno di una tabella
Scegliere il tipo di join corretto

← Torna a Coding Interview Prep