Eine Abfrage in einer Abfrage

Die Chefin von Pixel Shop will wissen, welche Spiele mehr kosten als das durchschnittliche Spiel. Du weißt, wie du den Durchschnitt findest:

SELECT AVG(preis) FROM spiele;

Du könntest also eine zweite Abfrage mit WHERE preis > 28.5 schreiben. Das funktioniert, aber du hast die Zahl von Hand abgeschrieben: Wenn der Shop einen Preis ändert, ändert sich auch der Durchschnitt, und deine Abfrage gibt stillschweigend eine falsche Antwort. Und die naheliegende Abkürzung funktioniert gar nicht:

SELECT titel
FROM spiele
WHERE preis > AVG(preis);

Error on line 1: misuse of aggregate function AVG()

WHERE entscheidet über eine Zeile nach der anderen, bevor irgendwelche Gruppen gebildet werden (erinnere dich an die Reihenfolge, in der die Teile einer Abfrage laufen), also gibt es noch keinen Durchschnitt, mit dem es vergleichen könnte.

Der Ausweg ist, die erste Abfrage in Klammern genau dorthin zu setzen, wo die Zahl stand:

SELECT titel, preis
FROM spiele
WHERE preis > (SELECT AVG(preis) FROM spiele)
ORDER BY preis DESC, titel;

Die Abfrage in Klammern ist eine Unterabfrage: eine Abfrage innerhalb einer anderen Abfrage. Die Datenbank führt sie zuerst aus, bekommt einen Wert, 28.5, und führt dann die äußere Abfrage aus, als hättest du an ihrer Stelle 28.5 getippt. Aber anders als eine getippte Zahl berechnet die Unterabfrage den Durchschnitt bei jeder Ausführung neu, er stimmt also immer.

Eine Unterabfrage ist eine vollständige Abfrage mit eigenem SELECT und FROM, und sie kann ihr eigenes WHERE haben. Wenn sie einen einzigen Wert liefert (eine Zeile mit einer Spalte), kannst du sie überall benutzen, wo eine Zahl, ein Text oder ein Datum stehen könnte. Diese Abfrage findet die letzte Bestellung: Die Unterabfrage findet das späteste Datum, und die äußere Abfrage die Bestellung, die an diesem Tag aufgegeben wurde:

SELECT *
FROM bestellungen
WHERE bestellt_am = (SELECT MAX(bestellt_am) FROM bestellungen);

Eine Unterabfrage kann auch Teil einer Rechnung sein: preis - (SELECT AVG(preis) FROM spiele) AS differenz in SELECT zeigt, wie viel mehr oder weniger als der Durchschnitt jedes Spiel kostet.

Typischer Fehler: eine Unterabfrage, die mehrere Zeilen findet, wo ein Wert erwartet wird. Welche Spiele kosten genauso viel wie ein Action-Spiel?

SELECT titel, preis
FROM spiele
WHERE preis = (SELECT preis FROM spiele WHERE genre = 'Action')
ORDER BY titel;

Es gibt vier Action-Spiele, für 25 €, 35 €, 30 € und 40 €, aber die Abfrage hat nur die Spiele für 25 € gefunden. In SQLite ist das kein Fehler: Wenn eine Unterabfrage, die als Wert benutzt wird, mehrere Zeilen findet, nimmt SQLite stillschweigend die erste (hier den Preis von Shadow Ninja, dem ersten Action-Spiel in der Tabelle) und ignoriert die anderen. Andere Datenbanken brechen mit einem Fehler ab. Eine Unterabfrage, die als Wert benutzt wird, sollte deshalb genau einen Wert liefern: meist eine Aggregatfunktion wie AVG() oder MAX() oder eine Bedingung, die genau eine Zeile trifft. Für eine ganze Liste von Werten gibt es IN, das als Nächstes kommt.