Guida illustrata all'autogiunzione SQL

Cos'è e come funziona un self join in SQL? Quando è opportuno utilizzarlo? Troveremo le risposte a queste domande!

In SQL, possiamo combinare i dati di più tabelle utilizzando l'operatore JOIN. JOIN ha diverse varianti; abbiamo già parlato di CROSS JOIN, INNER JOIN e OUTER JOIN. Nella maggior parte dei casi, questi operatori uniscono i dati di due o più tabelle diverse. Potete esercitarvi con tutti i diversi tipi di JOINs nel nostro corso interattivo. SQL JOINs corso interattivo. In questo articolo, tuttavia, spiegheremo come unire i record di una stessa tabella.

Un self join SQL unisce dati provenienti dalla stessa tabella. In altre parole, unisce una tabella con se stessa. I record presi dalla tabella vengono abbinati ad altri record della stessa tabella. Perché si fa questa operazione? Potrebbe essere necessario confrontare un valore con un altro valore della stessa riga. Non è possibile farlo se non unendo la tabella a se stessa e confrontando i valori come se fossero due record separati.

Non esiste un operatore specifico per questo tipo di query. Invece, l'auto-unione SQL utilizza l'operatore INNER JOIN, uno degli operatori OUTER JOIN o un CROSS JOIN. La differenza è che una singola tabella è elencata sia come tabella di sinistra che di destra nell'unione.

Vediamo un esempio di self join SQL basato sulla tabella employee, mostrata di seguito:

La tabella employee contiene i numeri identificativi dei dipendenti, i nomi, i cognomi, gli stipendi e il numero identificativo del loro manager. A eccezione del capo (di cui ID = 1), tutti hanno un supervisore. Solo il capo può avere un valore NULL nella colonna manager_id.

Proviamo a eseguire una query che restituisca il superiore diretto di ciascun dipendente:

SELECT e.id, e.first_name, e.last_name,  e.salary, 
  m.first_name AS fname_boss, m.last_name AS lname_boss
FROM employee e
JOIN employee m ON  e.manager_id = m.id ;

Si noti che abbiamo usato la tabella employee sia come tabella di sinistra che di destra nel join. Per unire dati provenienti dalla stessa tabella, abbiamo dovuto assegnare due alias al nome della tabella.

È necessario utilizzare gli alias quando si eseguono le auto-unioni. Poiché si uniscono colonne della stessa tabella, esse avranno gli stessi nomi. L'alias rinomina le colonne in modo che il motore del database possa eseguire la query. Nella query precedente, abbiamo usato la lettera e come alias per la tabella di sinistra. In questo caso, stiamo utilizzando la tabella employee per i record dei dipendenti. Abbiamo usato la lettera m come alias per la tabella di destra, che utilizza la tabella employee per i record dei manager. Anche se stiamo usando una sola tabella, SQL la tratta come due tabelle diverse.

Abbiamo selezionato i dati dei dipendenti selezionando le colonne id, first_name e last_name nella tabella "tabella". e "tabella". I dati dei manager sono stati selezionati facendo riferimento alla "tabella". m "tabella". Si noti che i record dei dipendenti hanno il prefisso "e" e quelli dei manager il prefisso "m". Ora possiamo unirli perché abbiamo usato una condizione di unione appropriata: la colonna manager_id della tabella è stata confrontata con la colonna . e è stata confrontata con la colonna id della tabella m. I record di entrambe le tabelle sono stati abbinati utilizzando l'operatore JOIN (o INNER JOIN; ricordate che il "INNER" è implicito).

Avete notato che il capo non viene visualizzato nei risultati? Non c'è alcuna corrispondenza nella tabella di destra "manager", quindi non è stato possibile trovare dati sul superiore del capo.

L'immagine seguente mostra come vengono abbinati i record durante l'esecuzione della query.

Analizziamo i dati nella tabella di output:

Nella nostra tabella, JOIN non ha restituito un record per John Watson. Non ha un identificatore che punta all'id del suo superiore, quindi è il capo. Dovremmo usare un LEFT JOIN invece di un INNER JOIN se volessimo restituire i dati di tutti i dipendenti, compreso il capo. In questo caso, verrebbero mostrati anche i record della tabella di sinistra che non trovano corrispondenza nella tabella di destra:

SELECT e.id, e.first_name, e.last_name,  e.salary, m.first_name 
   AS fname_boss  , m.last_name  AS lname_boss
FROM employee e
LEFT JOIN employee m ON  e.manager_id = m.id ;

Il nostro corso interattivo SQL JOINs ha un'intera sezione dedicata alla pratica dei self join.

Quando usare le auto-unioni in SQL?

I self join sono comunemente utilizzati nelle seguenti aree:

• Relazioni gerarchiche
• Relazioni sequenziali
• Dati grafici

Analizzeremo ciascuna di queste aree singolarmente.

Dati gerarchici

L'elaborazione di dati gerarchici è una delle applicazioni più frequenti del self join SQL. Questo avviene quando c'è una colonna aggiuntiva che punta a un identificatore nella stessa tabella, come nel caso della nostra tabella employee come nella nostra tabella. Nel nostro caso, la colonna manager_id fa riferimento (ha lo stesso valore) alla colonna id.

L'esempio sopra riportato (la relazione dipendente-direttore) non è l'unica situazione in cui si utilizzano dati gerarchici. A distinta base per un'automobile ha una struttura simile. Ogni automobile è composta da più componenti, come il motore, l'impianto frenante e l'impianto elettrico. Ognuno di questi componenti è costituito da parti più piccole. L'impianto elettrico di un'auto può essere suddiviso in componenti, come la batteria e l'alternatore (che possono essere suddivisi in un numero ancora maggiore di parti dell'auto). Ciò significa che le parti dell'auto costituiscono un gruppo di dati gerarchici. La tabella car tabella seguente mostra alcuni dati relativi alle parti dell'auto.

Un altro esempio di dati gerarchici è la relazione genitore-figlio. Memorizzando questa relazione, possiamo utilizzare una singola tabella per ospitare un intero albero genealogico. Possiamo quindi utilizzare un self join SQL per recuperare facilmente i dati sugli antenati di una determinata persona.

La tabella seguente può aiutarci a identificare rapidamente gli antenati più anziani di una famiglia. Queste persone non hanno dati sugli antenati nei loro record, il che significa che costituiscono la radice dell'albero genealogico.

La tabella person tabella presentata di seguito illustra questo aspetto:

La query seguente recupera il nome e il cognome di ogni persona, insieme al nome e al cognome della madre e del padre.

SELECT c.first_name, c.last_name, m.first_name AS fname_mother, m.last_name  AS lname_mother
FROM family c
LEFT JOIN person m ON  c.mather_id = m.id 
LEFT JOIN person f ON  c.father_id = f.id ;

Si noti che è stato necessario utilizzare un self join tre volte (cioè su tre "tabelle" o alias) per ottenere i dati relativi al padre e alla madre. John Watson, Anne Brown e Jacob Miller non hanno dati che puntano ai loro antenati.

Gli esempi di strutture gerarchiche discussi in precedenza memorizzano i dati utilizzando un approccio di record superiore-inferiore. Questo ci permette di presentare i dati come una struttura ad albero. L'elaborazione delle strutture ad albero in SQL verrà approfondita man mano che si continuerà a conoscere i database relazionali.

Dati sequenziali

Anche i dati sequenziali possono trarre vantaggio dall'uso di un self join in SQL. Per esempio, supponiamo di avere dei record che descrivono le fasi consecutive necessarie per preparare un piatto. Tutti i passaggi possono essere inseriti in un'unica tabella. Il loro ordine viene determinato in base alle colonne che puntano agli ID dei record precedenti e successivi della stessa tabella.

Illustriamo questo utilizzando la tabella instruction tabella:

Come si vede, nell'ordine attuale queste istruzioni non hanno senso. Ma se utilizziamo un self join SQL per mostrare la relazione sequenziale per ogni fase della ricetta, otteniamo:

1. Preriscaldare il forno a 220 gradi C.
2. Sbucciare quattro patate.
3. Tagliare le patate a fette.
4. Aggiungere l'olio alle patate tagliate a fette.
5. Cuocere nel forno preriscaldato per 20 minuti.
6. Condire le fette calde con sale e pepe.

Otteniamo anche delle gustose patatine fritte al forno!

Grafici

L'SQL self join può essere utilizzato anche per mostrare le relazioni necessarie per i grafi. Un grafo è una struttura composta da nodi collegati tra loro da spigoli (relazioni). Un esempio di grafo è la rete stradale tra più città.

Osservate il disegno qui sotto.

Questo grafico rappresenta cinque città collegate tra loro. Ogni freccia indica una strada che collega una città all'altra. In questo caso, le città sono i nodi e le strade che le collegano sono i bordi. Utilizziamo due tabelle per memorizzare questi dati. La tabella city memorizza il numero ID e il nome di ogni città. La tabella route contiene il numero ID del percorso, la città di partenza (colonna from_city_id ) e la città di arrivo (colonna to_city_id ).

Questa è la tabella "città":

E questa è la tabella "percorso":

È possibile utilizzare un self join SQL sulla tabella city insieme a una INNER JOIN delle due tabelle, per scoprire quali percorsi esistono tra le città.

Guardate la query.

SELECT c1.name AS from_city, c2.name AS to_city 
FROM city c1
JOIN route r ON c1.id = r.from_city_id
JOIN city c2  ON c2.id = r.to_city_id ;

Le colonne city e route sono state unite utilizzando la colonna id di city e la colonna from_city_id di route. A questo punto, è possibile recuperare solo il nome della città di partenza. Per recuperare il nome della città di destinazione, è stato utilizzato un self-join sulla tabella city sulla tabella. Questa volta, abbiamo confrontato la colonna id della tabella alias con la colonna della tabella alias. city con la colonna to_city_id della tabella route della tabella.

Ecco il risultato:

È possibile utilizzare un self join SQL anche nelle sottoquery ricorsive che memorizzano grafici. Maggiori informazioni su questo argomento sono disponibili nel nostro nuovo corso, "Recursive Queries".

Utilizzo di SQL Self Join per trovare valori duplicati

Le self-join possono essere utilizzate anche per identificare i valori duplicati in una tabella. Presentiamo un esempio di tabella chiamata color:

Ogni record della tabella è diverso grazie alla colonna id, che deve essere sempre unica. Ma questo non impedisce a due righe di memorizzare lo stesso nome di colore. Vogliamo identificare questi casi e trovare gli ID dei nomi dei colori duplicati. Proviamo a farlo:

SELECT c1.id AS id1, c1.name  AS color1, c2.id AS id2, c2.name AS color2
FROM color c1
JOIN color c2 ON c1.name = c2.name AND c1.id < c2.id   ; 

Siamo riusciti a trovare nomi di colori duplicati perché abbiamo unito i record in base al nome del colore. La seconda condizione viene utilizzata per saltare i record identici di entrambe le tabelle e le stesse coppie di record in ordine inverso.

Osservate il risultato della query:

Ora è facile vedere che ci sono valori duplicati per il blu e il giallo.

Per saperne di più

Se volete saperne di più sulle JOIN rispetto a quanto descritto in questa guida illustrata sulle auto-giunzioni SQL, consultate il nostro corso SQL JOINs . Ricordate che il modo migliore per imparare SQL JOINs - o qualsiasi altra cosa, è imparare facendo!