MySQL Unterabfrage mit Beispielen

Was ist eine Unterabfrage in SQL?

A Unterabfrage Eine innere SELECT-Abfrage ist in einer anderen Abfrage enthalten. Sie dient üblicherweise dazu, das Ergebnis der äußeren Abfrage zu ermitteln. Daher wertet die Datenbank die innere Abfrage zuerst aus und gibt deren Ergebnis anschließend an die äußere Abfrage weiter.

Die innere Abfrage wird als die innere Abfrage oder verschachtelte Abfrage, und die Anweisung, die sie enthält, wird als die äußere AbfrageSchauen wir uns die Syntax der Unterabfrage an.

Das obige Diagramm zeigt die allgemeine Struktur der Anweisung: Die äußere SELECT-Anweisung gibt die Spalten an, die angezeigt werden sollen, und die in Klammern gesetzte innere SELECT-Anweisung gibt den Wert oder die Liste der Werte an, mit denen die WHERE-Klausel vergleicht.

Warum eine Unterabfrage verwenden?

Bevor wir uns die verschiedenen Typen ansehen, ist es hilfreich zu wissen, wann eine Unterabfrage in einer Anweisung ihren Platz verdient.

Eine Unterabfrage beantwortet eine Frage, deren Filterwert nicht im Voraus bekannt ist. Er muss aus den Daten selbst berechnet werden. Ein häufiger Kritikpunkt von Kunden der MyFlix-Videothek ist die geringe Anzahl an Filmtiteln. Das Management möchte Filme für die Kategorie mit den wenigsten Titeln kaufen. Da niemand weiß, welche Kategorie das ist, bis die Datenbank abgefragt wird, muss der Wert zunächst berechnet und dann als Filter verwendet werden.

Unterabfragen befinden sich beitractive aus drei praktischen Gründen:

• Ablesbarkeit: Jeder Teil der Logik steht in einem eigenen geklammerten Block, sodass sich die Aussage eher wie eine Folge kleiner Fragen als wie ein komplexer Ausdruck liest.
• Isolationswerte: Eine innere Abfrage kann separat ausgeführt werden, um zu bestätigen, dass sie den erwarteten Wert zurückgibt, was das Testen und Debuggen erheblich vereinfacht.
• Flexibilität: Das gleiche Muster funktioniert in den WHERE-, HAVING-, SELECT- und FROM-Klauseln sowie innerhalb der INSERT-, UPDATE- und DELETE-Anweisungen.

Der Kompromiss besteht in der Geschwindigkeit, die im späteren Verlauf dieses Artikels im JOIN-Vergleich untersucht wird.

Arten von Unterabfragen in MySQL

MySQL Es werden drei Arten von Unterabfragen unterstützt, wobei die Art des Ergebnisses der inneren Abfrage bestimmt wird. Jede Art wird im Folgenden anhand eines Beispiels mit der Datenbank myflixdb erläutert.

1) Skalare Unterabfrage

A Skalare Unterabfrage Gibt genau eine Zeile und eine Spalte zurück, also einen einzelnen Wert. Da das Ergebnis ein einzelner Wert ist, kann es überall dort verwendet werden, wo Literalwerte zulässig sind. Um auf das oben genannte MyFlix-Problem zurückzukommen: Sie können eine Abfrage wie diese verwenden:

SELECT category_name FROM categories
WHERE category_id = (SELECT MIN(category_id) FROM movies);

Es liefert folgendes Ergebnis:

Mal sehen, wie diese Abfrage funktioniert.

Wie das Ausführungsdiagramm zeigt, MySQL erste Läufe SELECT MIN(category_id) FROM moviesDie Funktion empfängt einen Wert und führt erst dann die äußere Abfrage mit diesem Wert aus. Da nur ein einzelner Wert zurückgegeben wird, sind nur die Standardvergleichsoperatoren zulässig. =, <> (oder !=), >, >=, < und <=.

💡 Tipp: Wenn eine Unterabfrage nach = gibt mehr als eine Zeile zurück. MySQL löst Fehler 1242 aus, Die Unterabfrage gibt mehr als eine Zeile zurück.. Schalten Sie den Operator um auf INoder die innere WHERE-Klausel verschärfen.

2) Zeilenunterabfrage

A Zeilen-Unterabfrage Die Abfrage gibt ebenfalls eine einzelne Zeile zurück, diese kann jedoch mehrere Spalten enthalten. Die äußere Abfrage vergleicht daher eine Zeile mit Werten mit einem Zeilenkonstruktor anstatt mit einem einzelnen Wert.

SELECT full_names, contact_number FROM members
WHERE (membership_number, gender) = (SELECT membership_number, gender FROM members WHERE full_names = 'Janet Jones');

Zulässige Operatoren sind die gleichen Vergleichsoperatoren wie oben aufgeführt, die auf die gesamte Zeile gleichzeitig angewendet werden.

3) Tabellen-Unterabfrage

A Tabellenunterabfrage Gibt mehrere Zeilen und oft auch mehrere Spalten zurück, muss die äußere Abfrage einen Mengenoperator wie z. B. verwenden. IN, NOT IN, ANY, ALLden EXISTS.

Angenommen, Sie benötigen die Namen und Telefonnummern von Mitgliedern, die einen Film ausgeliehen, aber noch nicht zurückgegeben haben, um sie telefonisch daran zu erinnern. Sie können eine Abfrage wie diese verwenden:

SELECT full_names, contact_number FROM members
WHERE membership_number IN (SELECT membership_number FROM movierentals WHERE return_date IS NULL);

Mal sehen, wie diese Abfrage funktioniert.

In diesem Fall liefert die innere Abfrage mehrere Ergebnisse, daher wird die Liste der Mitgliedsnummern an die IN Der Operator und alle zugehörigen Elemente werden zurückgegeben.

Verschachtelung von Unterabfragen über mehrere Ebenen

Bisher haben Sie zwei Ebenen kennengelernt. Eine Unterabfrage kann auch eine weitere Unterabfrage enthalten, wodurch eine dreifach verschachtelte Anweisung entsteht.

Angenommen, das Management möchte das Mitglied mit dem höchsten Beitragssatz belohnen. Wir können eine Abfrage wie diese ausführen:

SELECT full_names FROM members
WHERE membership_number = (SELECT membership_number FROM payments
    WHERE amount_paid = (SELECT MAX(amount_paid) FROM payments));

Die innerste Abfrage ermittelt die höchste Zahlung, die mittlere Abfrage wandelt diesen Betrag in eine Mitgliedsnummer um und die äußere Abfrage wandelt die Mitgliedsnummer in einen Namen um. Die obige Abfrage liefert folgendes Ergebnis:

Wie man Unterabfragen mit INSERT, UPDATE und DELETE verwendet

Unterabfragen sind nicht auf SELECT-Anweisungen beschränkt. Das gleiche Klammermuster funktioniert auch innerhalb von Datenänderungsanweisungen, wodurch ein ganzer Satz von Zeilen in einem Durchlauf geändert werden kann, ohne eine temporäre Tabelle zu erstellen.

INSERT mit einer Unterabfrage. Eine Unterabfrage kann die einzufügenden Zeilen liefern und kopiert Daten von einer Tabelle in eine andere. Die Spaltenliste der SELECT-Anweisung muss mit der Spaltenliste der INSERT-Anweisung übereinstimmen.

INSERT INTO vip_members (membership_number, full_names)
SELECT membership_number, full_names FROM members
WHERE membership_number IN (SELECT membership_number FROM payments WHERE amount_paid > 5000);

UPDATE mit einer Unterabfrage. Die innere Abfrage entscheidet hier, welche Zeilen betroffen sind. Das folgende Beispiel markiert alle Mitglieder, deren Miete noch aussteht.

UPDATE members
SET reminder_sent = 1
WHERE membership_number IN (SELECT membership_number FROM movierentals WHERE return_date IS NULL);

DELETE mit einer Unterabfrage. Das gleiche Prinzip entfernt Zeilen, die eine in einer zweiten Tabelle festgelegte Bedingung erfüllen.

DELETE FROM members
WHERE membership_number NOT IN (SELECT membership_number FROM movierentals);

⚠️ Warnung: MySQL Eine Anweisung, die eine Tabelle ändert und innerhalb einer Unterabfrage in der FROM-Klausel Daten aus derselben Tabelle auswählt, ist nicht zulässig. Falls der Fehler 1093 auftritt, schließen Sie die innere Abfrage in eine abgeleitete Tabelle ein, z. B. SELECT * FROM (SELECT ...) AS t, So dass MySQL Das Ergebnis wird ermittelt, bevor die Änderung angewendet wird. Es ist außerdem ratsam, die innere SELECT-Anweisung zunächst separat auszuführen und die Zeilenanzahl zu bestätigen, bevor eine Änderung angewendet wird. AKTUALISIEREN oder die LÖSCHEN in Produktion.

Unterabfragen vs. Joins

Sowohl eine Unterabfrage als auch ein JOIN können Informationen aus mehr als einer Tabelle kombinieren, daher stellt sich die Frage, welche Methode man verwenden sollte.

Im Vergleich zu Joins sind Unterabfragen einfach zu verwenden und leicht lesbar. Sie sind nicht so kompliziert wie Joinsund werden daher häufig verwendet von SQL-Anfänger.

Unterabfragen weisen jedoch Leistungsprobleme auf. Die Verwendung eines Joins anstelle einer Unterabfrage kann die Leistung mitunter um das bis zu 500-Fache steigern, da der Optimierer einen Join in einem einzigen Durchlauf auflösen kann, anstatt die innere Anweisung wiederholt auszuwerten.

Wenn Sie die Wahl haben, wird empfohlen, einen JOIN für eine Unterabfrage zu verwenden. Unterabfragen sollten nur als Ausweichlösung verwendet werden, wenn Sie mit einer JOIN-Operation nicht das oben Genannte erreichen können.

Unterabfragen lassen sich außerdem leicht in einzelne logische Komponenten zerlegen, was sehr nützlich ist, wenn testing und Debuggen der Abfragen.

Häufig gestellte Fragen