Comment se débarrasser des doublons dans une JOIN SQL ?

Avez-vous des doublons indésirables dans votre requête SQL JOIN ? Dans cet article, j'aborderai les raisons possibles de l'apparition de doublons après la jonction de tables en SQL et je montrerai comment corriger une requête en fonction de la raison de ces doublons.

Les analystes de données ayant peu d'expérience de Les jointures en SQL rencontrent souvent des doublons indésirables dans l'ensemble des résultats. Il est difficile pour les débutants d'identifier la raison de ces doublons dans les JOINs.

La meilleure façon d'apprendre Les jointures en SQL est de s'exercer. Je recommande le Les jointures en SQL interactif. Il contient plus de 90 exercices qui vous font pratiquer les différents types de JOIN en SQL.

Dans cet article, j'aborderai les problèmes les plus courants conduisant à des doublons dans les résultats des JOIN SQL. Je présenterai également les solutions possibles à ces problèmes courants.

Commençons par un bref aperçu de Les jointures en SQL.

Les jointures en SQL Aperçu

JOIN La jointure est une construction SQL permettant de demander des informations à partir de deux ou plusieurs tables dans la même requête.

Par exemple, supposons que vous ayez une liste des 100 meilleurs films du XXe siècle et que vous souhaitiez la réduire aux films réalisés par des réalisateurs encore en vie. Dans votre table movies vous n'avez pas d'informations détaillées sur les réalisateurs, seulement leurs identifiants. Mais vous avez une table séparée directors séparée, avec l'ID, le nom complet, l'année de naissance et l'année de décès (le cas échéant) de chaque réalisateur.

Dans votre requête, vous pouvez joindre deux tables par l'ID du réalisateur pour obtenir une liste des films réalisés par des réalisateurs actuellement en vie :

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

Comme vous pouvez le voir, nous spécifions les tables que nous voulons joindre dans les clauses FROM et JOIN. Ensuite, dans la clause ON, nous spécifions les colonnes de chaque table à utiliser pour joindre ces tables. Si vous ne connaissez pas encore Les jointures en SQL, consultez ce guide d'introduction. Voici également un aide-mémoire sur les JOIN SQL avec la syntaxe et des exemples de différents JOIN.

Le SQL JOIN est un outil formidable qui offre une variété d'options au-delà de la simple jointure de deux tables. Si vous n'êtes pas familier avec les types de SQL JOIN, lisez cet article qui les explique avec des illustrations et des exemples. Selon votre cas d'utilisation, vous pouvez choisir INNER JOIN, LEFT JOIN, RIGHT JOIN et FULL JOIN. Vous pouvez même avoir besoin de joindre des tables sans colonne commune ou de joindre plus de deux tables.

Voyons maintenant comment ces différentes jointures peuvent donner lieu à des doublons indésirables.

Quelles sont les raisons des doublons dans Les jointures en SQL?

Il y a de nombreuses raisons possibles pour obtenir des doublons dans le résultat de votre requête SQL JOIN. Je vais passer en revue les 5 principales raisons ; pour chacune d'entre elles, je montrerai un exemple de requête présentant le problème et une requête corrigée pour obtenir un résultat sans doublons.

Commençons par examiner brièvement les données qui seront utilisées pour nos exemples. Imaginons que nous gérions une agence immobilière qui vend des maisons quelque part aux États-Unis. Nous disposons de tables avec agents, customers, et sales. Voir ci-dessous pour savoir quelles données sont stockées dans chaque table.

Sans plus attendre, passons à nos exemples.

1. Condition ON manquante

Les débutants qui ne connaissent pas Les jointures en SQL se contentent souvent d'énumérer les tables de FROM sans spécifier la condition JOIN lorsqu'ils essaient de combiner des informations provenant de deux tables ou plus. Il s'agit d'une syntaxe valide, et vous n'obtenez donc aucun message d'erreur. Mais le résultat est une jointure croisée avec toutes les lignes d'une table combinées avec toutes les lignes d'une autre table.

Par exemple, supposons que nous voulions obtenir des informations sur le client qui a acheté une maison particulière (ID #2134). Si nous utilisons la requête suivante :

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

Voici le résultat que nous obtenons :

Au lieu d'un enregistrement avec le client que nous voulons, nous avons tous nos clients listés dans le jeu de résultats.

Pour corriger la requête, vous avez besoin d'une syntaxe explicite JOIN. Les tables à combiner sont spécifiées dans FROM et JOIN, et la condition de jointure est spécifiée dans la clause 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;

Ici, nous spécifions l'ID client de la table sales pour qu'il corresponde à l'ID du client de la table customers table. Cela nous donne le résultat souhaité :

Vous pourriez spécifier la condition de jointure dans la clause WHERE pour obtenir le même résultat. Mais cela va à l'encontre de l'utilisation prévue de la clauseWHERE. En outre, il y a d'autres avantages à utiliser la syntaxe JOIN plutôt que de lister les tables dans FROM. Consultez cet article pour comprendre pourquoi la syntaxe JOIN est préférable.

2. Utilisation d'une condition ON incomplète

Les lignes non désirées dans le jeu de résultats peuvent provenir de conditions ON incomplètes. Dans certains cas, vous devez joindre des tables par plusieurs colonnes. Dans ces situations, si vous n'utilisez qu'une seule paire de colonnes, cela donne des lignes en double.

Disons que nous voulons voir le niveau d'expérience de l'agent immobilier pour chaque maison vendue. Si nous commençons par joindre les tables sales et agents par le nom de famille de l'agent :

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;

Voici ce que vous obtenez :

Ça n'a pas bien marché. Nous avons deux agents différents avec le nom de famille Scott : Maria et Sophia. Par conséquent, les maisons #1015 et #2134 sont chacune incluses deux fois avec des agents différents.

Pour résoudre cette requête, nous devons joindre les tables sales et agents en utilisant deux paires de colonnes, correspondant au nom de famille et au prénom de l'agent :

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;

Et voici le résultat que nous recherchions.

Bien que JOIN soit l'un des outils de base de SQL, vous devez être conscient des nombreuses nuances pour joindre des tables efficacement. Je vous recommande de vous entraîner sur Les jointures en SQL avec ce cours interactif qui couvre une variété de scénarios de jointure avec 93 défis de codage.

3. Sélection d'un sous-ensemble de colonnes

Dans certains cas, les enregistrements du jeu de résultats ne sont pas des doublons mais semblent l'être parce que le sous-ensemble de colonnes sélectionné ne montre pas toutes les différences entre les enregistrements.

Par exemple, imaginons que nous voulions voir les dates auxquelles chaque agent immobilier a vendu une maison. Si nous utilisons la requête suivante :

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;

elle produit le résultat suivant :

L'ensemble de résultats comprend deux enregistrements avec Alexandr McGregor qui semblent identiques. Cependant, si vous ajoutez l'ID de la maison à la déclaration SELECT, vous voyez que ces deux enregistrements correspondent à la vente de deux maisons différentes le même jour.

Si vous n'êtes pas intéressé par ces informations supplémentaires et que vous souhaitez qu'une seule ligne soit affichée ici, utilisez 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;

Maintenant, le résultat est le suivant :

4. Affichage des lignes correspondantes uniquement

Un problème similaire peut survenir si vous souhaitez ne répertorier que les lignes d'une table, mais qu'il existe plusieurs enregistrements correspondants dans l'autre table. Vous vous retrouvez alors avec des doublons indésirables dans votre ensemble de résultats.

Par exemple, disons que nous voulons lister tous les clients qui ont acheté des maisons par l'intermédiaire de notre agence. Si nous utilisons la requête suivante :

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

voici le résultat :

Comme vous le voyez, le tableau résultant inclut Ivan Lee deux fois. Cela est dû au fait qu'il a acheté deux maisons et qu'il y a deux enregistrements correspondants dans la table sales dans la table. Une solution possible est d'utiliser DISTINCT comme dans l'exemple précédent. Une solution encore meilleure est d'éviter d'utiliser SQL JOIN en filtrant le jeu de résultats à l'aide du mot-clé EXISTS:

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

Maintenant, le résultat est :

Cela vous donne le résultat souhaité et rend également l'intention de votre requête plus claire.

5. Utilisation des jointures automatiques

Enfin, les doublons indésirables dans les jointures résultent souvent d'une spécification incorrecte des conditions de jointure dans les jointures automatiques, c'est-à-dire lorsqu'une table est jointe à elle-même.

Disons que nous voulons que nos agents forment des paires pour notre prochaine formation. Évidemment, nous ne voulons pas qu'un agent soit jumelé avec lui-même. Nous pourrions donc spécifier la condition 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;

Cependant, cette requête produit chaque paire deux fois. Par exemple, dans la première ligne du tableau ci-dessous, Kate White est considérée comme l'agent 1, et Maria Scott comme l'agent 2. Mais plus près de la fin du tableau, vous obtenez la même paire d'agents mais avec Maria Scott comme agent 1 et Kate White comme agent 2.

Pour résoudre ce problème, vous devez ajouter une condition explicite pour inclure chaque paire une seule fois. Une solution courante consiste à spécifier la condition de jonction a1.id < a2.id. Avec cette condition, vous obtenez la paire Kate White et Maria Scott, mais pas l'inverse. En effet, l'identifiant de Kate (1) est un nombre inférieur à celui de Maria (6).

Dans la pratique, vous pouvez avoir d'autres conditions pour apparier les agents. Par exemple, vous pouvez vouloir jumeler des agents plus expérimentés (3+ ans) avec des agents moins expérimentés (< 3 ans). La condition de filtrage correspondante dans WHERE résout le problème :

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;

Voici le résultat :

Cet ensemble de résultats semble bien meilleur et permet de sélectionner plus facilement trois paires, chacune composée d'un agent plus expérimenté et d'un autre moins expérimenté.

Pratiquons Les jointures en SQL!

Joindre des tables en SQL n'est pas si difficile. Mais cela demande beaucoup de pratique. Si vous voulez éviter les pièges tels que les doublons indésirables dans les JOIN et les enregistrements manquants, suivez ce guide sur la pratique de Les jointures en SQL.

Si vous n'avez qu'une expérience de base de SQL et que vous souhaitez combiner des données provenant de plusieurs tables avec plus d'assurance, je vous recommande ce Les jointures en SQL cours interactif. Il couvre tous les principaux types de jointures, ainsi que la jointure d'une table avec elle-même, la jointure de plusieurs tables dans une requête et la jointure de tables sur des colonnes non clés. Vous trouverez plus de détails sur ce cours dans cet article de présentation.

Bonus. Voici les 10 meilleures questions d'entretien SQL JOIN avec les réponses.

Merci de votre lecture et bon apprentissage !