Leçon 12.5 · Temps de lecture : ~7 min

Les doublons apparaissent dans presque toutes les bases réelles : un import lancé deux fois, un formulaire soumis deux fois, une contrainte d'unicité jamais ajoutée. « Trouver les doublons » et « supprimer les doublons en gardant une ligne » sont des exercices classiques d'entretien SQL. Dans cette leçon, vous trouverez les valeurs en double avec GROUP BY ... HAVING, listerez chaque copie avec une fonction de fenêtrage, supprimerez les copies en trop en toute sécurité dans MySQL et empêcherez de nouveaux doublons avec une contrainte.

Trouver et supprimer les doublons en SQL

Décidez d'abord ce qu'est un doublon. Les lignes sont rarement identiques dans toutes les colonnes : elles ont généralement des id et des dates de création différents. Un doublon, c'est la même clé métier : le même e-mail, le même nom et la même date de naissance, le même numéro de commande. Toutes les requêtes ci-dessous regroupent par cette clé.

Les exemples utilisent la syntaxe de MySQL 8.0 et créent leur propre table : exécutez-les dans le bac à sable sur une base sans données d'exemple.

Données d'exemple

CREATE TABLE contact (
    id INT PRIMARY KEY,
    email VARCHAR(100) NOT NULL,
    full_name VARCHAR(50) NOT NULL
);

INSERT INTO contact VALUES
    (1, 'anna@mail.com', 'Anna Lee'),
    (2, 'bob@mail.com', 'Bob Stone'),
    (3, 'anna@mail.com', 'Anna Lee'),
    (4, 'carl@mail.com', 'Carl Diaz'),
    (5, 'bob@mail.com', 'Bob Stone'),
    (6, 'Anna@Mail.com', 'Anna Lee'),
    (7, 'dina@mail.com', 'Dina Park');

Étape 1 : trouver les valeurs en double

Regroupez par la clé et gardez les groupes de plus d'une ligne :

SELECT email, COUNT(*) AS copies
FROM contact
GROUP BY email
HAVING COUNT(*) > 1;

Résultat : anna@mail.com avec 3 copies et bob@mail.com avec 2.

Remarquez que Anna@Mail.com a été compté avec anna@mail.com. La collation par défaut de MySQL compare les chaînes sans tenir compte de la casse : c'est donc la même valeur. PostgreSQL tient compte de la casse et ne trouverait que 2 copies d'Anna ; regroupez-y par LOWER(email) pour obtenir la même réponse. Pour une clé de plusieurs colonnes, listez-les toutes : GROUP BY first_name, last_name.

Étape 2 : voir chaque ligne en double

GROUP BY affiche une ligne par valeur. Pour voir les lignes en double elles-mêmes, avec leurs id, comptez les copies avec une fonction de fenêtrage et gardez les lignes dont le compte dépasse un :

WITH counted AS (
    SELECT id, email, full_name,
           COUNT(*) OVER (PARTITION BY email) AS copies
    FROM contact
)
SELECT id, email, full_name, copies
FROM counted
WHERE copies > 1
ORDER BY email, id;

Résultat : 5 lignes : id 1, 3, 6 pour Anna et 2, 5 pour Bob.

Avant de supprimer quoi que ce soit, examinez ces lignes : les copies désignent-elles vraiment la même chose, et laquelle faut-il garder ?

Étape 3 : marquer les copies à supprimer

Numérotez les lignes de chaque groupe avec ROW_NUMBER(). La ligne numéro 1 est celle à garder ; ici la plus ancienne, au plus petit id :

SELECT id, email,
       ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
FROM contact
ORDER BY email, id;

Résultat : les id 1, 2, 4, 7 obtiennent rn = 1 ; les id 3, 5, 6 obtiennent 2 ou 3 et sont les copies en trop.

L'ORDER BY dans la fenêtre décide de la ligne qui survit : ORDER BY id DESC garde la plus récente, ORDER BY updated_at DESC la dernière mise à jour.

Étape 4 : supprimer les copies en trop

Dans MySQL, une sous-requête dans DELETE ne peut pas lire la table dont on supprime (erreur 1093). Enveloppez-la dans une table dérivée, que MySQL matérialise d'abord :

DELETE FROM contact
WHERE id IN (
    SELECT id
    FROM (
        SELECT id,
               ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
        FROM contact
    ) AS numbered
    WHERE rn > 1
);

Résultat : 3 lignes supprimées ; les id 1, 2, 4 et 7 restent, un par e-mail.

Une forme plus ancienne, propre à MySQL, utilise une auto-jointure : supprimer chaque ligne pour laquelle il existe une ligne avec le même e-mail et un id plus petit :

DELETE c
FROM contact c
JOIN contact k ON k.email = c.email AND k.id < c.id;

Résultat : les 3 mêmes lignes supprimées.

Dans PostgreSQL, la forme habituelle est DELETE FROM contact c USING contact k WHERE k.email = c.email AND k.id < c.id; ; dans SQL Server, on peut supprimer directement depuis une CTE avec ROW_NUMBER().

Exécutez le SELECT de l'étape 3 avant le DELETE, comparez le nombre de lignes affectées et, en production, faites la suppression dans une transaction.

Étape 5 : empêcher de nouveaux doublons

Le nettoyage n'aide que jusqu'au prochain import. Une contrainte d'unicité fait rejeter les doublons par la base de données :

ALTER TABLE contact ADD CONSTRAINT uq_contact_email UNIQUE (email);

Ajoutez-la après le nettoyage : tant qu'il existe des doublons, la création de la contrainte échoue. Les imports peuvent ensuite utiliser INSERT IGNORE ou INSERT ... ON DUPLICATE KEY UPDATE (leçon 8.1) pour ignorer ou mettre à jour les lignes existantes.

Pratique

• Noms d'acteurs en double
• Acteurs homonymes
• E-mails en double

Points clés :

• Un doublon est une clé métier répétée, pas forcément une ligne identique ; regroupez par cette clé.
• GROUP BY key HAVING COUNT(*) > 1 trouve les valeurs en double ; COUNT(*) OVER (PARTITION BY key) montre chaque ligne en double.
• ROW_NUMBER() OVER (PARTITION BY key ORDER BY ...) marque la ligne à garder (rn = 1) et les copies à supprimer.
• Dans MySQL, supprimez via une table dérivée ou une auto-jointure pour éviter l'erreur 1093.
• La casse et la collation décident de ce qui est égal ; terminez par une contrainte UNIQUE.

Questions fréquentes

Comment trouver les lignes en double en SQL ?

Regroupez par les colonnes qui devraient être uniques et filtrez les groupes : SELECT email, COUNT(*) FROM contact GROUP BY email HAVING COUNT(*) > 1;. Pour voir les lignes complètes, utilisez COUNT(*) OVER (PARTITION BY email) et gardez les lignes où il dépasse 1.

Comment supprimer les doublons en gardant une ligne ?

Numérotez les lignes de chaque groupe avec ROW_NUMBER() OVER (PARTITION BY key ORDER BY id) et supprimez celles dont le numéro est supérieur à 1. Dans MySQL, placez la numérotation dans une table dérivée à l'intérieur du DELETE, ou utilisez une auto-jointure : DELETE c FROM t c JOIN t k ON k.key = c.key AND k.id < c.id.

Pourquoi MySQL affiche-t-il l'erreur 1093 quand je supprime des doublons ?

MySQL n'autorise pas une sous-requête dans DELETE ou UPDATE à lire directement la même table. Envelopper la sous-requête dans une table dérivée (SELECT id FROM (...) AS d) force MySQL à la matérialiser d'abord, et l'erreur disparaît.

Comment trouver les doublons sur plusieurs colonnes ?

Listez-les toutes dans GROUP BY et dans PARTITION BY : GROUP BY first_name, last_name HAVING COUNT(*) > 1. Des lignes ne sont des doublons que si toutes les colonnes listées correspondent.