Che cos'è un'operazione di self-join in SQL? Sette esempi

Le operazioni di self-join sono un caso speciale molto diffuso di JOIN in SQL. Mentre la maggior parte delle operazioni JOIN collega due o più tabelle tra loro per presentare i loro dati insieme, una self-join collega una tabella a se stessa. Di solito questo avviene unendo una tabella a se stessa una sola volta all'interno di una query SQL, ma è possibile farlo più volte all'interno della stessa query.

In genere, all'interno di ogni tabella di un database vengono memorizzate un tipo specifico di informazioni. Quindi, spesso in un database sono presenti centinaia di tabelle collegate tra loro. Ciò implica la necessità di effettuare operazioni di unione. È possibile unire tabelle diverse in base alle loro colonne comuni, utilizzando la parola chiave JOIN. È anche possibile unire una tabella a se stessa, con un'operazione nota come self-join. In questo articolo discuteremo cos'è una self-join, come funziona e quando è necessario usarla nelle query SQL.

Per esercitarsi con le operazioni JOIN in SQL, comprese le self-join, vi consiglio il nostro corso interattivo JOIN in SQL. Contiene oltre 90 esercizi su diversi tipi di operazioni JOIN, comprese sezioni dedicate proprio alle self-join.

Che cos'è un'operazione di self-join in SQL?

Una self-join, come dice il nome, unisce una tabella a se stessa. Per realizzare un'operazione di self-join, la tabella deve contenere una colonna (chiamata X) che funge da chiave primaria e un'altra colonna (chiamata Y) che memorizza valori che possono essere abbinati ai valori della colonna X. I valori delle colonne X e Y non devono necessariamente essere gli stessi per ogni riga e il valore della colonna Y può anche essere null.

Vediamo un esempio. Consideriamo la tabella dipendenti:

Ogni dipendente ha il proprio id, che è la nostra "Colonna X". Per un dato dipendente (cioè una riga), la colonna id_manager contiene il codice id del suo manager; questa è la nostra "Colonna Y". Se rintracciamo le coppie dipendente-manager in questa tabella utilizzando queste colonne, otteniamo:

• Il manager del dipendente John Smith è il dipendente con id 3, cioè Tom Lanon.
• Il manager del dipendente Jane Anderson è il dipendente con id 3, cioè Tom Lanon.
• Il manager del dipendente Tom Lanon è il dipendente con id 4, cioè Anne Connor.
• La dipendente Anne Connor non ha un manager; il suo id_manager è null.
• Il manager del dipendente Jeremy York è il dipendente con id 1, cioè John Smith.

Questo tipo di struttura delle tabelle è molto comune negli organigrammi. Ora, per mostrare il nome del manager di ciascun dipendente nella stessa riga, si può eseguire la seguente query:

SELECT
	dipendente.id,
    	dipendente.nome_completo,
    	dipendente.id_manager,
    	manager.nome_completo as nome_del_manager
FROM dipendenti dipendente
JOIN dipendenti manager
ON dipendente.id_manager = manager.id

che restituisce il seguente risultato:

La query seleziona le colonne id, nome_completo e id_manager dalla tabella con alias dipendente. Seleziona anche la colonna nome_completo della tabella con alias manager e la designa come nome_del_manager. Di conseguenza, ogni dipendente a cui è assegnato un manager viene visualizzato insieme all'id e al nome di quest'ultimo.

In questa query, la tabella dipendenti è unita a se stessa e ha due ruoli diversi:

• Ruolo 1: memorizza i dati dei dipendenti (alias dipendente).
• Ruolo 2: memorizza i dati dei manager (alias manager).

In questo modo, si considerano essenzialmente le due copie della tabella dipendenti come se fossero due tabelle distinte, una per i dipendenti e una per i manager.

Per saperne di più sul concetto di self-join, consultate il nostro articolo Guida illustrata alle operazioni di self-join in SQL.

Alias delle tabelle nelle operazioni di self-join

Quando si fa riferimento alla stessa tabella più di una volta in una query SQL, è necessario distinguere ogni riferimento dagli altri. Per questo motivo, è importante utilizzare alias per identificare in modo univoco ogni riferimento alla stessa tabella in una query SQL. Come buona prassi, gli alias devono indicare il ruolo della tabella per ogni specifico riferimento in una query.

Gli alias sono segnati in rosso nella query seguente. Puoi notare la loro dichiarazione nelle clausole FROM e JOIN.

SELECT
	dipendente.id,
    	dipendente.nome_completo,
    	dipendente.id_manager,
    	manager.nome_completo as nome_del_manager
FROM dipendenti dipendente
JOIN dipendenti manager
ON dipendente.id_manager = manager.id

La parola chiave JOIN collega due tabelle ed è solitamente seguita da una clausola ON o USING che specifica le colonne comuni utilizzate per collegare le due tabelle. In questo caso, vediamo che i due riferimenti alla tabella dipendenti sono collegati utilizzando come condizione la corrispondenza tra l'id_manager di un dipendente con l'id dipendente del manager.

Esempi

Vediamo alcuni scenari comuni in cui viene utilizzata l'operazione di self-join.

Scenario 1: elaborazione di un organigramma in SQL

Le operazioni di self-join vengono usate comunemente nell'elaborazione degli organigrammi. Come abbiamo visto in precedenza, all'interno di un organigramma viene assegnata una riga di una tabella a un'altra riga della stessa tabella. Potete pensare che questo tipo di dati vengono espressi con righe padre e righe figlio.

Torniamo all'esempio con i dipendenti e i rispettivi manager. Ecco di nuovo la tabella dipendenti:

E il codice per elencare ogni dipendente che ha un manager con il nome del proprio manager:

SELECT
	dipendente.id,
    	dipendente.nome_completo,
    	dipendente.id_manager,
    	manager.nome_completo as nome_del_manager
FROM dipendenti dipendente
JOIN dipendenti manager
ON dipendente.id_manager = manager.id

Ecco il risultato dell'esecuzione del codice:

Questa query utilizza l'operatore JOIN standard, noto anche come INNER JOIN. Per saperne di più su INNER JOIN, consultate il nostro articolo Guida illustrata a INNER JOIN in SQL.

Se si desidera elencare tutti i dipendenti, indipendentemente dal fatto che abbiano o meno un manager, è possibile utilizzare l'operatore LEFT OUTER JOIN. Potete vederne un esempio nella query di seguito:

SELECT
	dipendente.id,
    	dipendente.nome_completo,
    	dipendente.id_manager,
    	manager.nome_completo as nome_del_manager
FROM dipendenti dipendente
LEFT OUTER JOIN dipendenti manager
ON dipendente.id_manager = manager.id

Quando si esegue questa query, si ottiene il seguente risultato:

La differenza tra JOIN e LEFT OUTER JOIN diventa evidente se confrontiamo il risultato di quest'ultima query SQL con quella precedente. Nel risultato della query (INNER) JOIN sono inclusi solo i dipendenti con manager. Al contrario, la query LEFT OUTER JOIN restituisce tutti i dipendenti, con o senza manager. Per saperne di più sugli operatori OUTER JOIN, leggete il nostro articolo Guida illustrata ad OUTER JOIN in SQL.

Un altro esempio di gerarchia è la relazione tra genitori e figli. Si consideri la tabella umani mostrata qui:

Nella query che segue, i figli vengono assegnati ai rispettivi genitori unendo la tabella umani a se stessa:

SELECT
	figli.id as id_figlio,
	figli.nome as nome_del_figlio,
	figli.eta as eta_del_figlio,
	figli.id_genitore,
	genitori.nome as nome_del_genitore,
	genitori.eta as eta_del_genitore
FROM umani figli
INNER JOIN umani genitori
ON figli.id_genitore = genitori.id

Ecco il risultato di questa query:

Il risultato della query include solo i figli che hanno i genitori. Come nell'esempio dell'organigramma dipendenti-dirigenti, è possibile utilizzare LEFT OUTER JOIN per includere tutte le righe della tabella con alias figli.

Ecco un altro esempio di set di dati gerarchico. Si consideri la tabella categorie mostrata di seguito:

Quando è possibile, assegniamo una categoria padre a ogni categoria. Ecco una query per farlo:

SELECT
	cat.id,
	cat.quantita,
	cat.categoria,
	cat.id_categoria_padre,
	cat_padre.categoria as categoria_padre
FROM categorie cat
JOIN categorie cat_padre
ON cat.id_categoria_padre = cat_padre.id

Ed ecco il risultato:

Le prime quattro colonne del risultato sopra riportato provengono dal riferimento alla tabella con alias cat. L'ultima colonna proviene dalla tabella con alias cat_padre e contiene il nome della categoria padre a cui corrisponde il rispettivo id.

La tabella categoria riveste due ruoli diversi, come indicato dai due riferimenti distinti. La colonna id_categoria_padre della tabella con alias cat è abbinata a l'id della tabella con alias cat_padre. La clausola ON specifica che id_categoria_padre di cat deve essere uguale all'id di cat_padre per collegare le righe corrispondenti.

Scenario 2: elencare le coppie all'interno di una tabella

È possibile utilizzare un'operazione di self-join per generare coppie di righe in base alla condizione della clausola ON. Cominciamo con un semplice esempio che genera tutte le coppie possibili tra i colleghi. Consideriamo la seguente tabella, colleghi:

Supponiamo di dover generare tutte le coppie possibili tra i colleghi, in modo che tutti abbiano la possibilità di parlare con tutti gli altri alla serata di presentazione dell'azienda. Ecco il codice SQL:

SELECT
	compagno1.nome_completo as nome_completo_compagno1,
	compagno1.eta as eta_compagno1,
	compagno2.nome_completo as nome_completo_compagno2,
	compagno2.eta as eta_compagno2
FROM colleghi compagno1
CROSS JOIN colleghi compagno2
ON compagno1.nome_completo <> compagno2.nome_completo

Ed ecco il risultato:

Il risultato fa corrispondere ogni singola persona della tabella con ogni altra. Poiché non vogliamo che nessuno sia abbinato a se stesso, abbiamo inserito la condizione della clausola ON compagno1.nome_completo <> compagno2.nome_completo . Ciò significa che ogni persona sarà abbinata ad altri tre colleghi, perché in questo evento ci sono quattro colleghi.

Vediamo ora un esempio un po' più complicato. Consideriamo la tabella umani riportata di seguito. Vogliamo abbinare tutti gli antenati a ogni persona, laddove i dati lo consentano, con la condizione che gli antenati abbiano un valore id più alto o uguale rispetto all'id_genitore della persona.

Di seguito è riportata la tabella umani utilizzata in questo esempio.

Troviamo tutte le coppie discendente-antenato nella tabella precedente. Ecco il codice SQL:

SELECT
	discendente.id,
	discendente.nome,
	discendente.eta,
	discendente.id_genitore,
	antenato.id as id_antenato,
	antenato.nome as nome_antenato,
	antenato.eta as eta_antenato
FROM umani discendente
LEFT JOIN umani antenato
ON discendente.id_genitore <= antenato.id

E il risultato:

Specificando la condizione della clausola ON discendente.id_genitore <= antenato.id , troviamo tutti gli antenati presenti di ogni persona nella tabella; in caso contrario, la query restituisce null per le informazioni sugli antenati.

Le prime quattro colonne sono prese dalla tabella con alias discendente che contiene le informazioni sulla persona per la quale si cercano gli antenati. Le ultime tre colonne sono tratte dalla tabella con alias antenato e contengono dettagli su ciascun antenato.

Scenario 3: self-join in combinazione con un'altra tabella

In SQL è possibile utilizzare un'operazione di self-join in combinazione con una o più tabelle diverse. Anche se non si tratta di una self-join pura, si tratta di un impiego molto comune nella pratica.

Un esempio reale è quello delle informazioni sui voli negli aeroporti, con un'enorme quantità di dati ogni ora. Supponiamo di voler cercare il numero di identificazione di un volo insieme ai dettagli sugli aeroporti di partenza e di destinazione. Consideriamo le seguenti tabelle:

Tabella aeroporti:

Tabella voli:

Qui notate che la colonna id_aeroporto della tabella aeroporti è la chiave esterna delle colonne id_aeroporto_di_partenza e id_aeroporto_di_arrivo della tabella voli. Uniremo la tabella aeroporti alla tabella voli due volte distinte, come segue:

• Nella prima operazione JOIN, aeroporti prende il ruolo della tabella con gli aeroporti di partenza.
• Nella seconda operazione JOIN, aeroporti prende il ruolo della tabella con gli aeroporti di arrivo.

La query ha questo aspetto:

SELECT
	volo.id_volo,
	volo.id_aereo,
	volo.id_aeroporto_di_partenza,
	aeroportopartenza.paese as paese_aeroporto_di_partenza,
	aeroportopartenza.citta as citta_aeroporto_di_partenza,
	volo.id_aeroporto_di_arrivo,
	aeroportoarrivo.paese as paese_aeroporto_di_arrivo,
	aeroportoarrivo.citta as citta_aeroporto_di_arrivo
FROM voli volo
JOIN aeroporti aeroportopartenza
ON volo.id_aeroporto_di_partenza = aeroportopartenza.id_aeroporto
JOIN aeroporti aeroportoarrivo
ON flight.id_aeroporto_di_arrivo = aeroportoarrivo.id_aeroporto

E il risultato della query si presenta così:

Analizziamo il risultato. Le prime tre colonne provengono da un semplice SELECT della tabella voli. Le due colonne successive provengono da aeroporti nel ruolo di tabella degli aeroporti di partenza; le righe sono abbinate in base a id_aeroporto e id_aeroporto_di_partenza delle tabelle aeroporti e voli rispettivamente. Segue una colonna della tabella voli. Le ultime due colonne provengono da aeroporti, questa volta nel ruolo di tabella degli aeroporti di arrivo; le righe sono abbinate in base a id_aeroporto e id_aeroporto_di_arrivo rispettivamente dalle tabelle aeroporti e voli.

Siete ancora un po' confusi su tutte le operazioni JOIN? Esistono molti altri articoli che potete consultare per trovare aiuto. Vi consiglio in particolare l'articolo su Come imparare gli operatori JOIN in SQL. E se avete bisogno di iniziare a esercitarvi, visitate il nostro articolo su Come esercitarsi con gli operatori JOIN in SQL.

Self-join: un caso speciale di operazione di unione

Come abbiamo visto, l'operazione di self-join è un importante caso speciale di unione. Abbiamo visto esempi di varie applicazioni di self-join, tra cui l'elaborazione di una gerarchia in una tabella e l'accoppiamento delle righe in una tabella. Possiamo unire la stessa tabella più volte, ma è importante dare a ogni riferimento un alias che ne indichi il ruolo. Questi alias delle tabelle vengono utilizzati per recuperare le colonne da una singola tabella, in base al ruolo che interpreta nel momento in cui viene usata come riferimento.

Le operazioni di unione sono una parte fondamentale di SQL, una funzione molto utile e frequentemente utilizzata per combinare tabelle diverse. Si trovano dappertutto: date un'occhiata al nostro corso su JOIN in SQL per imparare a usare questo potente strumento!