Come mantenere le righe non abbinate di due tabelle in una JOIN SQL

Volete unire due tabelle in SQL senza rimuovere le righe non abbinate? Volete conservare le righe non abbinate di una o di entrambe le tabelle? In questo articolo vi spiegherò come mantenere tutti i record desiderati utilizzando le JOIN esterne, come LEFT JOIN, RIGHT JOIN e FULL JOIN. Esempi inclusi!

Il JOIN SQL è un potente strumento che consente di combinare i dati provenienti da più tabelle del database. Si tratta di un'idea fondamentale alla base dei database relazionali: memorizzare i dati in diverse tabelle correlate e combinare i dati da queste tabelle quando necessario per l'analisi dei dati e la creazione di report. Se avete bisogno di un riassunto sull'unione delle tabelle in SQL, consultate questa guida per principianti e il nostro corso interattivo. SQL JOINs corso interattivo.

Purtroppo, quando si è alle prime armi con SQL, le query JOIN possono produrre risultati frustranti. Ad esempio, si possono ottenere duplicati, dati mancanti, valori NULL inattesi, ecc. In questo articolo, vorrei concentrarmi sul caso in cui si vogliano mantenere le righe non corrispondenti di due tabelle, ma una JOIN SQL le rimuove. Vedremo perché questo accade e come ottenere il risultato atteso.

Cominciamo con un esempio.

Supponiamo che stiate pianificando una campagna di marketing che prevede l'assegnazione di un bonus speciale a un gruppo di clienti. Ci sono diversi criteri per cui un cliente può beneficiare del bonus. Per questa particolare campagna, si cercano i clienti che hanno ordini con lo stato "Completato" nell'ultimo mese, ma che non hanno vinto nella campagna di marketing precedente.

Si desidera combinare le informazioni delle tre tabelle seguenti:

È possibile utilizzare la seguente query per unire queste tabelle e ottenere un elenco di clienti con le informazioni aggiuntive necessarie:

SELECT 
  c.id, 
  c.first_name, 
  c.last_name, 
  o.order_status, 
  lcp.winner
FROM customers c
JOIN orders o
ON c.id = o.customer_id
JOIN last_campaign_participants lcp
ON c.id = lcp.customer_id;

Se non siete sicuri di come funziona questa query, date un'occhiata al nostro corso interattivo con 93 sfide di codifica che coprono diversi tipi di JOIN.

Ecco l'output della query SQL di cui sopra:

Come si vede, l'output include solo due clienti su quattro. Questo perché solo questi due clienti hanno record corrispondenti in tutte e tre le tabelle e JOIN o INNER JOIN produce solo le righe corrispondenti. Con questo output, vediamo solo un cliente idoneo per la prossima campagna: Kate Wilson (ID 101). L'altro era il vincitore dell'ultima campagna e quindi non è idoneo per questa campagna.

Ma possiamo perdere qualcuno rimuovendo le righe non abbinate? In realtà sì. Possiamo trascurare i clienti che hanno completato gli ordini ma non hanno partecipato all'ultima campagna. Questi clienti non sono presenti in questa tabella. Vogliamo che tutti i clienti siano presenti nell'output della JOIN SQL, per poi filtrare i risultati secondo le necessità.

Fortunatamente, SQL dispone di JOIN esterne che consentono di mantenere le righe non corrispondenti di due tabelle.

JOIN interne vs. JOIN esterne

A differenza di INNER JOIN, o semplicemente JOIN, che restituisce solo le righe corrispondenti di due tabelle, le outer JOIN restituiscono anche le righe non corrispondenti in SQL. Esistono diversi tipi di outer JOIN:

• Una LEFT JOIN restituisce tutti i record della tabella di sinistra (prima) anche se non ci sono corrispondenze nella tabella di destra (seconda).
• Una RIGHT JOIN restituisce tutti i record della tabella di destra (seconda) anche se non ci sono corrispondenze nella tabella di sinistra (prima).
• Una FULL JOIN restituisce tutti i record di entrambe le tabelle, compresi quelli non corrispondenti di entrambe le tabelle.

Questo articolo spiega questi tipi di JOIN SQL in modo più dettagliato, con esempi e illustrazioni. Consultate anche questo ottimo SQL JOINs Cheat Sheet per conoscere tutte le sfumature della sintassi.

Per utilizzare una JOIN esterna, basta sostituire la parola chiave JOIN con le parole chiave LEFT JOIN, RIGHT JOIN, o FULL JOIN, a seconda del caso. Tuttavia, si noti che per LEFT JOIN e RIGHT JOIN, l'ordine delle tabelle nella query SQL è importante.

Ora modifichiamo la nostra prima query per mantenere le righe non abbinate e ottenere tutti i clienti in uscita. Una possibilità è quella di utilizzare LEFT JOIN, assicurandosi che la tabella customers sia elencata per prima nella query (cioè subito dopo la parola chiave FROM ):

SELECT 
  c.id, 
  c.first_name, 
  c.last_name, 
  o.order_status, 
  lcp.winner
FROM customers c
LEFT JOIN orders o
ON c.id = o.customer_id
LEFT JOIN last_campaign_participants lcp
ON c.id = lcp.customer_id;

L'output di questa query comprende un elenco di tutti i clienti attuali con le informazioni aggiuntive corrispondenti delle altre due tabelle. Si noti che se nessun record corrisponde al cliente della tabella di sinistra, questi record vengono comunque inclusi ma con i valori NULL nelle rispettive colonne:

Mantenendo le righe non abbinate con un LEFT JOIN, individuiamo un altro cliente idoneo alla campagna. In particolare, Maria White (ID 102) ha completato gli ordini e non ha partecipato all'ultima campagna, quindi ovviamente non ha vinto.

Per comprendere meglio le JOIN esterne, vediamo altri esempi.

Esempi di JOIN esterne

Immaginiamo di gestire una libreria. Vogliamo confrontare due gruppi di clienti: quelli che hanno acquistato Harry Potter e la pietra filosofale e quelli che hanno acquistato Harry Potter e la camera dei segreti. Sono le stesse persone? C'è qualcuno che ha comprato un libro ma non un altro? Potremmo consigliare loro l'altro libro.

Esempio con la CONGIUNZIONE SINISTRA

Per prima cosa, vogliamo vedere tutti coloro che hanno acquistato Harry Potter e la Pietra Filosofale da noi e verificare se hanno acquistato anche Harry Potter e la Camera dei Segreti.

Per ottenere questo risultato, utilizziamo LEFT JOIN con un elenco di coloro che hanno acquistato il primo libro incluso nella clausola FROM della query (tabella di sinistra).

SELECT 
  ps.product_id, 
  ps.first_name, 
  ps.last_name, 
  cs.product_id, 
  cs.first_name, 
  cs.last_name
FROM philosophers_stone ps
LEFT JOIN chamber_of_secrets cs
ON ps.customer_id = cs.customer_id;

Questo ci permette di mantenere tutti gli acquirenti di Harry Potter e la Pietra Filosofale anche se non ci sono record corrispondenti nella seconda tabella:

Ora vediamo che due clienti hanno acquistato il primo libro della serie ma non il secondo. Potremmo consigliare loro il secondo libro nella prossima campagna di marketing.

Esempio con FULL JOIN

Ma cosa succede se ci sono clienti che hanno acquistato solo il secondo libro ma non il primo? Anche questo sarebbe bello da sapere.

Invece di scrivere una query separata per individuare questo gruppo di clienti, è meglio usare FULL JOIN che ci fornisce l'intero quadro: chi ha acquistato solo il primo libro, chi ha acquistato solo il secondo libro e chi ha acquistato entrambi i libri:

SELECT 
  ps.product_id, 
  ps.first_name, 
  ps.last_name, 
  cs.product_id, 
  cs.first_name, 
  cs.last_name
FROM philosophers_stone ps
FULL JOIN chamber_of_secrets cs
ON ps.customer_id = cs.customer_id;

Sembra ottimo! Ora possiamo facilmente confrontare due gruppi di clienti e trovare opportunità di raccomandazione per i libri. Le JOIN esterne possono essere molto utili!

È ora di fare pratica con SQL JOINs!

L'unione di tabelle è una delle abilità fondamentali per un uso efficace di SQL nell'analisi dei dati e nella creazione di report. Non è difficile, ma è necessario fare molta pratica con SQL JOINs per evitare insidie come record mancanti e duplicati imprevisti.

Si consiglia di iniziare con il SQL JOINs interattivo. Copre tutti i principali tipi di JOIN, l'unione di una tabella con se stessa, l'unione di più tabelle in una query e l'unione di tabelle su colonne non chiave. Per maggiori dettagli su questo corso, consultate questo articolo riassuntivo.

Bonus. Ecco le 10 migliori domande di intervista su SQL JOIN con le relative risposte.

Grazie per aver letto e buon apprendimento!