Wie werden Sie Duplikate in einem SQL JOIN los?

Haben Sie unerwünschte Duplikate in Ihrer SQL JOIN-Abfrage? In diesem Artikel erläutere ich die möglichen Gründe für Duplikate nach dem Joinen von Tabellen in SQL und zeige, wie Sie eine Abfrage je nach Grund für die Duplikate korrigieren können.

Datenanalysten mit wenig Erfahrung in SQL-JOINs stoßen oft auf unerwünschte Duplikate in der Ergebnismenge. Für Anfänger ist es schwierig, den Grund für diese Duplikate in JOINs zu identifizieren.

Der beste Weg, SQL-JOINs zu lernen, ist die Praxis. Ich empfehle den interaktiven SQL-JOINs Kurs. Er enthält über 90 Übungen, mit denen Sie die verschiedenen JOIN-Typen in SQL üben können.

In diesem Artikel werde ich die häufigsten Probleme erörtern, die zu Duplikaten in SQL JOIN-Ausgaben führen. Ich werde auch mögliche Lösungen für diese häufigen Probleme aufzeigen.

Beginnen wir mit einem sehr kurzen Überblick über SQL-JOINs.

SQL-JOINs Überblick

JOIN ist ein SQL-Konstrukt zur Abfrage von Informationen aus zwei oder mehr Tabellen innerhalb derselben Abfrage.

Nehmen wir an, Sie haben eine Liste der 100 besten Filme des 20. Jahrhunderts und möchten sie auf die Filme der derzeit lebenden Regisseure beschränken. In Ihrer movies Tabelle haben Sie keine detaillierten Informationen über die Regisseure, sondern nur deren IDs. Aber Sie haben eine separate directors Tabelle mit der ID, dem vollständigen Namen, dem Geburtsjahr und dem Todesjahr (falls zutreffend) jedes Regisseurs.

In Ihrer Abfrage können Sie zwei Tabellen nach der ID des Regisseurs verknüpfen, um eine Liste der Filme zu erhalten, die von derzeit lebenden Regisseuren gedreht wurden:

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

Wie Sie sehen, geben wir die Tabellen, die wir verknüpfen wollen, in den Klauseln FROM und JOIN an. In der ON-Klausel geben wir dann die Spalten aus jeder Tabelle an, die für die Verknüpfung dieser Tabellen verwendet werden sollen. Wenn Sie noch nicht mit SQL-JOINs vertraut sind, lesen Sie diesen Leitfaden zur Einführung. Hier finden Sie auch einen SQL JOIN-Spickzettel mit der Syntax und Beispielen für verschiedene JOINs.

Der SQL JOIN ist ein großartiges Werkzeug, das eine Vielzahl von Optionen bietet, die über die einfache Verknüpfung von zwei Tabellen hinausgehen. Wenn Sie mit den SQL JOIN-Typen nicht vertraut sind, lesen Sie diesen Artikel, der sie mit Illustrationen und Beispielen erklärt. Je nach Anwendungsfall können Sie INNER JOIN, LEFT JOIN, RIGHT JOIN und FULL JOIN wählen. Möglicherweise müssen Sie sogar Tabellen ohne eine gemeinsame Spalte oder mehr als zwei Tabellen miteinander verbinden.

Sehen wir uns nun an, wie diese verschiedenen JOINs zu unerwünschten Duplikaten führen können.

Was sind die Gründe für Duplikate in SQL-JOINs?

Es gibt viele mögliche Gründe für das Auftreten von Duplikaten im Ergebnis Ihrer SQL JOIN-Abfrage. Ich werde die 5 wichtigsten Gründe durchgehen; für jeden dieser Gründe zeige ich eine Beispielabfrage mit dem Problem und eine korrigierte Abfrage, um ein Ergebnis ohne Duplikate zu erhalten.

Lassen Sie uns zunächst kurz die Daten betrachten, die für unsere Beispiele verwendet werden sollen. Stellen Sie sich vor, wir betreiben ein Immobilienbüro, das irgendwo in den Vereinigten Staaten Häuser verkauft. Wir haben Tabellen mit agents, customers, und sales. Im Folgenden sehen Sie, welche Daten in den einzelnen Tabellen gespeichert sind.

Lassen Sie uns ohne weitere Verzögerung zu unseren Beispielen übergehen.

1. Fehlende ON-Bedingung

Anfänger, die mit SQL-JOINs nicht vertraut sind, listen oft einfach die Tabellen in FROM auf, ohne die Bedingung JOIN überhaupt anzugeben, wenn sie versuchen, Informationen aus zwei oder mehr Tabellen zu kombinieren. Dies ist eine gültige Syntax, so dass Sie keine Fehlermeldungen erhalten. Das Ergebnis ist jedoch eine Kreuzverknüpfung, bei der alle Zeilen aus einer Tabelle mit allen Zeilen aus einer anderen Tabelle kombiniert werden.

Nehmen wir zum Beispiel an, wir möchten Informationen über den Kunden erhalten, der ein bestimmtes Haus gekauft hat (ID #2134). Wenn wir die folgende Abfrage verwenden:

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

Das ist das Ergebnis, das wir erhalten:

Anstelle eines Datensatzes mit dem gewünschten Kunden werden alle Kunden in der Ergebnismenge aufgeführt.

Um die Abfrage zu korrigieren, benötigen Sie eine explizite JOIN Syntax. Die zu kombinierenden Tabellen werden in FROM und JOIN angegeben, und die Verknüpfungsbedingung wird in der Klausel ON angegeben:

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;

Hier geben wir die Kunden-ID aus der Tabelle sales Tabelle an, damit sie mit der Kunden-ID aus der customers Tabelle. So erhalten wir das gewünschte Ergebnis:

Sie könnten die Verknüpfungsbedingung in der WHERE -Klausel angeben, um das gleiche Ergebnis zu erhalten. Dies widerspricht jedoch der beabsichtigten Verwendung der WHERE-Klausel. Außerdem bietet die Verwendung der JOIN -Syntax zusätzliche Vorteile gegenüber der Auflistung der Tabellen in FROM. Lesen Sie diesen Artikel, um zu verstehen, warum die JOIN Syntax vorzuziehen ist.

2. Verwendung einer unvollständigen ON-Bedingung

Unerwünschte Zeilen in der Ergebnismenge können von unvollständigen ON-Bedingungen herrühren. In manchen Fällen müssen Sie Tabellen über mehrere Spalten verknüpfen. Wenn Sie in solchen Situationen nur ein Paar von Spalten verwenden, führt dies zu doppelten Zeilen.

Nehmen wir an, wir möchten die Erfahrung des Immobilienmaklers für jedes verkaufte Haus sehen. Wenn wir damit beginnen, die Tabellen sales und agents nach dem Nachnamen des Maklers:

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;

Das ist das, was Sie erhalten:

Das hat nicht gut funktioniert. Wir haben zwei verschiedene Makler mit dem Nachnamen Scott: Maria und Sophia. Infolgedessen sind die Häuser #1015 und #2134 jeweils zweimal mit verschiedenen Maklern enthalten.

Um diese Abfrage zu lösen, müssen wir die Tabellen sales und agents über zwei Spaltenpaare verbinden, die dem Nachnamen und dem Vornamen des Agenten entsprechen:

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;

Und hier ist das Ergebnis, nach dem wir gesucht haben.

Obwohl JOIN eines der grundlegenden Werkzeuge in SQL ist, müssen Sie sich der vielen verschiedenen Nuancen bewusst sein, um Tabellen effektiv zu verbinden. Ich empfehle, SQL-JOINs mit diesem interaktiven Kurs zu üben, der eine Vielzahl von Join-Szenarien mit 93 Codierungsaufgaben abdeckt.

3. Auswählen einer Teilmenge von Spalten

In manchen Fällen sind die Datensätze in der Ergebnismenge keine Duplikate, aber es sieht so aus, als ob sie Duplikate sind, weil die ausgewählte Teilmenge von Spalten nicht alle Unterschiede zwischen den Datensätzen zeigt.

Stellen Sie sich beispielsweise vor, Sie möchten die Daten der einzelnen Immobilienmakler sehen, die ein Haus verkauft haben. Wenn wir die folgende Abfrage verwenden:

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;

ergibt sich das folgende Ergebnis:

Die Ergebnismenge enthält zwei Datensätze mit Alexandr McGregor, die identisch erscheinen. Wenn Sie jedoch die Haus-ID zur Anweisung SELECT hinzufügen, sehen Sie, dass diese beiden Datensätze dem Verkauf von zwei verschiedenen Häusern am selben Tag entsprechen.

Wenn Sie an diesen zusätzlichen Informationen nicht interessiert sind und nur eine Zeile anzeigen lassen möchten, verwenden Sie 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;

Das Ergebnis lautet nun:

4. Nur übereinstimmende Zeilen auflisten

Ein ähnliches Problem kann auftreten, wenn Sie nur die Zeilen aus einer Tabelle auflisten wollen, es aber mehrere übereinstimmende Datensätze in der anderen Tabelle gibt. Sie erhalten dann unerwünschte Duplikate in Ihrer Ergebnismenge.

Nehmen wir zum Beispiel an, wir möchten alle Kunden auflisten, die über unsere Agentur Häuser gekauft haben. Wenn wir die folgende Abfrage verwenden:

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

Hier ist das Ergebnis:

Wie Sie sehen, enthält die resultierende Tabelle Ivan Lee zweimal. Das liegt daran, dass er zwei Häuser gekauft hat und es zwei entsprechende Datensätze in der sales Tabelle. Eine mögliche Lösung ist die Verwendung von DISTINCT wie im vorherigen Beispiel. Eine noch bessere Lösung besteht darin, SQL JOIN überhaupt nicht zu verwenden und die Ergebnismenge mit dem Schlüsselwort EXISTS zu filtern:

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

Jetzt ist das Ergebnis:

So erhalten Sie die gewünschte Ausgabe und die Absicht Ihrer Abfrage wird deutlicher.

5. Self Joins verwenden

Unerwünschte Duplikate in JOINs resultieren oft aus einer falschen Spezifikation der Join-Bedingungen in Self-Joins - das heißt, wenn eine Tabelle mit sich selbst verbunden wird.

Nehmen wir an, wir wollen, dass unsere Agenten Paare für unser nächstes Training bilden. Natürlich wollen wir nicht, dass ein Agent mit sich selbst gepaart wird. Wir könnten also die Bedingung ON a1.id <> a2.id angeben:

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;

Diese Abfrage gibt jedoch jedes Paar zweimal aus. In der ersten Zeile der Tabelle unten wird beispielsweise Kate White als Agentin 1 und Maria Scott als Agentin 2 betrachtet. Näher am Ende der Tabelle erhalten Sie jedoch dasselbe Agentenpaar, jedoch mit Maria Scott als Agentin 1 und Kate White als Agentin 2.

Um dieses Problem zu lösen, müssen Sie eine explizite Bedingung hinzufügen, um jedes Paar nur einmal zu berücksichtigen. Eine gängige Lösung ist die Angabe der Verknüpfungsbedingung a1.id < a2.id. Auf diese Weise erhalten Sie das Paar Kate White und Maria Scott, aber nicht umgekehrt. Der Grund dafür ist, dass Kates ID (1) eine niedrigere Zahl ist als Marias ID (6).

In der Praxis kann es sein, dass Sie andere Bedingungen für die Paarung der Bearbeiter haben. Zum Beispiel können Sie erfahrenere Bearbeiter (3+ Jahre) mit weniger erfahrenen Bearbeitern (< 3 Jahre) zusammenbringen. Die entsprechende Filterungsbedingung in WHERE löst das Problem:

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;

Hier ist das Ergebnis:

Diese Ergebnismenge sieht viel besser aus und erleichtert die Auswahl von drei Paaren, die jeweils aus einem Agenten mit mehr Erfahrung und einem anderen mit weniger Erfahrung bestehen.

Üben wir SQL-JOINs!

Das Verknüpfen von Tabellen in SQL ist gar nicht so schwierig. Aber es erfordert eine Menge Übung. Wenn Sie Fallstricke wie unerwünschte Duplikate in JOINs und fehlende Datensätze vermeiden wollen, folgen Sie diesem Leitfaden zum Üben SQL-JOINs.

Wenn Sie nur grundlegende Erfahrungen mit SQL haben und Daten aus mehreren Tabellen sicherer kombinieren möchten, empfehle ich diesen SQL-JOINs interaktiven Kurs. Er behandelt alle wichtigen JOIN-Typen sowie das Verbinden einer Tabelle mit sich selbst, das Verbinden mehrerer Tabellen in einer Abfrage und das Verbinden von Tabellen über Nicht-Schlüsselspalten. Weitere Einzelheiten zu diesem Kurs finden Sie in diesem Übersichtsartikel.

Bonus. Hier finden Sie die 10 besten SQL JOIN Interviewfragen mit Antworten.

Vielen Dank fürs Lesen und viel Spaß beim Lernen!