Intersect-Operator in MySQL

INTERSECT gibt Zeilen zurück, die in beiden Abfrageergebnissen vorkommen. Um es in MySQL zu verwenden, prüfen Sie zunächst, ob Ihr installierter Server die native Syntax akzeptiert. Andernfalls können Sie die Überschneidung mit einer Abfrage über INNER JOIN, IN oder EXISTS ermitteln. Wählen Sie das Muster anhand der benötigten Spalten und danach, ob doppelte Treffer erscheinen sollen.

So kombiniert INTERSECT Ergebnismengen

Betrachten Sie jedes SELECT als eine Menge von Zeilen. INTERSECT behält die Zeilen, die in beiden Mengen vorkommen, statt alle Zeilen aus einer der beiden Mengen zurückzugeben. Das Diagramm hebt diesen gemeinsamen Bereich hervor:

Werden beispielsweise Kunden-IDs aus einer Kundenliste und einer Bestellliste miteinander geschnitten, bleiben die Kunden übrig, die in beiden Ergebnissen vorkommen. Das ist eine Mengenoperation auf den ausgewählten Werten. Ein Join eignet sich als Nachbildung, wenn seine Join-Bedingung genau die Werte abgleicht, die eine Zeile in der Schnittmenge definieren. Wählen Sie unterschiedliche Spalten aus, verbinden Sie zusammengehörige Datensätze, statt eine Schnittmenge identischer Zeilen nachzubilden.

Unterstützung für INTERSECT in Ihrer MySQL-Version prüfen

Ob die Syntax unterstützt wird, hängt von der tatsächlich verwendeten Serverversion ab. Verlassen Sie sich daher weder auf eine pauschale Aussage noch auf eine angenommene Versionsgrenze. Schlagen Sie in der Dokumentation der installierten Version nach oder testen Sie in einer Entwicklungsumgebung eine minimale Abfrage wie SELECT 1 INTERSECT SELECT 1;. Lehnt der Server die Syntax ab, verwenden Sie eines der folgenden Muster. Die SQL-Ausschnitte sind Syntaxbeispiele; sie benötigen einen MySQL-Server und wurden in der bereitgestellten Laufzeitumgebung nicht ausgeführt.

Beispieltabellen vorbereiten

Die Beispiele verwenden Kunden und ihre Bestellungen. Nennen Sie die Tabelle orders: ORDER ist ein SQL-Schlüsselwort, daher kann seine Verwendung als unquotierter Tabellenname zu Fehlern führen. Erstellen Sie customer mit CREATE TABLE customer (customer_id INT PRIMARY KEY, customer_firstname VARCHAR(60) NOT NULL, customer_lastname VARCHAR(60) NOT NULL, customer_age INT NOT NULL, customer_salary INT NOT NULL);. Erstellen Sie orders mit CREATE TABLE orders (order_id INT PRIMARY KEY, order_date DATETIME NOT NULL, order_amount INT NOT NULL, customer_id INT NOT NULL, FOREIGN KEY (customer_id) REFERENCES customer(customer_id));.

Fügen Sie die Beispielkunden mit INSERT INTO customer VALUES (1, 'Shajeel', 'Daniel', 23, 9000), (2, 'Nayya', 'Preston', 54, 1500), (3, 'James', 'Robert', 36, 2500), (4, 'Jennifer', 'John', 29, 5000), (5, 'Sarah', 'Paul', 15, 8000), (6, 'Karen', 'Donald', 40, 3500); ein. Fügen Sie anschließend die Bestellungen mit INSERT INTO orders VALUES (1, '2019-12-03 10:25:30', 500, 2), (2, '2019-12-10 12:00:30', 1500, 4); ein. Die Spalten der Bestelltabelle stehen dabei in dieser Reihenfolge: order_id, order_date, order_amount, customer_id.

INTERSECT mit INNER JOIN, IN und EXISTS nachbilden

Zur Veranschaulichung einer Mengenüberschneidung behält dieses eigenständige Python-Beispiel die IDs bei, die in beiden Eingabeergebnissen vorkommen. Das Python-Beispiel wurde ausgeführt; es ist jedoch keine MySQL-Abfrage. Die Ausgabe wird unten gezeigt. Die Bezeichnungen im unveränderten Code- und Ausgabeblock bleiben auf Englisch. Die SQL-Ausschnitte sind Syntaxbeispiele und wurden in der bereitgestellten Laufzeitumgebung nicht ausgeführt. Führen Sie dieses Beispiel aus:

customers = {1, 2, 3, 4}
ordered_customers = {2, 4, 6}
print(sorted(customers & ordered_customers))

[2, 4]

Verwenden Sie INNER JOIN, wenn Sie Spalten aus beiden Tabellen benötigen. Die folgende Syntax gibt Bestelldetails zusammen mit den passenden Kundendetails zurück: SELECT o.order_id, c.customer_id, c.customer_firstname, c.customer_lastname, o.order_amount, o.order_date FROM orders AS o INNER JOIN customer AS c ON o.customer_id = c.customer_id;. In den Beispieldaten stimmen die Kunden-IDs 2 und 4 überein. Hat ein Kunde mehrere Bestellungen, liefert dieser Join eine Zeile pro passender Bestellung. Er verhält sich damit nicht automatisch wie eine Mengenoperation, die höchstens eine Zeile pro Kunde zurückgibt.

Verwenden Sie IN, wenn das Ergebnis Kundenspalten enthalten und lediglich die Zugehörigkeit zur anderen Ergebnismenge prüfen soll: SELECT c.customer_id, c.customer_firstname, c.customer_lastname, c.customer_age FROM customer AS c WHERE c.customer_id IN (SELECT o.customer_id FROM orders AS o);. Die Kunden 2 und 4 erscheinen jeweils einmal, selbst wenn einer von ihnen mehrere Bestellungen hat, denn IN prüft, ob ein passender Wert vorhanden ist.

Verwenden Sie EXISTS für dieselbe Zugehörigkeitsprüfung, wenn Sie außerdem eine Bedingung für den Kundendatensatz festlegen möchten: SELECT c.customer_id, c.customer_firstname, c.customer_lastname, c.customer_age FROM customer AS c WHERE c.customer_age < 45 AND EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = c.customer_id);. Mit diesen Beispieldaten wird Kunde 4 zurückgegeben; Kunde 2 ist 54 Jahre alt. EXISTS formuliert die Prüfung direkt und vervielfacht einen Kundendatensatz nicht, wenn mehrere Bestellungen passen.

Das passende Muster zur Nachbildung auswählen

Wählen Sie INNER JOIN, wenn die Ausgabe Spalten von beiden Seiten enthalten soll oder jedes passende Wertepaar relevant ist. IN eignet sich für einen knappen Zugehörigkeitsfilter, dessen Ausgabe aus einer Tabelle stammt. Verwenden Sie EXISTS, wenn sich die Bedingung am besten als korrelierte Prüfung formulieren lässt, insbesondere zusammen mit weiteren Filtern. Sind die Join-Schlüssel nicht eindeutig, kann ein Join wiederholte Zeilen zurückgeben. Prüfen Sie die Zugehörigkeit dann mit IN oder EXISTS, oder verwenden Sie gezielt DISTINCT, wenn die projizierten Zeilen eindeutig sein müssen. Vergleichen Sie die ausgewählten Spalten und das Verhalten bei Duplikaten mit dem erwarteten Ergebnis von INTERSECT, bevor Sie die Abfrage übernehmen.