Abfragen in Abfragen: Unterabfragen

Eine Unterabfrage, die einen Wert liefert

Überall, wo ein einzelner Wert stehen könnte, darf stattdessen eine Abfrage stehen, die genau eine Zeile und eine Spalte liefert.

SELECT name,
       price,
       ROUND((SELECT AVG(price) FROM products), 2) AS average_price
FROM products
WHERE price > (SELECT AVG(price) FROM products)
ORDER BY price DESC
LIMIT 20;

(SELECT AVG(price) FROM products) erzeugt eine Zahl, und der Rest der Abfrage behandelt sie, als hätten Sie diese Zahl getippt. Das ist auch die Antwort auf "warum kann ich nicht WHERE price > AVG(price) schreiben" – ein Aggregat fasst eine Gruppe zusammen, und während WHERE läuft, gibt es noch keine Gruppen. Eine Unterabfrage umgeht das, weil sie eine eigene vollständige Abfrage ist, die fertig ist, bevor die äußere filtert.

Eine Unterabfrage, die eine Liste liefert

SELECT first_name, last_name, email, country
FROM customers
WHERE id IN (
  SELECT customer_id
  FROM orders
  WHERE status = 'refunded'
)
ORDER BY last_name
LIMIT 20;

Die innere Abfrage liefert eine Spalte mit Kunden-IDs; IN prüft die Zugehörigkeit. Lesen Sie von innen nach außen – "die Kunden, deren ID unter den Kunden-IDs erstatteter Bestellungen vorkommt".

EXISTS: fragen, ob überhaupt etwas passte

SELECT c.first_name, c.last_name, c.country
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.id
    AND o.total > 1000
)
ORDER BY c.last_name
LIMIT 20;

EXISTS nimmt eine Abfrage und ist wahr, wenn sie mindestens eine Zeile liefert. Das SELECT 1 ist Konvention: Niemand sieht sich den ausgewählten Wert an, nur ob eine Zeile kam, also bringt es nichts, echte Spalten auszuwählen.

Diese Unterabfrage erwähnt c.id aus der äußeren Abfrage, was sie korreliert macht – eine andere Frage je Kunde statt einer einmal berechneten Liste. Für "existiert eine zugehörige Zeile" ist das meist sowohl die klarste Form als auch die, die Engines am besten optimieren.

Die NOT-IN-Falle

110 der 120 Mitarbeiter haben niemanden, der ihnen berichtet. Diese Abfrage sucht sie:

-- Employees nobody reports to. 110 of the 120 qualify,
-- and this query finds none of them.
SELECT first_name, last_name, position
FROM employees
WHERE id NOT IN (SELECT manager_id FROM employees);

Null Zeilen. Kein Fehler.

manager_id darf null sein, und die zehn Personen an der Spitze des Organigramms haben dort NULL. Die innere Abfrage liefert also eine Liste, die ein NULL enthält, und x NOT IN (1, 2, NULL) fragt: "Ist x verschieden von 1, und von 2, und von diesem unbekannten Wert?" Der letzte Teil ist unbekannt, also ist die ganze Bedingung unbekannt, also wird nie eine Zeile behalten – für jedes x. Ein einziges NULL irgendwo in der Liste leert das Ergebnis.

IN ist davon nicht in gleicher Weise betroffen: Eine Zeile, die auf einen echten Wert passt, bleibt erhalten, was sonst auch in der Liste steht. Nur die verneinte Form verlangt, dass jeder Vergleich wahr ist, weshalb ein Unbekanntes genügt, um sie zu leeren.

Die Korrektur ist entweder WHERE manager_id IS NOT NULL innerhalb der Unterabfrage oder – besser, weil man es nicht vergessen kann – NOT EXISTS:

SELECT e.first_name, e.last_name, e.position
FROM employees e
WHERE NOT EXISTS (
  SELECT 1
  FROM employees m
  WHERE m.manager_id = e.id
)
ORDER BY e.last_name
LIMIT 20;

NOT EXISTS fragt nach Zeilen, nicht nach Werten, also gerät NULL nie in einen Vergleich. Standardmäßig zu NOT EXISTS zu greifen, beseitigt eine ganze Klasse stiller falscher Antworten.

Eine Unterabfrage als Tabelle

Eine Abfrage, die viele Zeilen und Spalten liefert, darf dort stehen, wo eine Tabelle stünde: in FROM. Eine solche Abfrage heißt abgeleitete Tabelle.

SELECT category, products, average
FROM (
  SELECT cat.name AS category,
         COUNT(*) AS products,
         ROUND(AVG(p.price), 2) AS average
  FROM products p
  JOIN categories cat ON cat.id = p.category_id
  GROUP BY cat.id
)
WHERE products >= 10
ORDER BY average DESC;

Das ist die andere Antwort auf "ich will nach einem Aggregat filtern": innen gruppieren, dann das Ergebnis wie eine gewöhnliche Tabelle filtern. Es leistet dasselbe wie HAVING und erlaubt zusätzlich, nach einem Alias zu filtern, was HAVING nicht portabel kann.

Abgeleitete Tabellen lassen sich schachteln, und beim Schachteln werden sie unübersichtlich: Drei Ebenen tief muss die Abfrage von innen nach außen gelesen werden. Die nächste Lektion ersetzt sie durch etwas, das man von oben nach unten liest.

Eine Unterabfrage je Zeile

SELECT p.name,
       p.price,
       (SELECT COUNT(*) FROM reviews r WHERE r.product_id = p.id) AS reviews,
       (SELECT ROUND(AVG(r.rating), 2) FROM reviews r WHERE r.product_id = p.id) AS avg_rating
FROM products p
ORDER BY avg_rating DESC, reviews DESC
LIMIT 20;

Was Sie jetzt können

Alles, außer die komplizierten Abfragen lesbar zu machen – und darum geht es in der nächsten Lektion.