Korrelierte Unterabfragen
Kurz gesagt: Eine korrelierte Unterabfrage nutzt eine Spalte der äußeren Abfrage und wird deshalb für jede Zeile neu berechnet, während eine Unterabfrage in FROM ihr Ergebnis zu einer neuen Tabelle macht.
Problem
Teuer, im Vergleich zu wem?
Aus der vorigen Lektion weißt du, welche Produkte über dem Durchschnittspreis im Shop liegen:
Alles aus der Küche ist „teuer“, und kein Brot ist es. Aber Brot wird nicht mit einer Bratpfanne verglichen. Die Shopleitung will wissen, welche Produkte für ihre eigene Kategorie teuer sind: der Käse im Vergleich zu den Milchprodukten, der Kaffee im Vergleich zu den Getränken.
Die Idee
Korrelierte Unterabfrage
Eine Unterabfrage kann eine Spalte aus der Zeile nutzen, die die äußere Abfrage gerade ansieht:
p ist das Produkt außen, p2 sind die Produkte in der Unterabfrage. Die Bedingung p2.category_id = p.category_id heißt „die Produkte seiner Kategorie“. Für das Weißbrot berechnet die Unterabfrage den Durchschnitt von Brot und Gebäck (1,60 €), für den Weißkäse den Durchschnitt der Milchprodukte (4,66 €).
Eine solche Unterabfrage heißt korreliert: Sie hängt von der äußeren Zeile ab und wird für jede Zeile neu berechnet. Die gewöhnliche Unterabfrage aus der vorigen Lektion gibt für alle Zeilen dieselbe Antwort.
Ohne die kurzen Namen p und p2 ginge es nicht: Beide sind die Tabelle products, und die Datenbank muss wissen, welches category_id außen und welches innen gemeint ist.
Die Idee
In WHERE
Jetzt die Frage der Shopleitung: Welche Produkte liegen über dem Durchschnitt ihrer Kategorie?
Neun Produkte, aus jeder Kategorie mindestens eins: Das Vollkornbrot liegt über dem Durchschnitt von Brot und Gebäck, und das Waschpulver über dem Durchschnitt der Reinigung.
Die Geschenkkarte fehlt, obwohl sie 20 € kostet. Ihre Kategorie ist NULL, p2.category_id = NULL ist für keine Zeile wahr, der Durchschnitt ist NULL und der Vergleich unbekannt. Dasselbe NULL wie in der NULL-Lektion, nur besser versteckt.
Probier es aus
Die letzte Bestellung
Zeige für jeden Kunden seinen Namen und das Datum seiner letzten Bestellung (Spalte last_order). Auch die Kunden ohne Bestellungen müssen in der Liste stehen.
Lösung
SELECT name,
(SELECT MAX(o.ordered_at) FROM orders o WHERE o.customer_id = c.id) AS last_order
FROM customers c;(SELECT MAX(o.ordered_at) FROM orders o WHERE o.customer_id = c.id) als Spalte. 20 Zeilen. Teodora und Yavor haben keine Bestellungen, ihr Wert ist also NULL. Mit JOIN und GROUP BY würden sie verschwinden, außer es ist ein LEFT JOIN.
Genauer, für Neugierige
Im Prinzip läuft eine korrelierte Unterabfrage einmal für jede äußere Zeile: 20 Kunden, 20 Mal. Datenbanken schreiben sie oft in etwas Schnelleres um, aber bei großen Tabellen lohnt sich der Vergleich mit JOIN und GROUP BY. Wie du siehst, was die Datenbank tut, steht im Modul über Geschwindigkeit.
Eine korrelierte Unterabfrage liefert auch die Nummer der letzten Bestellung, nicht nur ihr Datum. Im Modul über das Zusammenfassen hast du gesehen, dass SQLite das mit MAX und einer freien Spalte erlaubt, andere Datenbanken aber nicht. Hier ist der Weg, der überall funktioniert:
18 Zeilen, eine für jeden Kunden mit Bestellungen. Hat ein Kunde an seinem letzten Tag zwei Bestellungen, kommen beide heraus.
Sieh es dir an
Der durchschnittliche Bestellwert
Die Buchhaltung fragt nach dem durchschnittlichen Wert einer Bestellung. Der erste Versuch:
Es braucht zwei Schritte: erst den Wert jeder Bestellung, dann den Durchschnitt dieser Werte. Den ersten Schritt kannst du schon:
Die Idee
Unterabfrage in FROM
Das Ergebnis jeder Abfrage ist eine Tabelle. Es kann also in FROM stehen, wo du einen Tabellennamen schreibst:
Die innere Abfrage macht eine Tabelle mit 60 Zeilen, eine pro Bestellung. Die äußere liest sie wie jede andere Tabelle und bildet den Durchschnitt: 32,73 €. Das heißt Unterabfrage in FROM (abgeleitete Tabelle, derived table). Der Name nach AS ist der, mit dem du die neue Tabelle ansprichst.
Genauer, für Neugierige
SQLite verlangt keinen Namen für eine Unterabfrage in FROM, aber MySQL und PostgreSQL bis Version 16 verlangen ihn zwingend. Gib ihr immer einen Namen, dann läuft die Abfrage überall.
Probier es aus
Wie viele Produkte in einer Bestellung
Das Lager plant Kartons und fragt: Wie viele verschiedene Produkte hat eine Bestellung im Durchschnitt? Runde auf zwei Nachkommastellen (Spalte avg_lines). Das Feld zählt die Produkte jeder Bestellung schon.
Lösung
SELECT ROUND(AVG(lines), 2) AS avg_lines
FROM (
SELECT order_id, COUNT(*) AS lines
FROM order_items
GROUP BY order_id
) AS t;Die Zahl für jede Bestellung wird zu einer Tabelle in FROM, und außen steht ROUND(AVG(lines), 2): 2,53 Produkte. 152 Positionen in 60 Bestellungen.
Übung
Zwei Aufgaben
1. Das Marketing will die Kunden zurückgewinnen, die seit dem Sommer nicht mehr bestellt haben.
Zeige die Namen der Kunden, deren letzte Bestellung vor dem 1. Juli 2026 liegt.
Lösung
SELECT name FROM customers c
WHERE (SELECT MAX(o.ordered_at) FROM orders o WHERE o.customer_id = c.id) < '2026-07-01';WHERE (SELECT MAX(o.ordered_at) FROM orders o WHERE o.customer_id = c.id) < '2026-07-01'. Acht Kunden. Teodora und Yavor sind nicht dabei: Ohne Bestellungen ist das letzte Datum NULL, und ein Vergleich mit NULL ist unbekannt.
2. Wie viel gibt ein Kunde im Shop im Durchschnitt aus?
Berechne den Durchschnittsbetrag, den ein Kunde für seine Bestellungen ausgegeben hat, ohne die stornierten, gerundet auf zwei Nachkommastellen (Spalte avg_spent). Zähle nur die Kunden mit mindestens einer Bestellung.
Lösung
SELECT ROUND(AVG(spent), 2) AS avg_spent
FROM (
SELECT o.customer_id, SUM(oi.quantity * oi.unit_price) AS spent
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status <> 'cancelled'
GROUP BY o.customer_id
) AS t;In FROM steht die Summe jedes Kunden (JOIN von orders und order_items, WHERE o.status <> 'cancelled', GROUP BY o.customer_id), und außen steht ROUND(AVG(spent), 2): 94,28 €. Der Durchschnitt läuft über 18 Kunden. Die zwei ohne Bestellungen sind nicht in der Unterabfrage, ziehen den Durchschnitt also nicht nach unten.
Test