Praktische Beispiele für die Verwendung von Nicht-Gleichheits-JOINs in SQL

Wenn Sie denken, dass zwei Tabellen in SQL nur über das gemeinsame Feld verbunden werden können, werden Sie begeistert sein zu erfahren, dass es noch andere Möglichkeiten der Verbindung gibt. Non-Equi JOINs verwenden verschiedene Arten von Vergleichsoperatoren in der JOIN-Bedingung. In diesem Artikel führe ich Sie durch Beispiele für nicht-äqui JOINs, um die häufigsten Anwendungsfälle für diese Art von JOINs zu zeigen.

Äqui JOIN vs. Nicht-Äqui JOIN

Wie verbinden Sie normalerweise zwei Tabellen in SQL? Höchstwahrscheinlich wählen Sie das gemeinsame Feld in diesen beiden Tabellen aus und verknüpfen sie mit dem Gleichheitszeichen in der Verknüpfungsbedingung. Sie können zum Beispiel die Produkt-ID aus der Produkttabelle mit der Produkt-ID aus der Bestelltabelle oder den Nachnamen aus der Mitarbeitertabelle mit dem Nachnamen aus dem Stundenzettel abgleichen.

In diesen Fällen verwenden Sie einen equi JOIN, was nur ein schicker Name für eine Verknüpfung mit einem Gleichheitszeichen in der Verknüpfungsbedingung ist. Viele SQL-Anfänger verwenden äqui JOINs und wissen nicht einmal, dass man eine ungleiche Bedingung in einem JOIN verwenden kann.

Solche Joins nennt man non-equi JOINs und sie sind auch in SQL möglich. Wenn Sie zwei Tabellen mit anderen bedingten Operatoren als dem Gleichheitszeichen verknüpfen, kommen Nicht-Gleichheits-JOINs ins Spiel. Vergleichsoperatoren wie <, >, <=, >=, != und <> sowie der Operator BETWEEN eignen sich hervorragend für die Verknüpfung von Tabellen in SQL.

In dieser illustrierten Anleitung zum SQL non-equi JOIN erfahren Sie, wie er funktioniert.

Kennenlernen der Daten

Bevor wir uns mit verschiedenen Beispielen für Non-equi JOINs beschäftigen, sollten wir uns zunächst die Daten ansehen, die wir in diesem Artikel untersuchen werden. Wir werden einige Datenanalysen für eine Immobilienagentur durchführen, die ein Vermietungsgeschäft betreibt. Wir haben drei Tabellen in unserer imaginären Datenbank:

• houses mit der Haus-ID, dem Bezirk, der Adresse, der Anzahl der Zimmer und der Miete
• renters mit der Mieter-ID, dem Namen, dem bevorzugten Bezirk, der Mindestanzahl der benötigten Schlafzimmer und der akzeptablen Mietspanne
• deals mit der Geschäfts-ID, dem Datum, der Mieter-ID, der Haus-ID und der Maklergebühr aus dem entsprechenden Geschäft

Siehe diese drei Tabellen unten.

Houses

Renters

Deals

Jetzt sind wir bereit, zu den Nicht-Equi JOIN-Beispielen überzugehen.

Anwendungsfälle für Non-Equi JOINs

Wenn Sie noch nie Non-Equi JOINs verwendet haben, fragen Sie sich vielleicht, was die üblichen Szenarien für die Anwendung dieses Nicht-Standardtyps von JOIN sind. In der Tat gibt es eine ganze Reihe von Fällen. Sie können Non-Equi JOINs verwenden, um alle (eindeutigen) Paare von Elementen aufzulisten, Duplikate zu identifizieren, Elemente innerhalb eines bestimmten Wertebereichs oder zwischen bestimmten Daten aufzulisten, laufende Summen zu berechnen und vieles mehr.

Wir beginnen mit den häufigsten Anwendungsfällen für Non-equi JOINs.

Auflistung von Paarkombinationen

Nehmen wir an, unsere Mieter sind bereit, ein Haus mit einer anderen Familie zu teilen. Daher möchten wir alle möglichen Paare unserer Mieter zusammen mit ihrem bevorzugten Bezirk auflisten, um zu sehen, welche Mieter möglicherweise ein Haus zusammen mieten könnten. Hier ist die SQL-Abfrage, die Sie verwenden können:

SELECT r1.name, r1.preferred_district, r2.name, r2.preferred_district
FROM renters r1
JOIN renters r2
ON r1.preferred_district = r2.preferred_district AND r1.id != r2.id;

Wie Sie in diesem Beispiel sehen können, haben wir die Tabelle renters mit sich selbst verknüpft. Wir haben zwei Bedingungen mit unterschiedlichen Vergleichsoperatoren in unserer JOIN-Anweisung verwendet:

• Das standardmäßige Gleichheitszeichen stellt sicher, dass wir nur Kunden mit demselben bevorzugten Bezirk zusammenführen.
• Der Vergleichsoperator != stellt sicher, dass das Ergebnis alle möglichen Paare von Mietern enthält, außer der Paarung der Mieter mit sich selbst

Die zweite Bedingung mit dem Operator != macht diesen JOIN zu einem Nicht-Gleichheits-JOIN.

Das Ergebnis sieht ziemlich gut aus, außer dass wir dieselben Mieterpaare zweimal in unserer Tabelle haben. Es wäre sinnvoller, nur eindeutige Paare in unserem Ergebnis zu haben. Diese sehr kleine Änderung unserer Abfrage ist ein nützlicher Trick:

SELECT r1.name, r1.preferred_district, r2.name, r2.preferred_district
FROM renters r1
JOIN renters r2
ON r1.preferred_district = r2.preferred_district AND r1.id < r2.id;

Indem wir den Vergleichsoperator in der zweiten Verknüpfungsbedingung von != auf < ändern, listen wir nur die Paare auf, bei denen der ID-Wert des ersten Mieters kleiner ist als der ID-Wert des zweiten Mieters. So haben wir jetzt nur die Zeile, in der Michael Lane (ID 2) in der ersten Spalte und Susan Sanders (ID 3) in der dritten Spalte aufgeführt sind, und nicht die Zeile, in der Susan Sanders an erster Stelle steht.

Um alle (eindeutigen) Paare von Kunden aufzulisten, haben wir die Tabelle renters mit sich selbst verknüpft, was im Grunde ein self JOIN ist. Weitere Informationen zu Self JOINs finden Sie in diesem umfassenden Leitfaden mit leicht verständlichen Beispielen.

Identifizieren von Duplikaten

Eine weitere häufige Anwendung von Nicht-Equi JOINs ist das Auffinden von Duplikaten in einem Datensatz. Nehmen wir zum Beispiel an, wir möchten überprüfen, ob unsere Häusertabelle Duplikate enthält, d. h. Häuser mit der gleichen Adresse, aber unterschiedlichen IDs.

Unsere Abfrage ist derjenigen sehr ähnlich, die wir für die Auflistung eindeutiger Paare verwendet haben, aber dieses Mal werden wir die Tabelle "Häuser" selbst verknüpfen:

SELECT h1.id, h1.address, h2.id, h2.address
FROM houses h1
JOIN houses h2
ON h1.address = h2.address AND h1.id < h2.id;

Wir haben wieder zwei Join-Bedingungen: (1) um zu prüfen, ob die Adresse die gleiche ist, und (2) um sicherzustellen, dass wir nur eindeutige Paare mit unterschiedlichen IDs auflisten.

Die obige Tabelle zeigt, dass es in unserem Datensatz ein Duplikat gibt. Das Haus in der Rose Street 5 wird in der Tabelle zwei Mal erwähnt, mit der ID 1 und der ID 3.

Verbinden von Tabellen über einen Bereich von Werten

Andere beliebte Anwendungen von nicht-äquivalenten JOINs sind das Verbinden von zwei Tabellen mit:

• den Operator BETWEEN, um zu prüfen, ob ein bestimmter Wert/Datum in einen bestimmten Bereich fällt
• Vergleichsoperatoren wie >= oder <= zur Überprüfung der Kapazität

Um zu sehen, wie diese non-equi JOINs in der Praxis funktionieren, wollen wir andere Häuser auflisten, die wir unseren Mietern als Alternative vorschlagen können. Dabei sollte es sich um Häuser handeln, die (1) in ihrem bevorzugten Bezirk liegen, (2) in ihrer Preisspanne liegen, (3) die gewünschte Anzahl von Schlafzimmern haben und (4) nicht belegt sind (d. h. nicht in unserer Angebotstabelle aufgeführt sind). Hier ist die SQL-Abfrage, die wir verwenden können:

SELECT r.id, r.name, h.id, h.address, h.rent, h.bedrooms
FROM renters r
JOIN houses h
ON h.district = r.preferred_district
    AND h.rent BETWEEN r.min_rent AND r.max_rent
    AND h.bedrooms >= r.min_bedrooms
WHERE h.id NOT IN (SELECT house_id FROM deals);

In der Bedingung JOIN haben wir die ersten drei oben genannten Bedingungen überprüft:

• ob der Bezirk des Hauses mit dem bevorzugten Bezirk des Mieters übereinstimmt (Gleichheitsbedingung)
• ob die Miete innerhalb des vom Mieter akzeptierten Bereichs liegt (Nicht-Gleichheitsbedingung mit Wertebereich)
• ob die Anzahl der Schlafzimmer den Mindestanforderungen entspricht (Nicht-Gleichheitsbedingung zur Überprüfung der Kapazität)

Dann haben wir in der WHERE-Anweisung eine Unterabfrage verwendet, um nur die Häuser zu filtern, die vermutlich frei sind, d. h. noch nicht in unserer Tabelle deals.

Und hier ist die Liste der Häuser, die wir unseren Kunden vorschlagen können (beachten Sie, dass das Haus mit der ID 3 nur ein Duplikat des Hauses ist, das dieser Kunde jetzt mietet):

Mehr Übung mit diesen Arten von SQL non-equi JOINs erhalten Sie in unserem umfassenden Kurs über SQL-JOINs.

Erweiterte Szenarien für die Verwendung von Non-Equi JOINs

Zusätzlich zu den oben erwähnten allgemeinen Anwendungen von Nicht-Equi JOINs gibt es einige fortgeschrittenere Szenarien für die Anwendung dieser Arten von JOINs. Lassen Sie uns in zwei Beispiele eintauchen.

Berechnen von laufenden Summen

Ein nicht-gleichheitsbezogener JOIN kann verwendet werden, um eine laufende Summe einer bestimmten Spalte zu berechnen. Nehmen wir zum Beispiel an, dass wir nach jedem abgeschlossenen Geschäft die gesamte bisher erhaltene Vermittlungsgebühr wissen möchten. Hier ist die SQL-Abfrage, die wir verwenden können:

SELECT d1.date, d1.agent_fee, SUM(d2.agent_fee) AS total_agent_fee
FROM deals d1
JOIN deals d2
ON d1.date >= d2.date
GROUP BY d1.agent_fee, d1.date
ORDER BY d1.date;

Wir haben die Tabelle deals mit einem Self-Join verknüpft und eine Non-Equi JOIN-Bedingung verwendet, um alle bis zum Datum des Geschäftsabschlusses erhaltenen Maklergebühren zu summieren. Hier ist das Ergebnis.

Bitte beachten Sie, dass eine bessere Methode zur Berechnung einer laufenden Summe die Verwendung von Fensterfunktionen ist. Unser entsprechender Leitfaden erklärt, was eine laufende Summe ist und wie man eine SQL-Abfrage schreibt, um sie zu berechnen. Zum Üben besuchen Sie den interaktiven Kurs LearnSQL.de Fensterfunktionen.

Lösen von Konflikten zwischen LEFT JOINs und WHERE

Non-equi JOINs können auch in einigen Situationen nützlich sein, wenn LEFT JOIN in Kombination mit der WHERE -Anweisung nicht wie vorgesehen funktioniert. Insbesondere ist es oft der Fall, dass die WHERE Bedingung die LEFT JOIN"aufhebt" und stattdessen als INNER JOIN funktioniert.

Nehmen wir zum Beispiel an, wir möchten alle Häuser aus unserer Datenbank zusammen mit dem Datum des entsprechenden Geschäfts auflisten, falls es stattgefunden hat. Wir wollen auch nur die Geschäfte berücksichtigen, die nach dem 1. März stattgefunden haben.

Da wir daran interessiert sind, alle Häuser aufzulisten, unabhängig davon, ob sie ein passendes Geschäft haben, werden wir eine LEFT JOIN in unserer SQL-Abfrage verwenden. Wir fügen auch eine WHERE Bedingung hinzu, um nur Geschäfte zu berücksichtigen, die nach einem bestimmten Datum abgeschlossen wurden:

SELECT h.id, h.address, d.date
FROM houses h
LEFT JOIN deals d
ON h.id = d.house_id
WHERE d.date >= '2020-03-01';

Wie Sie sehen können, ist das Ergebnis nicht genau das, was wir wollten. Die Tabelle enthält nur die Häuser, die nach dem 1. März vermietet wurden, und nicht alle Häuser.

Eine Lösung besteht darin, die Bedingung WHERE auf ON zu setzen und sie zu einer Bedingung JOIN zu machen. Dies ist ein Non-equi JOIN, da ein >= Vergleichsoperator verwendet wird.

SELECT h.id, h.address, d.date
FROM houses h
LEFT JOIN deals d
ON h.id = d.house_id AND d.date >= '2020-03-01';

Jetzt werden alle Häuser im Ergebnis angezeigt, auch wenn sie keine übereinstimmenden Angebote haben.

Zeit zum Üben von Non-Equi JOINs!

Sie haben gelernt, dass Non-Equi JOINs in verschiedenen Szenarien sehr hilfreich sein können. Sie können eindeutige Paare in einer Tabelle auflisten, Duplikate identifizieren, Tabellen mit einem Werte- und Datumsbereich verbinden, laufende Summen ohne Fensterfunktionen berechnen und vieles mehr.

Wie übt man nun am besten SQL-JOINs? Auf jeden Fall, indem man Code schreibt. Machen wir also weiter mit den interaktiven Übungen!

Der LearnSQL.de SQL-JOINs werden verschiedene JOIN-Typen behandelt, einschließlich nicht-gleichnamiger JOINs. Sie werden die Möglichkeit haben, Non-Equi JOINs in verschiedenen Anwendungsfällen zu üben. Darüber hinaus bietet der umfassende SQL für Anfänger Darüber hinaus bietet der umfassende Kurs zusätzliche Übungen zu Non-equi JOINs, indem er die Abfrage von mehr als einer Tabelle erklärt und tiefer gehende Themen zu JOINs behandelt.

Viel Spaß beim Lernen!