Come si eliminano i duplicati in una JOIN SQL?

Avete duplicati indesiderati da una query SQL JOIN? In questo articolo, discuteremo i possibili motivi per cui si ottengono duplicati dopo l'unione di tabelle in SQL e mostreremo come correggere una query a seconda del motivo alla base dei duplicati.

Gli analisti di dati con poca esperienza in SQL JOINs spesso incontrano duplicati indesiderati nell'insieme dei risultati. Per i principianti è difficile identificare il motivo di questi duplicati nelle JOIN.

Il modo migliore per imparare SQL JOINs è la pratica. Raccomando il corso interattivo SQL JOINs interattivo. Contiene oltre 90 esercizi che consentono di esercitarsi sui diversi tipi di JOIN in SQL.

In questo articolo, verranno illustrati i problemi più comuni che portano alla formazione di duplicati nei risultati delle JOIN SQL. Mostrerò anche le possibili soluzioni a questi problemi comuni.

Iniziamo con una breve panoramica di SQL JOINs.

SQL JOINs Panoramica

JOIN è un costrutto SQL per richiedere informazioni da due o più tabelle all'interno della stessa query.

Per esempio, supponiamo di avere un elenco dei 100 migliori film del XX secolo e di volerlo suddividere in base ai film realizzati dai registi attualmente in vita. Nella tabella movies non si hanno informazioni dettagliate sui registi, ma solo i loro ID. Ma si dispone di una tabella directors con l'ID, il nome completo, l'anno di nascita e l'anno di morte (se applicabile) di ciascun regista.

Nella vostra query, potete unire due tabelle in base all'ID del regista per ottenere un elenco di film realizzati da registi attualmente in vita:

SELECT movies.title, directors.full_name
FROM movies
JOIN directors
ON movies.director_id = directors.id
WHERE directors.death_year IS NULL;

Come si può vedere, nelle clausole FROM e JOIN si specificano le tabelle che si desidera unire. Quindi, nella clausola ON, si specificano le colonne di ciascuna tabella da utilizzare per unire le tabelle. Se non conoscete SQL JOINs, consultate questa guida introduttiva. Ecco anche una scheda informativa sulle JOIN SQL con la sintassi e gli esempi di diverse JOIN.

La JOIN SQL è un ottimo strumento che offre una serie di opzioni oltre alla semplice unione di due tabelle. Se non conoscete i tipi di JOIN SQL, leggete questo articolo che li spiega con illustrazioni ed esempi. A seconda del caso d'uso, si possono scegliere INNER JOIN, LEFT JOIN, RIGHT JOIN e FULL JOIN. Si può anche avere la necessità di unire tabelle senza una colonna comune o di unire più di due tabelle.

Vediamo ora come queste diverse JOIN possono generare duplicati indesiderati.

Quali sono le ragioni dei duplicati in SQL JOINs?

Le ragioni per cui si ottengono duplicati nel risultato di una query SQL JOIN sono molteplici. Passerò in rassegna i 5 motivi principali; per ognuno di essi, mostrerò una query di esempio con il problema e una query corretta per ottenere un risultato senza duplicati.

Cominciamo a rivedere brevemente i dati da utilizzare per i nostri esempi. Immaginiamo di gestire un'agenzia immobiliare che vende case da qualche parte negli Stati Uniti. Abbiamo tabelle con agents, customers, e sales. Vedere di seguito i dati memorizzati in ciascuna tabella.

Senza ulteriori indugi, passiamo agli esempi.

1. Condizione ON mancante

I principianti che non hanno familiarità con SQL JOINs spesso si limitano a elencare le tabelle in FROM senza specificare affatto la condizione JOIN quando cercano di combinare le informazioni di due o più tabelle. Si tratta di una sintassi valida, quindi non viene visualizzato alcun messaggio di errore. Ma il risultato è un join incrociato con tutte le righe di una tabella combinate con tutte le righe di un'altra tabella.

Ad esempio, supponiamo di voler ottenere informazioni sul cliente che ha acquistato una determinata casa (ID #2134). Se utilizziamo la seguente query:

SELECT house_id, first_name, last_name, email
FROM sales, customers
WHERE house_id = 2134;

Questo è il risultato che otteniamo:

Invece di un record con il cliente desiderato, abbiamo tutti i clienti elencati nel set di risultati.

Per risolvere la query, è necessaria una sintassi esplicita JOIN. Le tabelle da combinare sono specificate in FROM e JOIN, mentre la condizione di unione è specificata nella clausola ON:

SELECT s.house_id, c.first_name, c.last_name, c.email
FROM sales s
JOIN customers c
ON s.customer_id = c.id
WHERE s.house_id = 2134;

Qui si specifica che l'ID del cliente della tabella sales in modo che corrisponda all'ID cliente della tabella customers tabella. In questo modo si ottiene il risultato desiderato:

Si potrebbe specificare la condizione di unione nella clausola WHERE per ottenere lo stesso risultato. Ma questo è contrario all'uso previsto della clausolaWHERE. Inoltre, ci sono ulteriori vantaggi nell'usare la sintassi JOIN piuttosto che elencare le tabelle in FROM. Consultate questo articolo per capire perché è preferibile la sintassi JOIN.

2. Utilizzo di una condizione ON incompleta

Le righe indesiderate nel set di risultati possono derivare da condizioni ON incomplete. In alcuni casi, è necessario unire tabelle per più colonne. In queste situazioni, se si utilizza una sola coppia di colonne, si ottengono righe duplicate.

Supponiamo di voler vedere il livello di esperienza dell'agente immobiliare per ogni casa venduta. Se iniziamo a unire le tabelle sales e agents in base al cognome dell'agente:

SELECT s.house_id, a.first_name, a.last_name, a.experience_years
FROM sales s
JOIN agents a
ON s.agent_last_name = a.last_name
ORDER BY s.house_id;

Questo è ciò che si ottiene:

Non ha funzionato bene. Abbiamo due agenti diversi con il cognome Scott: Maria e Sophia. Di conseguenza, le case #1015 e #2134 sono incluse due volte con agenti diversi.

Per risolvere questa query, dobbiamo unire le tabelle sales e agents utilizzando due coppie di colonne, corrispondenti al cognome e al nome dell'agente:

SELECT s.house_id, a.first_name, a.last_name, a.experience_years
FROM sales s
JOIN agents a
ON s.agent_last_name = a.last_name
   AND s.agent_first_name = a.first_name
ORDER BY s.house_id;

Ed ecco il risultato che stavamo cercando.

Sebbene il JOIN sia uno degli strumenti di base di SQL, è necessario conoscere le diverse sfumature per unire le tabelle in modo efficace. Consiglio di esercitarsi su SQL JOINs con questo corso interattivo che copre una serie di scenari di unione con 93 sfide di codifica.

3. Selezione di un sottoinsieme di colonne

In alcuni casi, i record nel set di risultati non sono duplicati, ma sembrano esserlo perché il sottoinsieme di colonne selezionato non mostra tutte le differenze tra i record.

Ad esempio, immaginiamo di voler vedere le date in cui ogni agente immobiliare ha venduto una casa. Se utilizziamo la seguente query:

SELECT a.first_name, a.last_name, s.date
FROM agents a
JOIN sales s
ON s.agent_last_name = a.last_name
   AND s.agent_first_name = a.first_name;

produce il seguente risultato:

Il set di risultati include due record con Alexandr McGregor che sembrano identici. Tuttavia, se si aggiunge l'ID della casa all'istruzione SELECT, si vede che questi due record corrispondono alla vendita di due case diverse nello stesso giorno.

Se non si è interessati a queste informazioni aggiuntive e si desidera visualizzare una sola riga, utilizzare DISTINCT:

SELECT DISTINCT a.first_name, a.last_name, s.date
FROM agents a
JOIN sales s
ON s.agent_last_name = a.last_name
   AND s.agent_first_name = a.first_name;

Ora il risultato è:

4. Elencare solo le righe corrispondenti

Un problema simile può verificarsi se si vogliono elencare solo le righe di una tabella, ma ci sono diversi record corrispondenti nell'altra tabella. Si finisce per avere duplicati indesiderati nell'insieme dei risultati.

Per esempio, supponiamo di voler elencare tutti i clienti che hanno acquistato case tramite la nostra agenzia. Se utilizziamo la seguente query:

SELECT c.first_name, c.last_name, c.email
FROM customers c
JOIN sales s
ON c.id = s.customer_id;

ecco il risultato:

Come si vede, la tabella risultante include Ivan Lee due volte. Questo perché ha acquistato due case e ci sono due record corrispondenti nella tabella. sales tabella. Una possibile soluzione è utilizzare DISTINCT come nell'esempio precedente. Una soluzione ancora migliore è quella di evitare del tutto l'uso di SQL JOIN, filtrando l'insieme dei risultati con la parola chiave EXISTS:

SELECT c.first_name, c.last_name, c.email
FROM customers c
WHERE EXISTS (SELECT customer_id FROM sales);

Ora, il risultato è:

In questo modo si ottiene l'output desiderato e si chiarisce l'intento della query.

5. Uso delle auto-unioni

Infine, i duplicati indesiderati nelle JOIN spesso derivano da un'errata specificazione delle condizioni di unione nelle self join, cioè quando una tabella viene unita a se stessa.

Supponiamo di volere che i nostri agenti formino delle coppie per il prossimo allenamento. Ovviamente, non vogliamo che nessun agente sia accoppiato con se stesso. Quindi, potremmo specificare la condizione ON a1.id <> a2.id :

SELECT
    a1.first_name as agent1_first_name,
    a1.last_name as agent1_last_name,
    a1.experience_years as agent1_experience,
    a2.first_name as agent2_first_name,
    a2.last_name as agent2_last_name,
    a2.experience_years as agent2_experience
FROM agents a1
JOIN agents a2
ON a1.id <> a2.id
ORDER BY a1.id;

Tuttavia, questa query produce ogni coppia due volte. Ad esempio, nella prima riga della tabella sottostante, Kate White è considerata l'Agente 1 e Maria Scott è considerata l'Agente 2. Ma più avanti, alla fine della tabella, si può vedere l'Agente 1 e l'Agente 2. Ma più vicino alla fine della tabella, si ottiene la stessa coppia di agenti, ma con Maria Scott come Agente 1 e Kate White come Agente 2.

Per risolvere questo problema, è necessario aggiungere una condizione esplicita per includere ogni coppia solo una volta. Una soluzione comune è quella di specificare la condizione di unione a1.id < a2.id. In questo modo si ottiene la coppia Kate White e Maria Scott, ma non viceversa. Questo perché l'ID di Kate (1) è un numero inferiore a quello di Maria (6).

In pratica, si possono avere altre condizioni per accoppiare gli agenti. Ad esempio, si potrebbe voler accoppiare agenti più esperti (3+ anni) con altri meno esperti (< 3 anni). La corrispondente condizione di filtraggio in WHERE risolve il problema:

SELECT
    a1.first_name as agent1_first_name,
    a1.last_name as agent1_last_name,
    a1.experience_years as agent1_experience,
    a2.first_name as agent2_first_name,
    a2.last_name as agent2_last_name,
    a2.experience_years as agent2_experience
FROM agents a1
JOIN agents a2
ON a1.id <> a2.id
WHERE a1.experience_years>=3 AND a2.experience_years < 3
ORDER BY a1.id;

Ecco il risultato:

Questo insieme di risultati ha un aspetto migliore e rende più facile la selezione di tre coppie, ciascuna composta da un agente con più esperienza e un altro con meno esperienza.

Esercitiamoci su SQL JOINs!

Unire tabelle in SQL non è poi così difficile. Ma richiede molta pratica. Se volete evitare insidie come duplicati indesiderati nelle JOIN e record mancanti, seguite questa guida per esercitarvi su SQL JOINs.

Se avete solo un'esperienza di base con SQL e volete combinare i dati da più tabelle in modo più sicuro, vi consiglio questo corso interattivo. SQL JOINs corso interattivo. Copre tutti i principali tipi di JOIN, nonché l'unione di una tabella con se stessa, l'unione di più tabelle in un'unica 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!