Come unire due volte la stessa tabella

La JOIN è una delle istruzioni più comuni in SQL. Come forse sapete, viene utilizzata per unire e combinare i dati di due o più tabelle in un unico insieme di dati. In questo articolo parleremo di tipi speciali di join, in cui si unisce due volte la stessa tabella, compreso il join di una tabella a se stessa, noto anche come self join. Quando e perché è necessario farlo? Come si scrive in SQL? Scopriamolo.

Unioni: Un rapido ripasso

Probabilmente conoscete i join in SQL. Abbiamo due tabelle, A e B, e le uniamo utilizzando una colonna comune a entrambe. Ecco un esempio:

Abbiamo due tabelle: customer e citycon una colonna comune denominata city_id.

Ora, se si desidera unire le due tabelle per ottenere i nomi delle città dei clienti, è possibile farlo con un join di questo tipo:

select customer.customer_id,
     customer.firstname,
     customer.lastname,
     customer.birthdate,
     customer.spouse_id,
     customer.city_id,
     city.name as city_name
from customer 
join city 
on customer.city_id = city.city_id;

In questa istruzione JOIN, vengono abbinati i record di customer e city in base a una chiave (city_id). Si recuperano tutte e 6 le colonne della tabella customer e una colonna, name, dalla tabella city dalla tabella. Esistono diversi tipi di join in SQL; questo esempio fa un INNER JOIN.

Non intendo approfondire la sintassi di JOIN in questa sede. Per saperne di più, date un'occhiata al nostro corso interattivo sulle giunzioni SQL che potete trovare su LearnSQL.it.

Il risultato di questa unione sarà una tabella con tutti i 6 campi della tabella customer più un campo aggiuntivo dalla tabella city della tabella:

Unire più volte la stessa tabella

Ora che abbiamo fatto un rapido ripasso, esaminiamo le unioni più complesse.

A volte è necessario unire più volte la stessa tabella. In genere, si tratta di aggiungere una o più colonne a un set di risultati della stessa tabella, ma a record diversi o per colonne diverse. Esamineremo due scenari di questo tipo: l'unione di una tabella a se stessa e l'unione di tabelle con relazioni multiple.

Self Join: Unire una tabella a se stessa

La self join è un caso speciale di join. Invece di unire due tabelle diverse, si unisce una tabella a se stessa. Perché si vuole fare questo?

Nell'esempio precedente, volevamo aggiungere una colonna della tabella city il nome della città, alla tabella customer tabella. Quindi, abbiamo unito due tabelle diverse. Eseguire un self join significa, per esempio, unire la tabella customer a se stessa.

Ecco la customer come promemoria:

La colonna spouse_id contiene l'indirizzo customer_id del coniuge del cliente. Ad esempio, i clienti 1 e 2 (John e Mary) sono coniugi tra loro, i clienti 3 e 5 (Lisa e Tim) sono coniugi tra loro e così via. Possiamo aggiungere il nome e il cognome del coniuge a ogni record della tabella. customer tabella. Per fare questo, dobbiamo eseguire un self join, cioè unire la tabella customer a se stessa:

select cust.customer_id,
      cust.firstname,
      cust.lastname,
      cust.birthdate,
      cust.spouse_id,
      spouse.firstname as spouse_firstname,
      spouse.lastname as spouse_lastname
from customer cust 
join customer spouse
on cust.spouse_id = spouse.customer_id;

Quando si esegue questo codice, il risultato è il seguente:

Dopo aver visto un esempio di utilizzo delle self join, esaminiamo la sintassi SQL.

Sintassi del self join

La sintassi della self join è molto simile a quella di qualsiasi altro tipo di join. Ecco il codice del nostro esempio di self join:

select cust.customer_id,
      cust.firstname,
      cust.lastname,
      cust.birthdate,
      cust.spouse_id,
      spouse.firstname as spouse_firstname,
      spouse.lastname as spouse_lastname
from customer cust
join customer spouse
on cust.spouse_id = spouse.customer_id;

Si tratta di un'istruzione JOIN in cui la tabella customer viene utilizzata due volte.

Le informazioni sul coniuge, come il suo nome, sono memorizzate nella stessa tabella come un cliente separato con il proprio customer_id. Poiché spouse_id contiene il customer_id nome del coniuge, dobbiamo unire la tabella con se stessa per ottenere il nome del coniuge.

Si può pensare a un self join come a un join tra due copie della stessa tabella. Per ogni record con un valore non nullo in spouse_id, cerchiamo il valore di customer_id che corrisponde. Quando troviamo una corrispondenza, le colonne firstname e lastname vengono aggiunte alla tabella risultante.

Gli alias delle tabelle sono necessari in un self join. Il codice non funziona senza di essi, poiché non saprebbe a quale copia della tabella ci si riferisce. Qui, sto usando gli alias cust e spouse.

Subito prima della parola chiave FROM, scegliamo le colonne che vogliamo mantenere nella tabella risultante. Dobbiamo usare gli alias della tabella per recuperare le colonne (cust.firstname, cust.lastname, spouse.firstname, ecc.). Manteniamo cinque colonne dalla tabella customer e aggiungiamo dalla stessa tabella due colonne che contengono il nome del coniuge.

Si tratta di una join interna, ma si può usare qualsiasi tipo di join: LEFT JOIN, RIGHT JOIN, CROSS JOIN, ecc. Nel nostro esempio, abbiamo una join interna che restituisce solo i record corrispondenti; cioè, vengono restituiti solo i clienti con coniugi. Donna non ha un coniuge nella tabella, quindi Donna non è inclusa nell'insieme di dati risultante.

Se volete saperne di più sui join, vi consiglio il nostro articolo "Come imparare SQL JOINs"."Sulla nostra piattaforma è disponibile anche un corso interattivo di SQL joins. LearnSQL.it piattaforma.

Esempi di Self Join

Questo è solo un esempio. Quando si inizia a usare SQL quotidianamente, ci si imbatte spesso nella necessità di eseguire self join.

Un caso d'uso comune per la self join è quando esiste una gerarchia tra i record di una tabella. Questo tipo di struttura di dati si chiama struttura ad albero e spesso è necessario unire la tabella con se stessa in SQL. Ecco alcuni esempi.

Esempio 1: Gerarchia dei dipendenti

Ogni dipendente ha un manager, e un manager a sua volta ha il suo manager, tutti nella stessa tabella. Se si desidera aggiungere a ogni record le informazioni relative al manager, è necessario eseguire un self join. Questo esempio è illustrato nell'articolo "Guida illustrata alla Self Join di SQL", per cui si consiglia di dare un'occhiata a questo articolo per vedere come si presenta.

Esempio 2: Gerarchia dei dipartimenti

Ogni dipartimento all'interno di un'organizzazione ha un genitore: ad esempio, il dipartimento Data Science è sotto il dipartimento IT, il dipartimento IT è sotto il Business Support e il Business Support è sotto il consiglio di amministrazione.

Considerate la seguente tabella, departmentche ha questo aspetto:

Se si desidera aggiungere il nome del genitore a ciascun reparto, è necessario scrivere un self join:

select c.*,
   p.name as parent_name
from department c 
left join department p 
on c.parent_department_id=p.department_id;

All'interno di questo SELECT, uniamo la tabella department con se stessa per ottenere il nome del reparto genitore come campo aggiuntivo. Si noti che il record il cui department_id è 1 non ha un genitore (parent_department_id è NULL; non è popolato). Questo perché il consiglio di amministrazione si trova in cima alla struttura ad albero. Vogliamo visualizzare questo record nel risultato, quindi usiamo un LEFT JOIN e non un INNER JOIN.

Quando si esegue questo codice, la tabella risultante ha questo aspetto:

Si può facilmente vedere il genitore a cui appartiene ogni reparto: IT è sotto Amministrazione, Amministrazione è sotto Consiglio, ecc.

Esempio 3: Gerarchia delle categorie

Prendiamo ad esempio la pubblicità classificata: quelle popolari pagine web in cui si può affittare, comprare o vendere qualsiasi cosa, da immobili a prodotti e servizi vari. Per inserire un annuncio, si sceglie una categoria e una sottocategoria per l'annuncio. Ad esempio, se si vendono immobili, si può scegliere tra sottocategorie come casa, appartamento o terreno.

Abbiamo una tabella denominata category che contiene informazioni su queste categorie e sottocategorie e sulle loro relazioni. Le relazioni tra le categorie e le sottocategorie di questa tabella sono memorizzate in una struttura genitore-figlio come questa:

Tutte le categorie e le sottocategorie si trovano in questa tabella. Ora, se si vogliono aggiungere informazioni sul genitore a ogni record, è necessario eseguire un self join, ossia unire la tabella a se stessa:

select subcategory.*,
    main.category_name as parent_name
from category subcategory 
left join category main 
on subcategory.parent_category_id = main.category_id;

Ecco il risultato dell'esecuzione di questa istruzione SQL:

Relazioni multiple tra due tabelle

Oltre al self join, ci sono situazioni in cui è necessario unire la stessa tabella più di una volta. Una di queste è quando si hanno relazioni multiple tra due tabelle diverse. In questo caso, si unisce la stessa tabella due volte, ma di solito a qualche altra tabella e non necessariamente a se stessa.

Supponiamo che la tabella customer abbia due campi che contengono gli ID delle città. Questo è comune se si hanno due città diverse per ogni cliente, ad esempio la città di residenza (residence_city_id) e la città dell'indirizzo postale dove devono essere inviate le notifiche (notice_city_id):

Abbiamo anche city che contiene l'ID della città (city_id) e il nome della città (name), come visto in precedenza e mostrato di seguito come promemoria:

Ora, se si desidera visualizzare i nomi delle città, è necessario unire la tabella city due volte:

select cust.customer_id,
      cust.firstname,
      cust.lastname,
      cust.birthdate,
      cust.residence_city_id,
      cust.notice_city_id,
      residence_city.name as residence_city_name,
      notice_city.name as notice_city_name
from customer cust 
join city residence_city 
on cust.residence_city_id=residence_city.city_id
join city notice_city 
on cust.notice_city_id=notice_city.city_id;

Vediamo cosa succede in questo codice. Per prima cosa, uniamo customer e city con residence_city_id come chiave. Otteniamo residence_city_name abbinandolo a city_id nella tabella. city nella tabella. Si esegue una seconda unione tra customer e city per ottenere notice_city_name. La chiave utilizzata in questo caso è notice_city_id, che corrisponde anche a city_id nella tabella. city nella tabella.

Utilizziamo gli alias della tabella cust per customer, residence_city per la prima copia di city per ottenere il nome della città di residenza e notice_city per la seconda copia di city per ottenere il nome della città di avviso. Gli alias vengono utilizzati per definire le colonne della tabella risultante. Gli alias vengono utilizzati anche durante la join per definire le colonne chiave. Anche in questo caso, gli alias sono necessari per distinguere le due copie di city.

Quando si esegue questo codice, si ottiene il seguente risultato:

Ora abbiamo due colonne aggiuntive con i nomi delle città corrispondenti.

In questo caso utilizziamo join semplici (cioè interni), ma è possibile utilizzare qualsiasi tipo di join a seconda delle necessità. Se non conoscete le join di SQL e volete saperne di più sui diversi tipi, vi consiglio di leggere gli articoli "Come imparare le join" e "Come esercitarsi con le join" che trattano questi argomenti. Se preferite imparare guardando i video. Consiglio vivamente l'episodio che tratta le Join.

Unire due volte la stessa tabella

In questo articolo abbiamo discusso quando è necessario unire due volte la stessa tabella in SQL e abbiamo visto alcuni casi d'uso comuni. Abbiamo spiegato come farlo e come si presenta la sintassi SQL. Le auto-unioni con dati gerarchici e le relazioni multiple tra due tabelle sono solo due delle situazioni in cui è necessario unire due volte la stessa tabella. Ce ne sono altre; in genere, si tratta di aggiungere una o più colonne a un set di risultati provenienti dalla stessa tabella nella stessa colonna.

Se volete saperne di più sulle unioni, comprese le autounioni, vi consiglio il nostro corso interattivo sulle unioni SQL disponibile sulla nostra piattaforma. LearnSQL.it piattaforma. Quando si parla di SQL, è importante fare pratica; il nostro corso è pensato proprio per questo!