Wie behält man nicht übereinstimmende Zeilen aus zwei Tabellen in einem SQL JOIN

Möchten Sie zwei Tabellen in SQL verbinden, ohne nicht übereinstimmende Zeilen zu entfernen? Möchten Sie nicht übereinstimmende Zeilen aus einer oder beiden Tabellen behalten? In diesem Artikel erkläre ich Ihnen, wie Sie mit Hilfe von Outer JOINs, wie LEFT JOIN, RIGHT JOIN und FULL JOIN, alle gewünschten Datensätze behalten können. Beispiele inklusive!

Der SQL JOIN ist ein leistungsfähiges Werkzeug, mit dem Sie Daten aus mehreren Tabellen Ihrer Datenbank kombinieren können. Dies ist ein Kerngedanke relationaler Datenbanken - das Speichern von Daten in verschiedenen, miteinander verbundenen Tabellen und das Kombinieren von Daten aus diesen Tabellen, wenn sie für die Datenanalyse und Berichterstattung benötigt werden. Wenn Sie einen Überblick über die Verknüpfung von Tabellen in SQL benötigen, sehen Sie sich diese anfängerfreundliche Anleitung und unseren interaktiven SQL-JOINs Kurs.

Leider können JOIN-Abfragen zu frustrierenden Ergebnissen führen, wenn Sie gerade erst mit SQL beginnen. Zum Beispiel können Sie Duplikate, fehlende Daten, unerwartete NULL-Werte usw. erhalten. In diesem Artikel möchte ich mich auf den Fall konzentrieren, in dem Sie nicht übereinstimmende Zeilen aus zwei Tabellen behalten wollen, aber ein SQL JOIN diese entfernt. Wir werden sehen, warum das passiert und wie Sie die erwartete Ausgabe erhalten.

Lassen Sie uns mit einem Beispiel beginnen.

Angenommen, Sie planen eine Marketingkampagne, bei der Sie einer Reihe von Kunden einen besonderen Bonus gewähren. Es gibt mehrere Kriterien, damit ein Kunde für den Bonus in Frage kommt. Für diese spezielle Kampagne suchen Sie nach Kunden, die im letzten Monat Aufträge mit dem Status "Abgeschlossen" hatten, aber nicht an Ihrer vorherigen Marketingkampagne teilgenommen haben.

Sie möchten Informationen aus den folgenden drei Tabellen kombinieren:

Sie können die folgende Abfrage verwenden, um diese Tabellen zu verknüpfen und eine Liste von Kunden mit den zusätzlichen Informationen zu erhalten, die Sie benötigen:

SELECT 
  c.id, 
  c.first_name, 
  c.last_name, 
  o.order_status, 
  lcp.winner
FROM customers c
JOIN orders o
ON c.id = o.customer_id
JOIN last_campaign_participants lcp
ON c.id = lcp.customer_id;

Wenn Sie sich nicht sicher sind, wie diese Abfrage funktioniert, sehen Sie sich unseren interaktiven Kurs mit 93 Programmieraufgaben an, die verschiedene Arten von JOINs abdecken.

Hier sehen Sie die Ausgabe der obigen SQL-Abfrage:

Wie Sie sehen, enthält die Ausgabe nur zwei von vier Kunden. Das liegt daran, dass nur diese beiden Kunden entsprechende Datensätze in allen drei Tabellen haben, und JOIN oder INNER JOIN gibt nur die übereinstimmenden Zeilen aus. Mit dieser Ausgabe sehen wir nur einen geeigneten Kunden für die nächste Kampagne - Kate Wilson (ID 101). Der andere war der Gewinner unserer letzten Kampagne und kommt daher für diese Kampagne nicht in Frage.

Aber können wir jemanden übersehen, wenn wir die nicht übereinstimmenden Zeilen entfernen? Eigentlich ja. Wir können Kunden übersehen, die zwar Bestellungen abgeschlossen haben, aber an der letzten Kampagne gar nicht teilgenommen haben. Diese Kunden fehlen in dieser Tabelle. Wir wollen alle Kunden in der Ausgabe des SQL-JOINs haben und dann die Ergebnisse nach Bedarf filtern.

Glücklicherweise gibt es in SQL äußere JOINs, mit denen Sie nicht übereinstimmende Zeilen aus zwei Tabellen behalten können.

Inner JOINs vs. Outer JOINs

Im Gegensatz zu INNER JOIN oder JOIN, die nur übereinstimmende Zeilen aus zwei Tabellen zurückgeben, geben äußere JOINs in SQL auch die nicht übereinstimmenden Zeilen zurück. Es gibt mehrere Arten von Outer JOINs:

• Ein LEFT JOIN gibt alle Datensätze aus der linken (ersten) Tabelle zurück, auch wenn es keine Übereinstimmungen in der rechten (zweiten) Tabelle gibt.
• Ein RIGHT JOIN gibt alle Datensätze aus der rechten (zweiten) Tabelle zurück, auch wenn es keine Übereinstimmungen in der linken (ersten) Tabelle gibt.
• Ein FULL JOIN gibt alle Datensätze aus beiden Tabellen zurück, einschließlich der nicht übereinstimmenden Datensätze aus beiden Tabellen.

In diesem Artikel werden diese SQL JOIN-Typen anhand von Beispielen und Abbildungen näher erläutert. Sehen Sie sich auch dieses großartige SQL-JOINs Cheat Sheet an, um alle Feinheiten der Syntax zu erfahren.

Um einen äußeren JOIN zu verwenden, ersetzen Sie einfach das Schlüsselwort JOIN durch das Schlüsselwort LEFT JOIN, RIGHT JOIN oder FULL JOIN, je nach Fall. Beachten Sie jedoch, dass es bei LEFT JOIN und RIGHT JOIN auf die Reihenfolge der Tabellen in der SQL-Abfrage ankommt.

Lassen Sie uns nun unsere erste Abfrage so ändern, dass nicht zugeordnete Zeilen erhalten bleiben und alle Kunden in der Ausgabe erscheinen. Eine Möglichkeit ist die Verwendung von LEFT JOIN, wobei sichergestellt werden muss, dass die customers Tabelle in der Abfrage an erster Stelle steht (d. h. direkt nach dem Schlüsselwort FROM ):

SELECT 
  c.id, 
  c.first_name, 
  c.last_name, 
  o.order_status, 
  lcp.winner
FROM customers c
LEFT JOIN orders o
ON c.id = o.customer_id
LEFT JOIN last_campaign_participants lcp
ON c.id = lcp.customer_id;

Die Ausgabe dieser Abfrage enthält eine Liste aller aktuellen Kunden mit den entsprechenden Zusatzinformationen aus den beiden anderen Tabellen. Beachten Sie, dass, wenn keine Datensätze mit dem Kunden aus der linken Tabelle übereinstimmen, diese Datensätze trotzdem enthalten sind, aber mit NULL Werten in den entsprechenden Spalten:

Indem wir die nicht übereinstimmenden Zeilen mit LEFT JOIN behalten, finden wir einen weiteren Kunden, der für die Kampagne in Frage kommt. Konkret hat Maria White (ID 102) Bestellungen abgeschlossen und nicht an der letzten Kampagne teilgenommen - also offensichtlich nicht gewonnen.

Um äußere JOINs besser zu verstehen, sehen wir uns einige weitere Beispiele an.

Outer JOIN Beispiele

Nehmen wir an, wir betreiben eine Buchhandlung. Wir möchten zwei Gruppen von Kunden vergleichen: diejenigen, die Harry Potter und der Stein der Weisen gekauft haben, und diejenigen, die Harry Potter und die Kammer des Schreckens gekauft haben. Sind das dieselben Kunden? Gibt es jemanden, der das eine Buch gekauft hat, das andere aber nicht? Vielleicht sollten wir ihnen das andere Buch empfehlen.

Beispiel mit LEFT JOIN

Zunächst möchten wir alle Personen anzeigen, die Harry Potter und der Stein der Weisen bei uns gekauft haben, und prüfen, ob sie auch Harry Potter und die Kammer des Schreckens gekauft haben.

Um diese Ausgabe zu erhalten, verwenden wir LEFT JOIN mit einer Liste derjenigen, die das erste Buch gekauft haben, das in der FROM Klausel der Abfrage enthalten ist (linke Tabelle).

SELECT 
  ps.product_id, 
  ps.first_name, 
  ps.last_name, 
  cs.product_id, 
  cs.first_name, 
  cs.last_name
FROM philosophers_stone ps
LEFT JOIN chamber_of_secrets cs
ON ps.customer_id = cs.customer_id;

Auf diese Weise können wir alle Käufer von Harry Potter und der Stein der Weisen behalten, auch wenn es keine übereinstimmenden Datensätze in der zweiten Tabelle gibt:

Jetzt sehen wir, dass zwei Kunden das erste Buch der Reihe gekauft haben, aber nicht das zweite. Wir können ihnen das zweite Buch in unserer nächsten Marketingkampagne empfehlen.

Beispiel mit FULL JOIN

Was aber, wenn es Kunden gibt, die nur das zweite Buch gekauft haben, aber nicht das erste? Auch das wäre gut zu wissen.

Anstatt eine separate Abfrage zu schreiben, um diese Gruppe von Kunden zu erkennen, ist es besser, FULL JOIN zu verwenden, die uns das gesamte Bild liefert: diejenigen, die nur das erste Buch gekauft haben, diejenigen, die nur das zweite Buch gekauft haben, und diejenigen, die beide Bücher gekauft haben:

SELECT 
  ps.product_id, 
  ps.first_name, 
  ps.last_name, 
  cs.product_id, 
  cs.first_name, 
  cs.last_name
FROM philosophers_stone ps
FULL JOIN chamber_of_secrets cs
ON ps.customer_id = cs.customer_id;

Sieht toll aus! Jetzt können wir ganz einfach zwei Gruppen von Kunden miteinander vergleichen und Möglichkeiten für gute Buchempfehlungen finden. Äußere JOINs können sehr praktisch sein!

Zeit zum Üben SQL-JOINs!

Das Verbinden von Tabellen ist eine der grundlegenden Fähigkeiten, die für die effektive Nutzung von SQL bei der Datenanalyse und Berichterstellung erforderlich sind. Es ist gar nicht so schwierig, aber Sie brauchen viel Übung mit SQL-JOINs, um Fallstricke wie fehlende Datensätze und unerwartete Duplikate zu vermeiden.

Ich empfehle, mit dem SQL-JOINs interaktiven Kurs zu beginnen. Er deckt alle wichtigen Arten von JOINs ab, 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 sind die 10 besten SQL JOIN-Interview-Fragen mit Antworten.

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