9. Subqueries
Subqueries in SQL
Abschnitt betitelt „Subqueries in SQL“Subqueries - Die Kraft verschachtelter Logik
Abschnitt betitelt „Subqueries - Die Kraft verschachtelter Logik“Bis zu diesem Punkt haben Sie gelernt, wie man Daten abruft, verbindet und aggregiert. In der realen Welt stehen Sie jedoch oft vor einem “Henne-Ei-Problem”: Sie möchten Daten anhand eines Wertes filtern, den Sie noch gar nicht kennen.
Stellen Sie sich zum Beispiel vor, Sie sollen alle Spieler finden, die mehr als den Durchschnitt erzielt haben. Um das zu beantworten, bräuchten Sie normalerweise zwei Schritte:
- Eine Abfrage ausführen, um den Durchschnittswert zu finden (z. B. 42).
- Eine zweite Abfrage mit dieser Zahl ausführen:
WHERE score > 42.
Subqueries (Unterabfragen) erlauben es Ihnen, diese beiden Schritte zu einer einzigen, eleganten und dynamischen Anweisung zu kombinieren. Statt eine Zahl wie “42” fest einzucodieren, betten Sie die erste Abfrage direkt in die zweite ein. Das macht Ihren SQL-Code “intelligent” und anpassungsfähig an sich ändernde Daten.
Stellen Sie sich eine Subquery als Abfrage innerhalb einer Abfrage vor - ein spezialisiertes Werkzeug, das es Ihnen erlaubt, komplexe, mehrschichtige Fragen in einem Durchgang zu lösen. Es ist der Schritt, bei dem Sie vom einfachen Abrufen von Daten zum Aufbau anspruchsvoller analytischer Logik übergehen.
Arten von Subqueries
Abschnitt betitelt „Arten von Subqueries“In SQL hängt die Art, wie Sie eine Subquery verwenden, vollständig von der Struktur der Daten ab, die sie zurückgibt. Wir unterscheiden zwischen Subqueries, die einen einzelnen Wert zurückgeben (Skalar), und solchen, die eine Liste oder eine ganze Menge zurückgeben (Tabelle).
Skalare Subqueries (Rückgabe eines einzelnen Werts)
Abschnitt betitelt „Skalare Subqueries (Rückgabe eines einzelnen Werts)“Eine skalare Subquery gibt genau eine Zeile und eine Spalte zurück. Da sie einen einzelnen Wert ergibt (etwa ein bestimmtes Datum oder eine Zahl), können Sie sie mit Standard-Vergleichsoperatoren wie =, > oder < verwenden.
Beispiel: Schüler finden, die jünger als der Durchschnitt sind
Um alle Schüler zu finden, die jünger als das Durchschnittsalter aller Schüler sind, muss die Datenbank zuerst das durchschnittliche Geburtsjahr berechnen. Schüler mit einem höheren Geburtsjahr wurden später geboren und sind daher jünger.
SELECT first_name, last_name, birth_dateFROM studentsWHERE YEAR(birth_date) > (SELECT AVG(YEAR(birth_date)) FROM students);- Die innere Abfrage: Berechnet das durchschnittliche Geburtsjahr über alle Schüler.
- Die äußere Abfrage: Vergleicht das Geburtsjahr jedes Schülers mit diesem einen berechneten Wert - Schüler, die in einem späteren Jahr geboren wurden, sind jünger.
Tabellen-Subqueries (Rückgabe mehrerer Werte)
Abschnitt betitelt „Tabellen-Subqueries (Rückgabe mehrerer Werte)“Eine Tabellen-Subquery gibt eine Liste (eine Spalte, mehrere Zeilen) oder sogar eine vollständige virtuelle Tabelle zurück. Da Sie es mit mehreren Werten zu tun haben, verwenden Sie mengenbasierte Operatoren wie IN, ANY oder ALL.
A) Verwendung des IN-Operators
Abschnitt betitelt „A) Verwendung des IN-Operators“Dies ist der häufigste Anwendungsfall für Subqueries. Es wird geprüft, ob ein Wert aus der äußeren Abfrage in der Ergebnisliste der inneren Abfrage vorhanden ist.
Beispiel: Schüler eines bestimmten Klassenvorstands finden
Angenommen, Sie möchten alle Schüler finden, deren Klassenvorstand ‘Herr Maurhart’ ist, aber der Name der Lehrkraft ist nur in der Tabelle teachers gespeichert.
SELECT first_name, last_name, classFROM studentsWHERE class IN ( SELECT class_name FROM teachers WHERE teacher_name = 'Maurhart');B) Subqueries in der FROM-Klausel (abgeleitete Tabellen)
Abschnitt betitelt „B) Subqueries in der FROM-Klausel (abgeleitete Tabellen)“Sie können eine Subquery verwenden, um eine temporäre “Tabelle” zu erstellen, und sie dann wie eine normale Tabelle behandeln.
Hinweis: In den meisten SQL-Dialekten muss einer Subquery in der
FROM-Klausel ein Alias (ein temporärer Name) gegeben werden.
Beispiel: Schüler in einer vorgefilterten Liste zählen Wenn Sie zuerst eine bestimmte Gruppe filtern und sie dann zählen möchten:
SELECT COUNT(*) AS junior_countFROM ( SELECT * FROM students WHERE birth_date > '2012-01-01') AS young_students;Korrelierte Subqueries
Abschnitt betitelt „Korrelierte Subqueries“Eine korrelierte Subquery ist eine besondere Art von Subquery, die auf eine oder mehrere Spalten der äußeren Abfrage verweist. Dadurch entsteht eine lebendige Verbindung zwischen der inneren und der äußeren Abfrage.
Der entscheidende Unterschied zu einer normalen Subquery ist, dass sie von der äußeren Abfrage abhängt und nicht für sich allein ausgewertet werden kann:
| Normale Subquery | Korrelierte Subquery | |
|---|---|---|
| Eigenständig auswertbar? | Ja, unabhängig von der äußeren Abfrage | Nein, benötigt Werte aus der äußeren Abfrage |
| Ergebnis | Ein fester Wert oder eine Liste | Ein Wert, der von der jeweiligen äußeren Zeile abhängt |
| Verweist auf äußere Abfrage? | Nein | Ja |
Stellen Sie sich das so vor: Das Ergebnis der inneren Abfrage hängt von den Daten genau des Schülers ab, der gerade von der äußeren Abfrage betrachtet wird.
Beispiel: Schüler finden, die über dem Durchschnitt ihrer eigenen Klasse liegen
Statt jeden Schüler mit dem schulweiten Durchschnitt zu vergleichen, möchten Sie jene finden, die den Durchschnitt ihrer eigenen Klasse übertreffen.
SELECT first_name, last_name, class, gradeFROM students AS s_outerWHERE grade > ( SELECT AVG(grade) FROM students AS s_inner WHERE s_inner.class = s_outer.class -- Verbindung zur aktuellen äußeren Zeile);- Die Verbindung:
s_inner.class = s_outer.class- die innere Abfrage “blickt zurück” auf die Zeile, die gerade von der äußeren Abfrage untersucht wird. - Was das bedeutet: Für einen Schüler in “3AHIF” liefert die innere Abfrage den Durchschnitt nur für 3AHIF; für einen Schüler in “3BHIF” den Durchschnitt für “3BHIF”, und so weiter.
EXISTS mit korrelierten Subqueries verwenden
Abschnitt betitelt „EXISTS mit korrelierten Subqueries verwenden“Der EXISTS-Operator wird häufig mit korrelierten Subqueries kombiniert. Er gibt keine Daten aus der inneren Abfrage zurück - er prüft nur, ob die innere Abfrage überhaupt Zeilen erzeugt. Wenn ja, ist das Ergebnis TRUE.
Beispiel: Alle Lehrkräfte finden, die mindestens einen Schüler in ihrer Klasse haben
SELECT teacher_name, class_nameFROM teachers AS tWHERE EXISTS ( SELECT 1 FROM students AS s WHERE s.class = t.class_name -- Verbindung: irgendein Schüler in der Klasse dieser Lehrkraft?);SELECT 1ist eine gängige Kurzform - der tatsächlich zurückgegebene Wert spielt keine Rolle, nur ob Zeilen existieren.- Das ist oft besser lesbar als eine lange
IN (...)-Liste, wenn die Existenz einer Beziehung geprüft werden soll.
Performance-Hinweis: Bei sehr großen Tabellen können korrelierte Subqueries langsamer sein als andere Formulierungen. Für schulgroße und mittelgroße Datenmengen sind sie eine völlig gültige und klare Lösung.
Vergleich: Wann verwendet man was?
Abschnitt betitelt „Vergleich: Wann verwendet man was?“| Merkmal | Skalare Subquery | Tabellen-Subquery | Korrelierte Subquery |
|---|---|---|---|
| Ergebnis | 1 Zeile, 1 Spalte | Mehrere Zeilen (Liste oder Tabelle) | Hängt von der jeweiligen äußeren Zeile ab |
| Häufige Operatoren | =, >, <, <> | IN, NOT IN, FROM | EXISTS, =, > |
| Verweist auf äußere Abfrage? | Nein | Nein | Ja |
| Schulbeispiel | ”… über dem Schuldurchschnitt." | "… in einer Liste bestimmter Klassen." | "… über dem Durchschnitt ihrer eigenen Klasse.” |
Zusammenfassung: Subqueries
Abschnitt betitelt „Zusammenfassung: Subqueries“In SQL ist eine Subquery eine “Abfrage innerhalb einer Abfrage”. Sie erlaubt es Ihnen, komplexe Fragen in einem Schritt zu lösen, indem das Ergebnis einer Abfrage der Haupt-Abfrage (äußeren Abfrage) hilft, Daten zu filtern oder zu verarbeiten. Das ist besonders nützlich, wenn Sie Daten anhand eines Wertes filtern müssen, den Sie noch nicht kennen - etwa eines Durchschnitts oder einer bestimmten Liste.
1. Das Kernkonzept
Abschnitt betitelt „1. Das Kernkonzept“Subqueries machen Ihren SQL-Code dynamisch. Statt einen Wert manuell nachzuschlagen und ihn dann in eine zweite Abfrage einzutippen, berechnet die Subquery diesen Wert “im Vorbeigehen”.
- Innere Abfrage: Liefert ein Ergebnis (etwa einen Wert oder eine Liste).
- Äußere Abfrage: Verwendet dieses Ergebnis, um die endgültige Datenmenge zu erzeugen.
2. Arten von Subqueries
Abschnitt betitelt „2. Arten von Subqueries“Wie Sie eine Subquery verwenden, hängt davon ab, was sie zurückgibt und ob sie auf die äußere Abfrage verweist:
- Skalare Subqueries (Einzelwert):
- Geben genau einen Wert zurück (z. B. die Durchschnittsnote).
- Werden mit Standardoperatoren wie
=,>oder<verwendet. - Beispiel: Alle Schüler finden, deren Punktzahl höher als der schulweite Durchschnitt ist.
- Tabellen-Subqueries (Listen oder Tabellen):
- Geben eine Liste von Werten oder eine temporäre Tabelle zurück.
- Werden mit Mengenoperatoren wie
INoder in derFROM-Klausel verwendet. - Beispiel: Alle Schüler finden, die zu einer Liste von Klassen gehören, die von einer bestimmten Lehrkraft betreut werden.
- Korrelierte Subqueries:
- Verweisen auf eine Spalte der äußeren Abfrage; ihr Ergebnis hängt daher von der jeweiligen äußeren Zeile ab.
- Werden mit
EXISTSoder Vergleichsoperatoren verwendet, wenn der verglichene Wert von der aktuellen Zeile abhängt. - Beispiel: Schüler finden, die über dem Durchschnitt ihrer eigenen Klasse liegen.
3. Schnellvergleich
Abschnitt betitelt „3. Schnellvergleich“| Merkmal | Skalare Subquery | Tabellen-Subquery | Korrelierte Subquery |
|---|---|---|---|
| Ergebnis | 1 Zeile, 1 Spalte | Mehrere Zeilen | Hängt von der jeweiligen äußeren Zeile ab |
| Operatoren | =, >, <, <> | IN, EXISTS, FROM | EXISTS, =, > |
| Verweist auf äußere Abfrage? | Nein | Nein | Ja |
| Ziel | Mit einer einzelnen Kennzahl vergleichen | Gegen eine Liste oder Gruppe filtern | Jede Zeile mit ihrer eigenen Gruppenkennzahl vergleichen |
4. Abgeleitete Tabellen
Abschnitt betitelt „4. Abgeleitete Tabellen“Sie können eine Subquery auch in die FROM-Klausel setzen. Das erzeugt eine temporäre “virtuelle Tabelle”, die Sie dann wie eine normale Tabelle abfragen können. In den meisten SQL-Systemen müssen Sie dieser temporären Tabelle einen Spitznamen (einen Alias) geben, damit es funktioniert.
Lernergebnisse: Was Sie können sollten
Abschnitt betitelt „Lernergebnisse: Was Sie können sollten“- Erkennen: Erkennen, wann ein Problem zwei Schritte erfordert (z. B. “Finde den Durchschnitt, dann filtere”) und es mit einer Subquery lösen.
- Vergleichen: Skalare Subqueries verwenden, um Daten gegen einzelne Werte wie
MAX()oderAVG()zu filtern. - Mit Listen filtern: Den
IN-Operator verwenden, um Tabellen zu verbinden, ohne einen Standard-JOIN zu benutzen, wenn nur ein Filter benötigt wird. - Strukturieren: Subqueries je nach Ziel korrekt in der
WHERE- oderFROM-Klausel platzieren. - Korrelieren: Eine korrelierte Subquery schreiben, die die innere Abfrage mit der aktuellen äußeren Zeile verbindet (z. B. “über dem Durchschnitt der eigenen Klasse”), und verstehen, dass ihr Ergebnis von der jeweiligen äußeren Zeile abhängt.
- Existenzprüfung:
EXISTSmit einer korrelierten Subquery verwenden, um effizient zu testen, ob eine verknüpfte Zeile in einer anderen Tabelle vorhanden ist.