Unterabfragen
Das Ergebnis einer Abfrage ist wieder eine Tabelle. Also kann man eine Abfrage überall dort einsetzen, wo eine Tabelle oder ein Wert stehen darf – auch innerhalb einer anderen Abfrage.
Ein Wert aus einer Unterabfrage
Welche Auftritte hatten überdurchschnittlich viele Zuschauer? Den Durchschnitt kennt man vorher nicht.
Eine Unterabfrage (englisch subquery) ist eine Abfrage innerhalb einer anderen. Sie steht in Klammern.
Liefert sie genau einen Wert, darf sie überall stehen, wo ein Wert erlaubt ist – also auch rechts von einem Vergleichsoperator.
Warum nicht einfach die Zahl einsetzen, die Schritt 1 geliefert hat?
Weil sie sich ändert. Sobald ein Auftritt dazukommt, stimmt sie nicht mehr. Die Unterabfrage rechnet den Wert bei jeder Ausführung neu aus – die Abfrage bleibt richtig, egal was in der Datenbank passiert.
Eine Wertemenge aus einer Unterabfrage
Liefert die Unterabfrage eine Spalte mit mehreren Zeilen, passt sie hinter IN.
Die Unterabfrage darf dabei genau eine Spalte ausgeben. SELECT * FROM auftritt würde hinter IN nicht funktionieren.
Die erste Abfrage lässt sich auch mit einem Verbund schreiben:
SELECT DISTINCT b.name
FROM band AS b
JOIN auftritt AS a ON a.band_id = b.band_id
WHERE a.buehne_id = 1
ORDER BY b.name;a) Führe beide aus. Liefern sie dasselbe?
b) Warum braucht die Verbundvariante ein DISTINCT, die Unterabfragevariante aber nicht?
c) Welche findest du besser lesbar? Begründe.
Eine Unterabfrage in der SELECT-Liste
Diese Unterabfrage benutzt b.band_id aus der äußeren Abfrage. Man nennt sie deshalb korreliert: Sie wird für jede Zeile der äußeren Abfrage einmal neu ausgewertet.
Das ist bequem, aber teuer. Bei 22 Bands merkt man nichts; bei 22 Millionen Zeilen sehr wohl. Was sich mit GROUP BY erledigen lässt, sollte man auch mit GROUP BY erledigen.
Eine Unterabfrage im FROM
Damit rechnet man über ein Gruppierungsergebnis: erst je Band zählen, dann über diese Zahlen mitteln. Eine Aggregatfunktion in einer Aggregatfunktion – AVG(COUNT(*)) – ist nicht erlaubt; die Unterabfrage im FROM ist der Weg dorthin.
Eine Unterabfrage im FROM braucht immer einen Aliasnamen, hier je_band.
Das größte Element finden
Damit lässt sich endlich sauber lösen, was in Lektion 1 offengeblieben ist:
Warum ist das besser als ORDER BY zuschauer DESC LIMIT 1?
Weil LIMIT 1 bei einem Gleichstand willkürlich eine Zeile auswählt und die andere verschweigt. Die Variante mit MAX liefert alle Auftritte mit der Höchstzahl.
Welche Variante richtig ist, hängt von der Frage ab: „Zeig mir einen Rekordhalter" oder „Zeig mir alle Rekordhalter".
Aufgaben
a) Alle Bands, deren Gründungsjahr über dem Durchschnitt aller Gründungsjahre liegt.
b) Alle Personen, die in mindestens einer Band Gesang machen – gelöst mit einer Unterabfrage, nicht mit einem Verbund.
c) Das teuerste Ticket: Zeige alle Tickets, deren Preis dem Höchstpreis entspricht.
d) Zeige für jede Bühne den Namen und die Zahl der dort gespielten Auftritte – gelöst mit einer korrelierten Unterabfrage in der SELECT-Liste.
Tipp 1: zu b)
Die Unterabfrage liefert die Menge der person_id, die in mitgliedschaft mit instrument = 'Gesang' steht. Die äußere Abfrage prüft mit IN, ob eine Person dazugehört.
Tipp 2: zu d)
Gerüst:
SELECT s.name,
(SELECT COUNT(*) FROM auftritt AS a WHERE a.buehne_id = …) AS auftritte
FROM buehne AS s;