Duplikate in SQL finden: Kompletter Leitfaden mit interaktiven Beispielen

Das Finden von Duplikaten in SQL kann eine mühsame Aufgabe sein, ist aber eine wesentliche Fähigkeit für jeden, der mit Datenbanken arbeitet. Duplikate können Fehler, Abweichungen und Inkonsistenzen in deinen Daten verursachen, was zu falschen Ergebnissen und schlechter Performance führt. Daher ist es entscheidend, Duplikate in deinen Tabellen zu identifizieren und zu entfernen, um die Datenintegrität und -genauigkeit zu gewährleisten.

SQL bietet verschiedene Möglichkeiten, Duplikate in deinen Daten zu finden, abhängig von deinen Anforderungen und der Struktur deiner Tabellen. Du kannst die GROUP BY- und HAVING-Klauseln verwenden, um Datensätze nach einer bestimmten Spalte zu gruppieren und Duplikate basierend auf einer Anzahl oder Bedingung herauszufiltern. Alternativ kannst du das DISTINCT-Schlüsselwort verwenden, um nur eindeutige Werte auszuwählen und diese mit der Originaltabelle zu vergleichen, um Duplikate zu identifizieren. Es gibt auch spezialisierte Funktionen und Operatoren wie COUNT(), EXISTS und JOIN, die dir helfen können, Duplikate in komplexeren Szenarien zu finden.

In diesem Leitfaden findest du interaktive SQL-Übungen, mit denen du das Finden von Duplikaten direkt im Browser üben kannst – keine Datenbankeinrichtung erforderlich. Schreibe Abfragen, erhalte sofortiges Feedback und sieh, ob deine Ergebnisse mit der erwarteten Ausgabe übereinstimmen.

In diesem Artikel werden wir verschiedene Techniken zum Finden von Duplikaten in SQL erkunden, von einfachen Abfragen bis hin zu fortgeschrittenen Methoden, und Beispiele sowie Best Practices bereitstellen, um dir zu helfen, diese Fähigkeit zu meistern. Egal ob du Anfänger oder erfahrener SQL-Entwickler bist, dieser Leitfaden wird dir helfen, die Qualität und Effizienz deiner Daten zu verbessern, indem du Duplikate in deinen Tabellen erkennst und eliminierst. Um diese Abfragen zu üben, erwäge die Verwendung eines modernen SQL-Editors wie dem SQL-Editor von Beekeeper Studio, der Syntaxhervorhebung und Autovervollständigungsfunktionen bietet.

Duplikate finden mit GROUP BY und HAVING Klauseln

Eine Möglichkeit, doppelte Werte in SQL zu finden, ist die Verwendung der GROUP BY- und HAVING-Klauseln. Diese Klauseln ermöglichen es dir, Zeilen zu gruppieren, die die gleichen Werte in einer oder mehreren Spalten haben, und dann die Gruppen basierend auf bestimmten Kriterien zu filtern. So funktioniert es:

1. Beginne damit, die Spalten auszuwählen, die du auf Duplikate überprüfen möchtest, mit der SELECT-Anweisung.
2. Verwende die GROUP BY-Klausel, um die Zeilen nach den ausgewählten Spalten zu gruppieren.
3. Verwende die COUNT-Funktion in der HAVING-Klausel, um die Gruppen herauszufiltern, die mehr als eine Zeile haben. Das sind die Gruppen, die Duplikate enthalten.

Angenommen, du hast eine Tabelle namens “customers” mit Spalten für “name” und “email”. Du möchtest alle Kunden finden, die sich mit der gleichen E-Mail-Adresse registriert haben. So würde die SQL-Abfrage aussehen:

SELECT name, email, COUNT(*)
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

Diese Abfrage gruppiert die Kunden nach ihren E-Mail-Adressen und zählt dann die Anzahl der Kunden in jeder Gruppe. Die HAVING-Klausel filtert Gruppen heraus, die nur einen Kunden haben, und lässt nur die Gruppen mit doppelten E-Mail-Adressen übrig.

Probiere es selbst aus:

[[ error ]]

[[ testData.solution ]]

Es ist wichtig zu beachten, dass die GROUP BY-Klausel alle Spalten enthalten muss, die du auswählst, außer denen, die eine Aggregatfunktion wie COUNT verwenden. Andernfalls wird die Abfrage einen Fehler zurückgeben.

Ein weiterer Punkt, den du beachten solltest, ist, dass die COUNT-Funktion alle Zeilen in jeder Gruppe zählt, nicht nur die eindeutigen. Wenn du also mehrere Zeilen mit demselben Namen und derselben E-Mail-Adresse hast, werden sie alle als Duplikate gezählt.

Zusätzlich zur Verwendung der GROUP BY- und HAVING-Klauseln kannst du auch andere SQL-Anweisungen wie ORDER BY, WHERE und JOIN verwenden, um deine Suche nach Duplikaten weiter zu verfeinern. Du kannst auch Unique-Constraints verwenden, um zu verhindern, dass doppelte Werte überhaupt in eine Tabelle eingefügt werden.

Insgesamt kann das Finden von Duplikaten in SQL ein mächtiges Werkzeug sein, um unverarbeitete Daten zu bereinigen oder potenzielle menschliche Fehler oder Anwendungsfehler zu identifizieren. Durch die Verwendung der richtigen Suchkriterien und die klare und prägnante Darstellung der Ergebnisse kannst du schnell doppelte Zeilen oder Werte identifizieren und Maßnahmen ergreifen, um sie zu korrigieren.

Verwendung der COUNT-Funktion

Eine Möglichkeit, Duplikate in SQL zu finden, ist die Verwendung der COUNT-Funktion. Die COUNT-Funktion ist eine Aggregatfunktion, die die Anzahl der Zeilen in einer Tabelle zählt, die eine bestimmte Bedingung erfüllen. Durch die Verwendung der COUNT-Funktion kannst du die Anzahl der Vorkommen eines bestimmten Wertes in einer Spalte zählen und Duplikate identifizieren.

Um die COUNT-Funktion zum Finden von Duplikaten zu verwenden, musst du die Zeilen nach der Spalte gruppieren, die du auf Duplikate überprüfen möchtest. Die GROUP BY-Klausel wird verwendet, um die Zeilen basierend auf den Werten in einer bestimmten Spalte zu gruppieren. Wenn du zum Beispiel Duplikate in der “email”-Spalte einer “users”-Tabelle finden möchtest, würdest du die Zeilen nach der “email”-Spalte gruppieren.

Hier ist ein Beispiel für eine Abfrage, die die COUNT-Funktion verwendet, um Duplikate in der “email”-Spalte einer “users”-Tabelle zu finden:

SELECT email, COUNT(email) as count
FROM users
GROUP BY email
HAVING COUNT(email) > 1;

In dieser Abfrage gruppiert die GROUP BY-Klausel die Zeilen nach der “email”-Spalte, und die COUNT-Funktion zählt die Anzahl der Vorkommen jeder E-Mail-Adresse. Die HAVING-Klausel filtert die Ergebnisse, um nur die E-Mail-Adressen anzuzeigen, die mehr als ein Vorkommen haben.

Das Ergebnis dieser Abfrage wird eine Tabelle sein, die die E-Mail-Adressen mit Duplikaten und die Anzahl der Vorkommen jeder E-Mail-Adresse anzeigt. Du kannst diese Information verwenden, um die doppelten Zeilen aus der Tabelle zu identifizieren und zu entfernen.

Die Verwendung der COUNT-Funktion ist eine einfache und effektive Methode, um Duplikate in SQL zu finden. Sie ermöglicht es dir, schnell die doppelten Werte in einer Spalte zu identifizieren und Maßnahmen zu ergreifen, um sie aus der Tabelle zu entfernen.

Verwendung der INNER JOIN Klausel

Eine der häufigsten Methoden, um Duplikate in SQL zu finden, ist die Verwendung der INNER JOIN-Klausel. Diese Klausel ermöglicht es dir, zwei oder mehr Tabellen basierend auf einer gemeinsamen Spalte zu kombinieren und nur die Zeilen zurückzugeben, die übereinstimmende Werte in beiden Tabellen haben.

Um die INNER JOIN-Klausel zum Finden von Duplikaten zu verwenden, musst du die Spalten identifizieren, die die doppelten Daten enthalten. Sobald du diese Spalten identifiziert hast, kannst du die INNER JOIN-Klausel verwenden, um die Tabelle mit diesen Spalten zu verknüpfen.

Hier ist ein Beispiel, wie man die INNER JOIN-Klausel verwendet, um Duplikate in einer Tabelle namens “orders” zu finden:

SELECT o1.order_id, o2.order_id
FROM orders o1
INNER JOIN orders o2
ON o1.customer_id = o2.customer_id
AND o1.order_date = o2.order_date
AND o1.order_id <> o2.order_id;

In diesem Beispiel wird die INNER JOIN-Klausel verwendet, um die “orders”-Tabelle mit sich selbst zu verknüpfen, wobei die Spalten “customer_id” und “order_date” als Verknüpfungskriterien dienen. Der “AND”-Operator wird verwendet, um anzugeben, dass alle drei Spalten übereinstimmen müssen, damit eine Zeile zurückgegeben wird. Die letzte Zeile der Abfrage, “AND o1.order_id <> o2.order_id”, stellt sicher, dass die Abfrage keine Zeilen zurückgibt, bei denen beide “order_id”-Werte gleich sind.

Das Ergebnis dieser Abfrage wird eine Liste aller Bestellungen sein, die die gleiche Kunden-ID und das gleiche Bestelldatum haben, aber unterschiedliche Bestell-IDs. Das sind die doppelten Bestellungen, die weiter untersucht werden müssen.

Übe mit Self-Joins:

[[ error ]]

[[ testData.solution ]]

Die Verwendung der INNER JOIN-Klausel auf diese Weise kann ein mächtiges Werkzeug zum Finden von Duplikaten in SQL sein. Es ist jedoch wichtig, bei der Verwendung dieser Methode vorsichtig zu sein, da es leicht passieren kann, versehentlich falsch positive Ergebnisse zu liefern oder Duplikate zu übersehen, die über mehrere Tabellen verteilt sind. Es ist immer eine gute Idee, deine Ergebnisse doppelt zu überprüfen und andere Methoden wie GROUP BY- und HAVING-Klauseln zu verwenden, um deine Erkenntnisse zu bestätigen.

Probiere es selbst aus:

[[ error ]]

[[ testData.solution ]]

Doppelte Paare mit Self-Joins finden

Eine weitere leistungsfähige Technik zum Finden von Duplikaten ist die Verwendung von Self-Joins, um Paare von Datensätzen zu identifizieren, die gemeinsame Merkmale teilen. Dieser Ansatz ist besonders nützlich, wenn du exakte Duplikatpaare finden möchtest, anstatt nur zu identifizieren, dass Duplikate existieren.

Ein Self-Join beinhaltet das Verknüpfen einer Tabelle mit sich selbst unter Verwendung verschiedener Aliase. Durch das Verknüpfen auf den Spalten, die Duplikate definieren, und das Sicherstellen, dass du einen Datensatz nicht mit sich selbst abgleichst, kannst du Duplikatpaare effektiv identifizieren.

Wenn du zum Beispiel eine Transaktionstabelle hast und Paare von Transaktionen mit der gleichen Kunden-ID und dem gleichen Betrag, aber unterschiedlichen Transaktions-IDs finden möchtest, kannst du einen Self-Join wie folgt verwenden:

SELECT t1.transaction_id as transaction_id1, 
       t2.transaction_id as transaction_id2, 
       t1.customer_id, 
       t1.amount
FROM transactions t1
INNER JOIN transactions t2 
ON t1.customer_id = t2.customer_id 
AND t1.amount = t2.amount 
AND t1.transaction_id < t2.transaction_id;

Die wichtigsten Punkte in dieser Abfrage sind:

• t1 und t2 sind Aliase für dieselbe Tabelle
• Die Verknüpfungsbedingungen spezifizieren, was Datensätze zu “Duplikaten” macht (customer_id und amount)
• t1.transaction_id < t2.transaction_id stellt sicher, dass wir jedes Paar nur einmal erhalten und keinen Datensatz mit sich selbst abgleichen

Probiere es selbst aus:

[[ error ]]

[[ testData.solution ]]

Duplikate mit Common Table Expressions (CTE) finden

Eine der Möglichkeiten, Duplikate in SQL zu finden, ist die Verwendung von Common Table Expressions (CTE). Eine CTE ist eine temporäre Ergebnismenge, die innerhalb des Ausführungsbereichs einer einzelnen SELECT-, INSERT-, UPDATE-, DELETE- oder CREATE VIEW-Anweisung definiert wird.

Um Duplikate mit CTE zu finden, kann man die ROW_NUMBER()-Funktion verwenden, die jeder Zeile innerhalb einer Partition einer Ergebnismenge eine eindeutige fortlaufende Nummer zuweist. Die PARTITION BY-Klausel gibt die Spalten an, die zur Definition der Partition verwendet werden, und die ORDER BY-Klausel gibt die Reihenfolge der Zeilen innerhalb jeder Partition an.

Hier ist ein Beispiel für die Verwendung von CTE zum Finden von Duplikaten:

WITH CTE AS (
   SELECT column1, column2, column3, ROW_NUMBER() OVER(PARTITION BY column1, column2, column3 ORDER BY column1, column2, column3) AS RowNumber
   FROM table_name
)
SELECT *
FROM CTE
WHERE RowNumber > 1

In diesem Beispiel wird die CTE mit den Spalten definiert, die auf Duplikate überprüft werden sollen. Die ROW_NUMBER()-Funktion wird verwendet, um eine Sequenznummer für jede Zeile innerhalb einer Partition der Ergebnismenge zu generieren, wobei die Partition durch die in der PARTITION BY-Klausel angegebenen Spalten definiert wird. Die Ergebnismenge wird dann gefiltert, um nur Zeilen anzuzeigen, bei denen die RowNumber größer als 1 ist, was auf Duplikate hinweist.

Es ist wichtig zu beachten, dass die ORDER BY-Klausel innerhalb der ROW_NUMBER()-Funktion mit der ORDER BY-Klausel in der Haupt-SELECT-Anweisung übereinstimmen muss, da sonst die Ergebnisse möglicherweise nicht genau sind.

Die Verwendung von CTE zum Finden von Duplikaten kann besonders nützlich in SQL Server 2017 sein, das Unterstützung für Graphverarbeitung mit Common Table Expressions eingeführt hat. Dies ermöglicht komplexere Abfragen, die Beziehungen zwischen Daten einbeziehen.

Insgesamt kann die Verwendung von CTE zum Finden von Duplikaten in SQL ein mächtiges Werkzeug für die Datenanalyse und -verwaltung sein.

Probiere es selbst aus:

[[ error ]]

[[ testData.solution ]]

Duplikate mit Window-Funktionen finden

Wenn es darum geht, Duplikate in SQL zu finden, ist eine der effizientesten Methoden die Verwendung von Window-Funktionen. Window-Funktionen sind ein mächtiges Werkzeug, das verwendet werden kann, um Berechnungen über eine Menge von Zeilen durchzuführen, die mit der aktuellen Zeile zusammenhängen.

Um Duplikate mit Window-Funktionen zu finden, kannst du die ROW_NUMBER()-Funktion in Kombination mit einer PARTITION BY-Klausel verwenden. Die ROW_NUMBER()-Funktion weist jeder Zeile innerhalb einer Partition eine eindeutige Nummer zu, und die PARTITION BY-Klausel gruppiert Zeilen in Partitionen basierend auf einer bestimmten Spalte oder Spaltenmenge.

Angenommen, du hast eine Tabelle von Benutzern, die Spalten für user_id, username und email enthält. Um alle Benutzer zu finden, die doppelte E-Mails haben, kannst du die folgende Select-Anweisung verwenden:

SELECT user_id, username, email
FROM (
  SELECT user_id, username, email,
    ROW_NUMBER() OVER (PARTITION BY email ORDER BY user_id) AS row_num
  FROM users
) AS subquery
WHERE row_num > 1;

In diesem Beispiel partitioniert die PARTITION BY-Klausel die Zeilen nach der email-Spalte, und die ROW_NUMBER()-Funktion weist jeder Zeile innerhalb der Partition basierend auf der user_id-Spalte eine eindeutige Nummer zu. Die WHERE-Klausel filtert alle Zeilen heraus außer denen mit einer row_num größer als 1, das sind die Zeilen mit doppelten E-Mails.

Übe mit ROW_NUMBER():

[[ error ]]

[[ testData.solution ]]

Es ist wichtig zu beachten, dass Window-Funktionen in Kombination mit anderen SQL-Funktionen wie CASE-Anweisungen und Aggregatfunktionen verwendet werden können, um komplexere Abfragen zu erstellen. Du könntest zum Beispiel eine CASE-Anweisung verwenden, um Zeilen in eine bestimmte Kategorie zu gruppieren, und dann eine Window-Funktion verwenden, um Duplikate innerhalb dieser Kategorie zu finden.

Zusammenfassend kann die Verwendung von Window-Funktionen zum Finden von Duplikaten in SQL eine leistungsfähige und effiziente Methode sein. Durch die Verwendung der ROW_NUMBER()-Funktion in Kombination mit einer PARTITION BY-Klausel kannst du Zeilen leicht in Partitionen gruppieren und jeder Zeile innerhalb der Partition eindeutige Nummern zuweisen. Dies ermöglicht es dir, doppelte Zeilen aus deinen Ergebnissen schnell zu identifizieren und zu entfernen.

Probiere es selbst aus:

[[ error ]]

[[ testData.solution ]]

Verwendung der ROW_NUMBER()-Funktion mit PARTITION BY-Klausel

Bei der Suche nach Duplikaten in einer SQL-Datenbank ist die ROW_NUMBER()-Funktion mit der PARTITION BY-Klausel ein nützliches Werkzeug. Diese Funktion weist jeder Zeile in einer Ergebnismenge basierend auf den angegebenen Partitionierungskriterien eine eindeutige Nummer zu.

Die PARTITION BY-Klausel ermöglicht es dem Benutzer, die Zeilen basierend auf einer oder mehreren Spalten in Partitionen zu gruppieren. Das bedeutet, dass die ROW_NUMBER()-Funktion jeder Zeile innerhalb jeder Partition eine eindeutige Nummer zuweist, anstatt über die gesamte Ergebnismenge hinweg.

Betrachte zum Beispiel eine Tabelle von Kundenbestellungen mit Spalten für Bestell-ID, Kunden-ID und Bestelldatum. Um doppelte Bestellungen für jeden Kunden zu finden, könnte man die folgende Abfrage verwenden:

SELECT order_id, customer_id, order_date,
       ROW_NUMBER() OVER (PARTITION BY customer_id, order_date
                          ORDER BY order_id) AS row_num
FROM orders

In dieser Abfrage wird die ROW_NUMBER()-Funktion mit der PARTITION BY-Klausel verwendet, um die Bestellungen nach Kunde und Bestelldatum zu gruppieren. Die Funktion weist dann jeder Zeile innerhalb jeder Partition basierend auf der Bestell-ID eine eindeutige Nummer zu.

Die resultierende Tabelle wird eine zusätzliche Spalte namens “row_num” haben, die die zugewiesenen Zeilennummern enthält. Duplikate können dann identifiziert werden, indem Zeilen mit row_num größer als 1 ausgewählt werden.

Die Verwendung der ROW_NUMBER()-Funktion mit der PARTITION BY-Klausel kann ein mächtiges Werkzeug zum Finden von Duplikaten in SQL-Datenbanken sein. Durch das Gruppieren der Zeilen in Partitionen basierend auf bestimmten Kriterien kann die Funktion jeder Zeile innerhalb jeder Partition eindeutige Nummern zuweisen, was die Identifizierung von Duplikaten erleichtert.

Verwendung der CASE-Anweisung mit ROW_NUMBER()-Funktion

In SQL wird die ROW_NUMBER()-Funktion verwendet, um jeder Zeile in einer Ergebnismenge eine eindeutige fortlaufende Nummer zuzuweisen. Diese Funktion kann in Verbindung mit der CASE-Anweisung verwendet werden, um doppelte Datensätze in einer Tabelle zu identifizieren.

Die CASE-Anweisung ermöglicht es, bedingte Logik auf jede Zeile in einer Ergebnismenge anzuwenden. Durch die Verwendung der ROW_NUMBER()-Funktion innerhalb der CASE-Anweisung kann die Anzahl der Vorkommen jedes Datensatzes bestimmt werden.

Zum Beispiel kann die folgende SQL-Abfrage verwendet werden, um doppelte Datensätze in einer “users”-Tabelle basierend auf den Spalten “first_name” und “last_name” zu finden:

SELECT *,
CASE
    WHEN ROW_NUMBER() OVER (PARTITION BY first_name, last_name ORDER BY id) > 1
    THEN 'Duplikat'
    ELSE 'Einzigartig'
END AS duplicate_status
FROM users;

Die obige Abfrage partitioniert die Ergebnismenge nach den Spalten “first_name” und “last_name” und ordnet die Zeilen nach der Spalte “id”. Die ROW_NUMBER()-Funktion weist jeder Zeile innerhalb jeder Partition eine eindeutige fortlaufende Nummer zu.

Die CASE-Anweisung prüft dann, ob der ROW_NUMBER()-Wert größer als 1 ist. Wenn ja, wird die Zeile als Duplikat identifiziert. Wenn nicht, wird die Zeile als einzigartig identifiziert.

Die Ergebnismenge wird eine zusätzliche Spalte namens “duplicate_status” enthalten, die entweder “Duplikat” oder “Einzigartig” für jede Zeile anzeigt.

Im obigen Beispiel werden die Zeilen 1, 2 und 4 als einzigartig identifiziert, während die Zeilen 3 und 5 als Duplikate basierend auf den Spalten “first_name” und “last_name” identifiziert werden.

Durch die Verwendung der ROW_NUMBER()-Funktion innerhalb der CASE-Anweisung können doppelte Datensätze in einer SQL-Tabelle leicht identifiziert und verwaltet werden.