Level 14: Abfragen in Abfragen: Unterabfragen

Lektion

Manchmal enthält eine Frage eine andere. „Welche Filme haben eine Bewertung über dem Durchschnitt?“ verlangt zuerst, den Durchschnitt zu berechnen, und dann jeden Film damit zu vergleichen. Eine Unterabfrage beantwortet die erste Frage innerhalb der zweiten: Sie ist ein vollständiges SELECT, das in Klammern in einer anderen Abfrage steht.

Warum nicht WHERE rating > AVG(rating) schreiben? Weil WHERE Zeile für Zeile arbeitet, vor jeder Aggregatberechnung. Die Unterabfrage (SELECT AVG(rating) FROM movies) ist eine unabhängige Abfrage, die getrennt berechnet wird: Ihr Wert (7.2) wird dann wie eine einfache Zahl verwendet.

Eine skalare Unterabfrage liefert einen einzigen Wert, eine Zeile und eine Spalte: Man vergleicht sie mit =, < oder >. Sie muss wirklich eine einzige Zeile liefern: Im Standard-SQL führen mehrere Zeilen zu einem Fehler, und SQLite nimmt stillschweigend die erste.

Eine Unterabfrage, die eine Spalte mit mehreren Werten liefert, verwendet man mit IN: WHERE author_id IN (SELECT id FROM authors WHERE country = 'Sweden'). NOT IN erfordert Vorsicht: Liefert die Unterabfrage ein einziges NULL, gibt NOT IN keine Zeile mehr zurück (siehe Level 12). Füge in der Unterabfrage WHERE … IS NOT NULL hinzu oder verwende NOT EXISTS (Level 15).

Eine Unterabfrage kann schließlich eine Tabelle im FROM ersetzen: Man fragt dann ihr Ergebnis wie eine temporäre Tabelle ab, der man einen Alias geben muss. FROM (SELECT seller, SUM(amount) AS total FROM sales GROUP BY seller) AS t erlaubt zum Beispiel, den Durchschnitt der Umsätze pro Verkäufer zu berechnen.

Lesetipp: Beginne immer mit der Unterabfrage. Führe sie allein aus, um zu sehen, was sie liefert, und lies dann die Hauptabfrage.

Syntax

SELECT …
FROM t
WHERE x > (SELECT AVG(x) FROM t) AND id IN (SELECT t_id FROM u);

Kommentiertes Beispiel

SELECT title, rating
FROM movies
WHERE rating > (SELECT AVG(rating) FROM movies);

Die Unterabfrage berechnet die Durchschnittsbewertung (7.2); die Hauptabfrage behält die Filme darüber.

Ergebnis des Beispiels

Das Wichtigste

• Die Unterabfrage steht in Klammern.
• Skalar: ein Wert, verglichen mit = < >. Spalte: verwendet mit IN.
• In FROM verhält sich eine Unterabfrage wie eine temporäre Tabelle.

Häufige Fallen

• WHERE salary > AVG(salary) ist nicht erlaubt: Man braucht eine Unterabfrage.
• NOT IN mit einer Unterabfrage, die ein NULL enthält, liefert nichts.

Weiterführend

Das Standard-SQL bietet auch x > ALL (Unterabfrage) („größer als alle Werte“) und x > ANY (Unterabfrage) („größer als mindestens einer“). SQLite kennt sie nicht: Man schreibt x > (SELECT MAX(…) …) und x > (SELECT MIN(…) …). Ein Detail unterscheidet sich: Bei einer leeren Unterabfrage ist > ALL wahr, während der Vergleich mit dem Maximum NULL ergibt.

IN oder EXISTS? Um die Zeilen mit einem Partner zu behalten, liefern beide dasselbe Ergebnis. Für das Gegenteil ziehe NOT EXISTS dem NOT IN vor, sobald die Spalte NULL enthalten kann (Übung 14.4).

Die 5 Übungen des Levels

1. Geführt · Zeige den Namen und das Gehalt der Mitarbeitenden, die mehr als das Durchschnittsgehalt verdienen. (Tabelle: staff)
2. Training · Zeige den Titel der Bücher von schwedischen oder deutschen Autoren. (Tabellen: books, authors)
3. Training · Zeige die ID, den Typ und den Preis des teuersten Zimmers. (Tabelle: rooms)
4. Training · Zeige den Namen der Regisseure, die keinen Film gedreht haben. Achtung: Ein Film der Tabelle hat keinen bekannten Regisseur. (Tabellen: directors, movies)
5. Herausforderung · Wie hoch ist der Durchschnitt der Verkaufssummen pro Verkäufer? (Berechne zuerst die Summe jedes Verkäufers, dann den Durchschnitt dieser Summen.) (Tabelle: sales)

In SpeedQL wird jede Abfrage sofort geprüft: Ihr Ergebnis wird mit dem erwarteten verglichen, dann läuft die Abfrage noch einmal auf einer versteckten Kontrolldatenbank. Zu jeder Übung gibt es schriftliche Hinweise, die du nur bei Bedarf aufrufst.