Imparare le JOIN con esempi di SQL del mondo reale

L'istruzione JOIN consente di lavorare con dati memorizzati in più tabelle. In questo articolo vi illustrerò l'argomento delle clausole JOIN utilizzando esempi SQL reali.

Immaginate di poter lavorare con una sola tabella del database alla volta. Fortunatamente, questo non è un problema di cui preoccuparsi. Una volta imparata l'istruzione JOIN, si può iniziare a collegare i dati tra loro. In questo articolo, utilizzerò esempi di SQL del mondo reale che illustrano l'uso di JOINs, il funzionamento di ciascun tipo di JOIN e quando utilizzarlo. Inoltre, condividerò alcuni suggerimenti che vi aiuteranno a evitare gli errori più comuni nell'uso di JOINs.

Che cos'è la clausola SQL JOIN?

La clausola JOIN consente di combinare le colonne di due o più tabelle in base a valori condivisi. La spiegherò utilizzando esempi SQL in tempo reale. Ecco il primo: supponiamo di avere una tabella students che contiene i nomi degli studenti, i loro rispettivi nomi utente e un numero ID. Abbiamo anche una tabella "commenti" che contiene tutti i messaggi comments che gli studenti hanno inserito in un forum. Ecco le due tabelle.

Aggiungiamo alcuni dati di prova:

INSERT INTO `students` VALUES
(1,'Jhon','jj2005'),(2,'Albert','salbert'),(3,'Mathew','powermath'),
(4,'Lisa','lisabbc'),(5,'Sandy','imsandyw'),(6,'Tamara','tamy21');

INSERT INTO 'comments' VALUES 
(1,'jj2005','Awesome!'),(2,'jj2005','This is great :)'),
(3,'powermath','Hmmm...'),(4,'imsandyw','Let\'s wait a moment'),
(5,'lisabbc','Sure thing'),(6,'lisabbc','wow!'),
(7,'lisabbc','lol :) :) :)');

Come si può notare, entrambe le tabelle hanno in comune la colonna forum_username. Pertanto, questa colonna può essere utilizzata in un'istruzione JOIN per mettere in relazione le due tabelle.

Ad esempio, se si volesse conoscere il nome effettivo dello studente per ogni commento del forum, si potrebbe scrivere questa query JOIN:

SELECT students.name, comments.forum_username, comments.comment
FROM students 
INNER JOIN comments
  ON students.forum_username = comments.forum_username
ORDER BY students.name ASC;

I risultati sarebbero questi:

Nota: ho usato di proposito forum_username per illustrare il concetto di JOIN, ma in pratica si usa la chiave primaria (in questo caso, la colonna id della tabella students ) per mettere in relazione le tabelle.

Conoscere i tipi di JOIN

Esistono diversi tipi di JOIN. Rivediamoli rapidamente:

INNER JOIN: Questo JOIN restituisce i record che hanno una corrispondenza in entrambe le tabelle in base al predicato di join (che viene dopo la parola chiave ON ). Si tratta dello stesso JOIN utilizzato nell'esempio precedente. La parola chiave INNER è opzionale.

LEFT [OUTER] JOIN: restituisce tutti i record della tabella di sinistra (cioè la tabella elencata per prima in JOIN) e solo i record corrispondenti della tabella di destra (cioè la seconda). La parola chiave OUTER è facoltativa.

Tornando agli esempi di SQL del mondo reale, nel caso del forum degli studenti, questo sarebbe:

SELECT students.name, comments.forum_username, comments.comment
FROM students 
LEFT JOIN comments
  ON students.forum_username = comments.forum_username
ORDER BY students.name ASC;

Questo LEFT JOIN restituirebbe un record per tutti gli studenti, compresi Tamara e Albert, che non hanno commenti e non sarebbero elencati nei risultati di un INNER JOIN. Si noti anche che in questo esempio LEFT JOIN i risultati delle colonne comment di Tamara e Albert saranno NULL.

RIGHT [OUTER] JOIN: è l'inverso di LEFT JOIN; restituisce tutti i record della tabella di destra (seconda) e solo quelli che hanno una corrispondenza nella tabella di sinistra (prima).

Una query simile a quella dell'esempio precedente avrebbe il seguente aspetto:

SELECT 
  students.name,
  comments.forum_username,
  comments.comment
FROM students 
RIGHT JOIN comments
  ON students.forum_username = comments.forum_username
ORDER BY students.name ASC;

FULL [OUTER] JOIN: si tratta essenzialmente della combinazione di una LEFT JOIN e di una RIGHT JOIN. L'insieme dei risultati includerà tutte le righe di entrambe le tabelle, popolando le colonne con i valori della tabella, quando possibile, o con NULL quando non c'è corrispondenza nella tabella di controparte. Questo non è un JOIN che si usa molto spesso nella vita reale. Nota: MySQL non dispone di questa istruzione, ma un risultato simile può essere ottenuto utilizzando UNION di LEFT JOIN e RIGHT JOIN.

Nel caso dei nostri esempi SQL del mondo reale, vale la pena notare che nella clausola FULL JOIN seguente forniamo la parola chiave ON come in LEFT o RIGHT JOINs :

SELECT *
FROM students 
FULL OUTER JOIN comments
  ON students.forum_username = comments.forum_username
ORDER BY students.name ASC;

CROSS JOIN: Questo è un altro tipo di join che non si usa spesso, in questo caso perché recupera il prodotto cartesiano di entrambe le tabelle. In pratica, si ottiene la combinazione di tutti i record di entrambe le tabelle. CROSS JOIN non applica un predicato (non c'è la parola chiave ON ), ma è comunque possibile filtrare le righe usando WHERE. In questo modo, l'insieme dei risultati potrebbe essere equivalente a un INNER JOIN. In MySQL, JOIN, CROSS JOIN e INNER JOIN sono equivalenti sintattici, cioè possono sostituirsi l'un l'altro.

Di seguito, non esiste una clausola ON per filtrare i risultati. Tutte le possibili combinazioni di entrambe le tabelle saranno mostrate nel set di risultati:

SELECT *
FROM students 
CROSS JOIN comments 
ORDER BY students.name ASC;

Potete saperne di più sui tipi di SQL JOINs sul nostro canale YouTube - Impariamo l'SQL. Ricordatevi di fare clic su Iscriviti.

Esempi di JOIN in SQL in tempo reale

Il numero di scenari che richiedono una JOIN è infinito, ma alcuni scenari si presentano più spesso. Invece di passare al tipico esempio di tabella1/tabella2, preferisco presentarvi esempi di SQL del mondo reale. Possiamo usarli per ottenere alcuni suggerimenti pratici.

La relazione tra nonno, padre e figlio

Uno scenario comune in tempo reale ha a che fare con dati che seguono questo tipo di relazione. Per esempio, una user si trova in una city che appartiene a una state. Le tabelle (con più o meno colonne) hanno un aspetto simile a questo:

Aggiungiamo alcuni dati:

INSERT INTO `states` VALUES (3,'California'),(2,'Florida'),(1,'Nevada');

INSERT INTO `cities` VALUES (1,1,'Miami'),(2,1,'Orlando'),
(3,2,'Las Vegas'),(4,2,'Coyote Springs');

INSERT INTO `users` VALUES (1,1,'Jhon','Doe'),(2,1,'Albert','Thomson'),
(3,2,'Robert','Ford'),(4,3,'Samantha','Simpson');

Per ottenere l'elenco completo degli utenti di una data città e di un dato stato, dovremo unire la tabella dei figli (User) con il padre (City) e il nonno (State).

SELECT 
  User.first_name,
  user.last_name,
  City.cityname,
  State.statename
FROM users User
INNER JOIN cities City
  ON User.city_id = City.id
INNER JOIN states State
  ON City.state_id = State.id;

Abbiamo già un paio di suggerimenti utili:

• Le colonne utilizzate per collegare le tabelle dovrebbero essere indicizzate per ottenere maggiori prestazioni.
• Quando le colonne che collegano le tabelle (come nell'esempio precedente) puntano alla chiave primaria della tabella correlata, si parla di chiavi esterne. In questo caso, è meglio includere questa relazione come parte della definizione della tabella, per aumentare le prestazioni. In MySQL, è possibile creare una chiave esterna utenti/città in questo modo:

  ALTER TABLE `users` 
  ADD INDEX `fk_city_idx` (`city_id` ASC);
  ALTER TABLE `users` 
  ADD CONSTRAINT `fk_city`
    FOREIGN KEY (`city_id`)
    REFERENCES `cities` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE;
  

  In questo modo si ottiene l'ulteriore vantaggio del controllo di integrità che verrà eseguito dal motore quando i dati di queste tabelle vengono aggiornati o cancellati.

Supponiamo di voler trovare tutti gli utenti di uno stato. Si aggiunge una condizione di filtraggio alla query, come mostrato di seguito:

SELECT 
  User.first_name,
  user.last_name,
  City.cityname,
  State.statename
FROM users User
INNER JOIN cities City
  ON User.city_id = City.id
INNER JOIN states State
  ON City.state_id = State.id
  AND State.statename = 'Nevada';

Oppure si può utilizzare un join implicito (indicato in grassetto), come questo:

SELECT 
  User.first_name,
  user.last_name,
  City.cityname,
  State.statename
FROM
   users User,
   cities City,
   states State
WHERE User.city_id = City.id
  AND City.state_id = State.id
  AND State.statename = 'Nevada';

Ma vi suggerisco di scrivere esplicitamente JOIN e di tenere separati i criteri di unione e le condizioni di filtraggio:

SELECT 
  User.first_name,
  user.last_name,
  City.cityname,
  State.statename
FROM users User
INNER JOIN cities City
  ON User.city_id = City.id
INNER JOIN states State
  ON City.state_id = State.id
WHERE State.statename = 'Nevada';

Un paio di concetti importanti:

• Possiamo vedere come la tabella figlio (user) viene filtrata in base alle condizioni utilizzate per la tabella del nonno (state). In altre parole, gli utenti erano basati su un determinato stato. Allo stesso modo, avremmo potuto filtrare i risultati in base alla tabella padre (city) e ottenere un elenco di utenti basato su una determinata città.
• Una regola generale è che i predicati di JOIN (le condizioni dopo la parola chiave ON ) devono essere usati solo per la relazione di unione. Lasciare il resto delle condizioni di filtraggio all'interno della sezione WHERE. Questo semplificherà la leggibilità delle query e la futura manutenzione del codice.

Al contrario, potremmo restituire gli stati in base a determinati criteri. Ad esempio, si potrebbero recuperare gli stati che hanno almeno un utente appartenente a quello stato:

SELECT 
  DISTINCT State.statename
FROM states State
INNER JOIN cities City
  ON City.state_id = State.id
INNER JOIN users User
  ON User.city_id = City.id

Che cosa impariamo qui?

• INNER JOIN rimuove tutti i record "inutilizzati" o non abbinati (record senza corrispondenza su entrambi i lati di JOIN).
• DISTINCT filtra i record duplicati. Poiché possono esserci più utenti per uno stato, se non si usasse DISTINCT si otterrebbero tanti stati ripetuti quanti sono gli utenti che vi appartengono. (Lo stesso effetto di filtraggio si potrebbe ottenere anche usando GROUP BY).
• Un ulteriore filtraggio può essere realizzato aggiungendo le condizioni di WHERE.

La relazione molti-a-molti

Volete altri esempi SQL in tempo reale? Un altro scenario tipico di JOINè quello in cui i record si relazionano tra loro in modo "molti-a-molti" o N-a-N. Supponiamo di avere un sistema in cui si creano dei badge che vengono assegnati agli utenti. In questo caso, un utente ha dei badge e allo stesso tempo un badge ha degli utenti. Queste relazioni necessitano di una terza tabella che colleghi le chiavi primarie di users e badges. L'aspetto sarebbe simile a questo:

Aggiungiamo alcuni dati di esempio:

INSERT INTO `badges` VALUES 
(1,'gold heart',100),(2,'silver heart',50),(3,'metal heart',10),(4,'star',5),(5,'wood',2),(6,'dust',1);
INSERT INTO `users` VALUES (1,'Robert','Williams'),(2,'Anthony','McPeters'),(3,'Tania','Krugger'),(4,'JJ','Richards'),(5,'Katy','Thomas');
INSERT INTO `badges_users` VALUES
(1,1,1),(2,4,1),(3,4,2),(4,4,2),(5,4,2),(6,3,3),(7,3,3),(8,2,4);

Come può JOIN recuperare tutti gli utenti con i rispettivi badge?

SELECT 
  User.first_name,
  User.last_name,
  BU.user_id,
  BU.badge_id,
  Badge.badge_name,
  Badge.badge_points
FROM users User
LEFT JOIN badges_users B
  ON User.id = BU.user_id
LEFT JOIN badges Badge
  ON BU.badge_id = Badge.id
ORDER BY Badge.badge_points DESC

Ecco alcune cose da ricordare su questo tipo di query:

• Abbiamo usato LEFT JOIN di proposito perché mostrerà gli utenti che non hanno alcun badge. Se avessimo usato una INNER JOIN o una inner join implicita (impostando le uguaglianze degli ID in una WHERE), gli utenti che non hanno badge non sarebbero stati inclusi nei risultati. Se si vuole escludere questi utenti, si deve usare una INNER JOIN.
• Inoltre, l'uso di un LEFT JOIN significa che i badge non utilizzati non saranno elencati; ci stiamo concentrando sugli utenti e non sui badge.
• Infine, ricordarsi di indicizzare correttamente la tabella intermedia (badges_users). Tutte le sue chiavi esterne devono essere definite.

Ora prendiamo tutti i badge che hanno almeno un utente. In altre parole, si tratta di badge che sono stati utilizzati almeno una volta. La query sarebbe:

SELECT DISTINCT Badge.badge_name
FROM badges Badge
LEFT JOIN badges_users BadgeUser
  ON Badge.id = BadgeUser.badge_id

E se volessimo ottenere tutti i badge non utilizzati, la query diventerebbe:

SELECT Badge.badge_name, Badge.badge_points
FROM badges Badge
LEFT JOIN badges_users BadgeUser
  ON Badge.id = BadgeUser.badge_id
WHERE BadgeUser.badge_id IS NULL

Si noti che:

• Non è sempre necessario unire tutte e tre le tabelle. Unendo solo la tabella intermedia (badges_users), che contiene i riferimenti alle tabelle user e badge è possibile eseguire le query con successo.
• La tabella intermedia può essere utilizzata anche per salvare informazioni aggiuntive. Ad esempio, potrebbe memorizzare il valore del timestamp quando un utente ha ricevuto un determinato badge.

Provate voi stessi

È probabile che vi troviate quotidianamente di fronte a questo tipo di situazioni quando avete a che fare con tabelle che contengono dati correlati. Vi consiglio vivamente di esaminare gli esempi SQL del mondo reale riportati sopra e di testarli con i dati reali. In questo modo avrete una comprensione più chiara dei concetti coinvolti.

Se avete commenti sui nostri esempi di SQL reali o idee su altre clausole JOIN reali, non esitate a condividerle per continuare a imparare!