👯Duplikate

Finden, genau eine Zeile behalten, unscharfe Dubletten und Vorbeugen per UNIQUE.

① finden             ② Gewinner festlegen          ③ Rest löschen           ④ vorbeugen
GROUP BY email   ─►  ROW_NUMBER() OVER (       ─►  DELETE … WHERE nr > 1  ─►  CREATE UNIQUE INDEX
HAVING COUNT(*)>1      PARTITION BY email           (vorher: FKs umhängen!)     ON kontakte (LOWER(TRIM(email)))
                       ORDER BY angelegt DESC)

👯 Tipp 1★ EinsteigerGROUP BYHAVINGCOUNT

Duplikate finden mit GROUP BY … HAVING

😖 Problem

Nach einem Import stehen Kontakte mehrfach in der Tabelle. Welche E-Mail-Adressen kommen öfter als einmal vor – und in welchen Zeilen?

💡 Lösung

Nach dem Merkmal gruppieren, das „gleich“ definiert, und mit HAVING COUNT(*) > 1 nur Gruppen mit mehreren Zeilen behalten. MIN/MAX und eine Liste der IDs zeigen, welche Zeilen betroffen sind.

🧪 Ausprobieren – Vorher / Nachher

jede Ausführung startet mit frischen Beispieldaten

SELECT email,
       COUNT(*)                      AS anzahl,
       MIN(kontakt_id)               AS erste_id,
       MAX(kontakt_id)               AS letzte_id,
       GROUP_CONCAT(kontakt_id, ',') AS alle_ids
  FROM kontakte
 GROUP BY email
HAVING COUNT(*) > 1
 ORDER BY anzahl DESC;

SQL-Engine wird geladen …

🌍 Dialekte nebeneinander

Ausgeführt werden nur SQLite (sql.js) und – wo markiert – PostgreSQL (PGlite). MySQL/MariaDB, SQL Server und Oracle sind nach der offiziellen Dokumentation geschrieben, laufen hier aber nicht.

PostgreSQL

▶ ausführbar · PGlite

SELECT email, COUNT(*) AS anzahl,
       string_agg(kontakt_id::text, ',' ORDER BY kontakt_id) AS ids
  FROM kontakte GROUP BY email HAVING COUNT(*) > 1;

MySQL / MariaDB

nur gezeigt, nicht ausgeführt

SELECT email, COUNT(*) AS anzahl,
       GROUP_CONCAT(kontakt_id ORDER BY kontakt_id) AS ids
  FROM kontakte GROUP BY email HAVING COUNT(*) > 1;

Achtung Kollation: Die Standard-Kollationen von MySQL/MariaDB vergleichen ohne Groß/Klein – dort landen „Anna.Berger@…“ und „anna.berger@…“ schon in derselben Gruppe.

SQL Serverab 2017 (STRING_AGG)

nur gezeigt, nicht ausgeführt

SELECT email, COUNT(*) AS anzahl,
       STRING_AGG(CAST(kontakt_id AS varchar(10)), ',')
         WITHIN GROUP (ORDER BY kontakt_id) AS ids
  FROM kontakte GROUP BY email HAVING COUNT(*) > 1;

Übliche Server-Kollationen sind ebenfalls case-insensitive (…_CI_…).

Oracle

nur gezeigt, nicht ausgeführt

SELECT email, COUNT(*) AS anzahl,
       LISTAGG(kontakt_id, ',') WITHIN GROUP (ORDER BY kontakt_id) AS ids
  FROM kontakte GROUP BY email HAVING COUNT(*) > 1;

SQLite

▶ ausgeführt · sql.js

SELECT email, COUNT(*) AS anzahl, GROUP_CONCAT(kontakt_id, ',') AS ids
  FROM kontakte GROUP BY email HAVING COUNT(*) > 1;

Textvergleich standardmäßig mit BINARY-Kollation: Groß/Klein zählt.

⚠️ Fallstricke

„Duplikat“ ist eine fachliche Definition: gleiche E-Mail? gleicher Name und Ort? Jonas Feld aus Bonn (8) ist vielleicht umgezogen – oder eine andere Person.
Ob Anna.Berger@… und anna.berger@… gleich sind, entscheidet die Kollation – in SQLite und PostgreSQL nein, in den Standard-Kollationen von MySQL und SQL Server ja.

📚 Belege

SQLite: Window Functions – Window Functions seit 3.25.0, ROWS/RANGE
SQL Server: STRING_AGG – STRING_AGG seit 2017 (14.x)

Grundlagen in der Schwester-App

↗ VisualSql: GROUP BY und HAVING animiert

👯 Tipp 2★★ FortgeschrittenROW_NUMBERPARTITION BYDELETE

Genau eine Zeile je Gruppe behalten (ROW_NUMBER)

😖 Problem

Von jeder mehrfach vorhandenen E-Mail soll nur der neueste Datensatz übrig bleiben. Welche Zeile „gewinnt“, muss eindeutig festgelegt sein.

💡 Lösung

ROW_NUMBER() OVER (PARTITION BY email ORDER BY angelegt DESC, kontakt_id DESC) nummeriert jede Gruppe durch – Nummer 1 ist der Gewinner. Gelöscht wird alles mit Nummer > 1. Der zweite Sortierschlüssel (kontakt_id) entscheidet bei Gleichstand.

🧪 Ausprobieren – Vorher / Nachher

jede Ausführung startet mit frischen Beispieldaten

-- erst ansehen …
SELECT kontakt_id, email, angelegt,
       ROW_NUMBER() OVER (PARTITION BY email ORDER BY angelegt DESC, kontakt_id DESC) AS nr
  FROM kontakte
 ORDER BY email, nr;

-- … dann löschen: alles außer Nummer 1
DELETE FROM kontakte
 WHERE kontakt_id IN (
   SELECT kontakt_id FROM (
     SELECT kontakt_id,
            ROW_NUMBER() OVER (PARTITION BY email ORDER BY angelegt DESC, kontakt_id DESC) AS nr
       FROM kontakte) AS t
    WHERE nr > 1);

SQL-Engine wird geladen …

🌍 Dialekte nebeneinander

Ausgeführt werden nur SQLite (sql.js) und – wo markiert – PostgreSQL (PGlite). MySQL/MariaDB, SQL Server und Oracle sind nach der offiziellen Dokumentation geschrieben, laufen hier aber nicht.

PostgreSQL

▶ ausführbar · PGlite

DELETE FROM kontakte
 WHERE kontakt_id IN (
   SELECT kontakt_id FROM (
     SELECT kontakt_id, ROW_NUMBER() OVER (
              PARTITION BY email ORDER BY angelegt DESC, kontakt_id DESC) AS nr
       FROM kontakte) t
    WHERE nr > 1)
RETURNING *;          -- zeigt, was gelöscht wurde

-- nur lesen, je Gruppe die neueste Zeile:
SELECT DISTINCT ON (email) * FROM kontakte ORDER BY email, angelegt DESC;

MySQL / MariaDBab MySQL 8.0 / MariaDB 10.2

nur gezeigt, nicht ausgeführt

DELETE k FROM kontakte k
JOIN (SELECT kontakt_id,
             ROW_NUMBER() OVER (PARTITION BY email
                                ORDER BY angelegt DESC, kontakt_id DESC) AS nr
        FROM kontakte) t ON t.kontakt_id = k.kontakt_id
WHERE t.nr > 1;

Direktes DELETE … WHERE id IN (SELECT … FROM kontakte) scheitert in MySQL (Fehler 1093) – über JOIN mit abgeleiteter Tabelle geht es.

SQL Server

nur gezeigt, nicht ausgeführt

WITH d AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY email
                                ORDER BY angelegt DESC, kontakt_id DESC) AS nr
    FROM kontakte)
DELETE FROM d WHERE nr > 1;

In SQL Server kann man direkt aus einer CTE löschen – sehr kompakt.

Oracle

nur gezeigt, nicht ausgeführt

DELETE FROM kontakte
 WHERE ROWID IN (
   SELECT rid FROM (
     SELECT ROWID AS rid,
            ROW_NUMBER() OVER (PARTITION BY email
                               ORDER BY angelegt DESC, kontakt_id DESC) AS nr
       FROM kontakte)
    WHERE nr > 1);

ROWID adressiert die physische Zeile – funktioniert auch ohne Primärschlüssel.

SQLiteab 3.25.0 (Window Functions)

▶ ausgeführt · sql.js

DELETE FROM kontakte
 WHERE rowid IN (
   SELECT rowid FROM (
     SELECT rowid, ROW_NUMBER() OVER (PARTITION BY email
                                      ORDER BY angelegt DESC) AS nr
       FROM kontakte)
    WHERE nr > 1);

Ohne Primärschlüssel hilft auch hier die rowid.

⚠️ Fallstricke

Ohne eindeutige Sortierung (Gleichstand bei angelegt) ist zufällig, welche Zeile überlebt – immer einen eindeutigen zweiten Schlüssel anhängen.
Vor dem DELETE das SELECT laufen lassen und die Zahl der zu löschenden Zeilen prüfen; bei großen Tabellen in einer Transaktion und in Batches (siehe Praxis-Rezepte).
Gibt es Fremdschlüssel auf die Dubletten (z. B. Bestellungen), müssen diese vorher auf den Gewinner umgehängt werden.

📚 Belege

SQLite: Window Functions – Window Functions seit 3.25.0, ROWS/RANGE
PostgreSQL: SELECT – DISTINCT ON – DISTINCT ON
MySQL: Window Functions – Window Functions in MySQL 8.0

Grundlagen in der Schwester-App

↗ VisualSql: Window Functions mit Fensterrahmen

👯 Tipp 3★★ FortgeschrittenEXISTSSelbstjoinDELETE

Ältesten behalten mit EXISTS-Selbstjoin

😖 Problem

Nicht jede Datenbank (oder ältere Version) hat Window Functions. Gesucht ist ein Weg, der überall funktioniert.

💡 Lösung

Eine Zeile ist überflüssig, wenn es eine andere Zeile mit derselben E-Mail und kleinerer ID gibt. Genau das prüft EXISTS mit einem Selbstbezug – übrig bleibt je Gruppe die kleinste ID.

🧪 Ausprobieren – Vorher / Nachher

jede Ausführung startet mit frischen Beispieldaten

DELETE FROM kontakte
 WHERE EXISTS (
   SELECT 1
     FROM kontakte AS k2
    WHERE k2.email      = kontakte.email
      AND k2.kontakt_id < kontakte.kontakt_id);

SQL-Engine wird geladen …

🌍 Dialekte nebeneinander

Ausgeführt werden nur SQLite (sql.js) und – wo markiert – PostgreSQL (PGlite). MySQL/MariaDB, SQL Server und Oracle sind nach der offiziellen Dokumentation geschrieben, laufen hier aber nicht.

PostgreSQL

▶ ausführbar · PGlite

DELETE FROM kontakte k
 USING kontakte k2
 WHERE k2.email = k.email
   AND k2.kontakt_id < k.kontakt_id;

DELETE … USING ist PostgreSQL-Syntax für Joins im DELETE.

MySQL / MariaDB

nur gezeigt, nicht ausgeführt

DELETE k FROM kontakte k
  JOIN kontakte k2
    ON k2.email = k.email AND k2.kontakt_id < k.kontakt_id;

SQL Server

nur gezeigt, nicht ausgeführt

DELETE k FROM kontakte k
 WHERE EXISTS (SELECT 1 FROM kontakte k2
                WHERE k2.email = k.email AND k2.kontakt_id < k.kontakt_id);

Oracle

nur gezeigt, nicht ausgeführt

DELETE FROM kontakte k
 WHERE EXISTS (SELECT 1 FROM kontakte k2
                WHERE k2.email = k.email AND k2.kontakt_id < k.kontakt_id);

SQLite

▶ ausgeführt · sql.js

DELETE FROM kontakte
 WHERE EXISTS (SELECT 1 FROM kontakte AS k2
                WHERE k2.email = kontakte.email
                  AND k2.kontakt_id < kontakte.kontakt_id);

⚠️ Fallstricke

Ohne Index auf email prüft jede Zeile alle anderen – quadratischer Aufwand. Vorher CREATE INDEX … (email, kontakt_id).
Semantik prüfen: Der Selbstjoin behält die älteste ID, nicht zwingend den besten Datensatz (vollständigste Adresse, neueste Stadt).

📚 Belege

SQLite: Window Functions – Window Functions seit 3.25.0, ROWS/RANGE

👯 Tipp 4★★ FortgeschrittenLOWERTRIMNormalisierung

Unscharfe Duplikate: normalisieren vor dem Vergleich

😖 Problem

Anna.Berger@example.org (Großbuchstaben, Leerzeichen am Ende) und anna.berger@example.org sind dieselbe Adresse – ein exakter Vergleich findet sie nicht.

💡 Lösung

Vor dem Gruppieren normalisieren: LOWER(TRIM(email)). So werden aus 2 Duplikatgruppen 3, und Anna hat plötzlich drei Einträge. Im Diagramm unten kannst du zwischen exakt und normalisiert umschalten.

🧪 Ausprobieren – Vorher / Nachher

jede Ausführung startet mit frischen Beispieldaten

SELECT LOWER(TRIM(email))            AS email_norm,
       COUNT(*)                      AS anzahl,
       GROUP_CONCAT(kontakt_id, ',') AS ids
  FROM kontakte
 GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1
 ORDER BY email_norm;

SQL-Engine wird geladen …

🌍 Dialekte nebeneinander

Ausgeführt werden nur SQLite (sql.js) und – wo markiert – PostgreSQL (PGlite). MySQL/MariaDB, SQL Server und Oracle sind nach der offiziellen Dokumentation geschrieben, laufen hier aber nicht.

PostgreSQL

▶ ausführbar · PGlite

SELECT lower(trim(email)) AS email_norm, COUNT(*)
  FROM kontakte GROUP BY 1 HAVING COUNT(*) > 1;
-- Alternative: Datentyp citext (Erweiterung) vergleicht ohne Groß/Klein

MySQL / MariaDB

nur gezeigt, nicht ausgeführt

SELECT LOWER(TRIM(email)) AS email_norm, COUNT(*)
  FROM kontakte GROUP BY email_norm HAVING COUNT(*) > 1;

Mit case-insensitiver Kollation ist LOWER() überflüssig – TRIM() aber nicht.

SQL Serverab 2017 (TRIM)

nur gezeigt, nicht ausgeführt

SELECT LOWER(TRIM(email)) AS email_norm, COUNT(*)
  FROM kontakte GROUP BY LOWER(TRIM(email)) HAVING COUNT(*) > 1;
-- vor 2017: LTRIM(RTRIM(email))

Oracle

nur gezeigt, nicht ausgeführt

SELECT LOWER(TRIM(email)) AS email_norm, COUNT(*)
  FROM kontakte GROUP BY LOWER(TRIM(email)) HAVING COUNT(*) > 1;

SQLite

▶ ausgeführt · sql.js

SELECT LOWER(TRIM(email)) AS email_norm, COUNT(*)
  FROM kontakte GROUP BY LOWER(TRIM(email)) HAVING COUNT(*) > 1;

LOWER() wandelt in SQLite nur ASCII-Buchstaben um (ohne ICU-Erweiterung) – „Ä“ bleibt „Ä“.

⚠️ Fallstricke

Normalisieren ist Fachlogik: Bei E-Mail-Adressen ist der lokale Teil laut Standard theoretisch case-sensitiv, praktisch behandeln ihn fast alle Anbieter ohne Groß/Klein.
Weitergehende Unschärfe (Tippfehler, „Str.“ vs. „Straße“) braucht Ähnlichkeitsmaße wie Levenshtein oder Trigramme (PostgreSQL: pg_trgm) – und einen Menschen, der entscheidet.
Die normalisierte Form am besten dauerhaft speichern (Generated Column) und darauf den UNIQUE-Index setzen – siehe nächster Tipp.

📚 Belege

SQLite: Indizes auf Ausdrücken – Ausdrucksindizes seit 3.9.0

👯 Tipp 5★★ FortgeschrittenUNIQUEAusdrucksindexpartieller Index

Duplikate verhindern: UNIQUE auf den normalisierten Wert

😖 Problem

Aufräumen hilft nur bis zum nächsten Import. Die Datenbank soll neue Dubletten selbst ablehnen – auch in Groß/Klein-Varianten.

💡 Lösung

Ein eindeutiger Index auf LOWER(TRIM(email)) lehnt jede Variante einer vorhandenen Adresse ab. Unten wird erst aufgeräumt, dann der Index angelegt – das letzte INSERT scheitert mit einem UNIQUE-Fehler, so soll es sein.

🧪 Ausprobieren – Vorher / Nachher

jede Ausführung startet mit frischen Beispieldaten

-- 1. aufräumen (je normalisierter Adresse die kleinste ID behalten)
DELETE FROM kontakte
 WHERE EXISTS (SELECT 1 FROM kontakte k2
                WHERE LOWER(TRIM(k2.email)) = LOWER(TRIM(kontakte.email))
                  AND k2.kontakt_id < kontakte.kontakt_id);

-- 2. vorbeugen
CREATE UNIQUE INDEX ux_kontakte_email ON kontakte (LOWER(TRIM(email)));

-- 3. Test: gleiche Adresse, andere Schreibweise
INSERT INTO kontakte VALUES (10, 'Anna B.', 'ANNA.BERGER@example.org', 'Hamburg', '2026-03-20');

SQL-Engine wird geladen …

🌍 Dialekte nebeneinander

Ausgeführt werden nur SQLite (sql.js) und – wo markiert – PostgreSQL (PGlite). MySQL/MariaDB, SQL Server und Oracle sind nach der offiziellen Dokumentation geschrieben, laufen hier aber nicht.

PostgreSQLab 15 (NULLS NOT DISTINCT)

▶ ausführbar · PGlite

CREATE UNIQUE INDEX ux_kontakte_email ON kontakte (lower(trim(email)));

-- nur aktive Zeilen eindeutig (partieller Index):
CREATE UNIQUE INDEX ux_aktiv ON kontakte (lower(email)) WHERE geloescht_am IS NULL;

-- mehrere NULL verbieten:
ALTER TABLE kontakte ADD UNIQUE NULLS NOT DISTINCT (telefon);

MySQL / MariaDBab MySQL 8.0.13

nur gezeigt, nicht ausgeführt

-- funktionaler Schlüsselteil: doppelte Klammern!
CREATE UNIQUE INDEX ux_kontakte_email ON kontakte ((LOWER(TRIM(email))));
-- keine partiellen Indizes → Generated Column, die für
-- gelöschte Zeilen NULL ist, und darauf UNIQUE

SQL Serverab 2008 (gefilterte Indizes)

nur gezeigt, nicht ausgeführt

ALTER TABLE kontakte ADD email_norm AS LOWER(TRIM(email)) PERSISTED;
CREATE UNIQUE INDEX ux_kontakte_email ON kontakte (email_norm);

-- gefilterter Index, z. B. mehrere NULL erlauben:
CREATE UNIQUE INDEX ux_telefon ON kontakte (telefon) WHERE telefon IS NOT NULL;

Ein UNIQUE-Constraint erlaubt in SQL Server nur eine NULL – der gefilterte Index ist der übliche Ausweg.

Oracle

nur gezeigt, nicht ausgeführt

CREATE UNIQUE INDEX ux_kontakte_email ON kontakte (LOWER(TRIM(email)));
-- „partiell“: Ausdruck liefert für ausgenommene Zeilen NULL
CREATE UNIQUE INDEX ux_aktiv ON kontakte (
  CASE WHEN geloescht_am IS NULL THEN LOWER(email) END);

Zeilen, deren Indexschlüssel komplett NULL ist, landen nicht im B-Baum-Index.

SQLiteab 3.9.0 (Ausdruck) / 3.8.0 (partiell)

▶ ausgeführt · sql.js

CREATE UNIQUE INDEX ux_kontakte_email ON kontakte (LOWER(TRIM(email)));
CREATE UNIQUE INDEX ux_aktiv ON kontakte (email) WHERE geloescht_am IS NULL;

⚠️ Fallstricke

Der Index lässt sich erst anlegen, wenn keine Dubletten mehr existieren – sonst schlägt CREATE UNIQUE INDEX fehl.
Upsert gegen einen Ausdrucksindex: In PostgreSQL und SQLite muss das Konfliktziel denselben Ausdruck nennen (ON CONFLICT (lower(trim(email)))).
NULL ist in UNIQUE meist „nicht gleich“ – mehrere NULL-Werte sind erlaubt (außer SQL Server-Constraint, PostgreSQL mit NULLS NOT DISTINCT).

📚 Belege

SQLite: Indizes auf Ausdrücken – Ausdrucksindizes seit 3.9.0
SQLite: Partielle Indizes – partielle Indizes seit 3.8.0
PostgreSQL: Partielle Indizes – CREATE UNIQUE INDEX … WHERE …
PostgreSQL: Release Notes 15 – MERGE seit 15, UNIQUE NULLS NOT DISTINCT seit 15
MySQL: CREATE INDEX (funktionale Schlüsselteile) – funktionale Indizes seit 8.0.13, keine WHERE-Klausel (keine partiellen Indizes)
SQL Server: Gefilterte Indizes (2008) – gefilterte Indizes seit 2008