9. Subqueries

Subqueries in SQL

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

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)

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_date
FROM students
WHERE 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)

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

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, class
FROM students
WHERE class IN (
    SELECT class_name
    FROM teachers
    WHERE teacher_name = 'Maurhart'
);

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_count
FROM (
    SELECT * FROM students WHERE birth_date > '2012-01-01'
) AS young_students;

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, grade
FROM students AS s_outer
WHERE 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

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_name
FROM teachers AS t
WHERE EXISTS (
    SELECT 1
    FROM students AS s
    WHERE s.class = t.class_name   -- Verbindung: irgendein Schüler in der Klasse dieser Lehrkraft?
);

SELECT 1 ist 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?

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

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

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

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 IN oder in der FROM-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 EXISTS oder 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

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

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

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() oder AVG() 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- oder FROM-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: EXISTS mit einer korrelierten Subquery verwenden, um effizient zu testen, ob eine verknüpfte Zeile in einer anderen Tabelle vorhanden ist.