Correlated Subqueries wirklich verstehen

1. Was eine Subquery korreliert macht

Eine correlated Subquery ist eine innere Abfrage, die innerhalb ihrer eigenen WHERE-, SELECT- oder HAVING-Klausel auf eine Spalte der umgebenden, aeusseren Abfrage verweist. Dieser Verweis ist das einzige, aber entscheidende Unterscheidungsmerkmal gegenueber einer nicht korrelierten Subquery: Eine nicht korrelierte Subquery laesst sich unabhaengig von der aeusseren Abfrage einmal ausfuehren und liefert ein festes Ergebnis, eine correlated Subquery kann das nicht, weil ihr Ergebnis von der jeweils aktuellen Zeile der aeusseren Abfrage abhaengt.

Diese Abhaengigkeit erkennt man syntaktisch daran, dass innerhalb der inneren WHERE-Bedingung ein Tabellenalias der aeusseren Abfrage auftaucht, etwa o.customer_id = c.id, wobei c aus der aeusseren FROM-Klausel stammt. Fehlt dieser Bezug, handelt es sich um eine gewoehnliche, nicht korrelierte Subquery, die die Datenbank ein einziges Mal ausfuehren und deren Ergebnis sie fuer alle aeusseren Zeilen wiederverwenden kann.

Der Begriff "korreliert" beschreibt also nicht eine besondere SQL-Syntax, sondern eine logische Abhaengigkeit zwischen innerer und aeusserer Abfrage. Genau diese Abhaengigkeit ist es, die eine correlated Subquery so maechtig fuer Zeile-fuer-Zeile-Logik macht und gleichzeitig zur haeufigsten Ursache fuer unerwartet langsame Abfragen in der Praxis.

2. Das mentale Modell: pro Zeile neu ausgewertet

Um eine correlated Subquery korrekt zu verstehen, hilft folgendes Gedankenmodell: Man stelle sich vor, die Datenbank arbeitet die aeussere Abfrage Zeile fuer Zeile ab und fuehrt fuer jede dieser Zeilen die komplette innere Abfrage separat aus, wobei sie dabei die Werte der aktuellen aeusseren Zeile als feste Konstanten in die innere Abfrage einsetzt. Bei tausend Zeilen in der aeusseren Abfrage bedeutet dieses Modell tausend logisch getrennte Ausfuehrungen der inneren Abfrage.

Wichtig ist die Einschraenkung "logisch": moderne Datenbank-Optimizer fuehren diese tausend Ausfuehrungen in der Praxis meist nicht wirklich tausendmal physisch aus, sondern erkennen das Muster und wandeln es intern in einen effizienteren Join oder Semi-Join um, ein Vorgang, der als Dekorrelation bezeichnet wird. Das mentale Modell "eine Ausfuehrung pro aeusserer Zeile" beschreibt also die Semantik, nicht zwangslaeufig die tatsaechliche physische Ausfuehrung, und genau dieser Unterschied zwischen Semantik und Ausfuehrung ist die Quelle vieler Missverstaendnisse rund um correlated Subqueries.

-- Sample tables
-- customers: id, name
-- orders: id, customer_id, amount, order_date

-- Correlated subquery: highest single order amount per customer
SELECT
  c.name,
  (
    SELECT MAX(o.amount)
    FROM orders o
    WHERE o.customer_id = c.id   -- references the outer row c.id
  ) AS highest_order
FROM customers c;

-- Conceptually, for each row of customers, the database evaluates:
-- SELECT MAX(amount) FROM orders WHERE customer_id = <this row's id>

3. Correlated Subqueries in WHERE, SELECT und HAVING

Eine correlated Subquery kann an drei Stellen einer Abfrage stehen, mit jeweils unterschiedlicher fachlicher Bedeutung. In der WHERE-Klausel filtert sie aeussere Zeilen, meist ueber EXISTS, NOT EXISTS oder einen skalaren Vergleich. In der SELECT-Liste berechnet sie einen zusaetzlichen Wert pro Zeile, wie im Beispiel des vorherigen Abschnitts mit dem hoechsten Bestellwert. In der HAVING-Klausel filtert sie nach der Gruppierung anhand eines Werts, der wiederum von der aktuellen Gruppe abhaengt.

Am haeufigsten begegnet man correlated Subqueries in Kombination mit EXISTS, weil dieses Konstrukt ausschliesslich prueft, ob mindestens eine passende innere Zeile existiert, ohne die konkreten Werte dieser Zeile zurueckzugeben. Diese Beschraenkung auf eine reine Ja/Nein-Antwort erlaubt es dem Optimizer, die Ausfuehrung fruehzeitig abzubrechen, sobald die erste passende Zeile gefunden ist, statt alle passenden Zeilen vollstaendig zu durchsuchen.

-- Correlated subquery in WHERE with EXISTS
SELECT c.name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id AND o.amount > 200
);

-- Correlated subquery in HAVING: customers whose average order
-- exceeds their own highest single order minus a fixed discount
SELECT c.id, AVG(o.amount) AS avg_amount
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id
HAVING AVG(o.amount) > (
  SELECT MAX(o2.amount) - 50
  FROM orders o2
  WHERE o2.customer_id = c.id
);

4. Der Nested-Loop-Ausfuehrungsplan im Detail

Der Ausfuehrungsplan, den eine Datenbank fuer eine nicht dekorrelierte correlated Subquery waehlt, ist typischerweise ein Nested-Loop-Join: fuer jede Zeile der aeusseren Tabelle wird die innere Abfrage einmal ausgefuehrt, in aller Regel als Index-Lookup, wenn ein passender Index existiert, oder als vollstaendiger Table-Scan, wenn keiner existiert. Bei N aeusseren Zeilen und einem Index-Lookup mit logarithmischer Komplexitaet ergibt sich eine Gesamtkomplexitaet von etwa N * log(M), wobei M die Groesse der inneren Tabelle ist, was fuer moderate Tabellengroessen meist akzeptabel ist.

Fehlt der Index auf der Verknuepfungsspalte der inneren Tabelle, verwandelt sich derselbe Nested-Loop-Plan in eine Katastrophe: jede der N aeusseren Zeilen loest einen vollstaendigen Scan der inneren Tabelle mit M Zeilen aus, was eine Gesamtkomplexitaet von N * M bedeutet. Bei zwei Tabellen mit jeweils zehntausend Zeilen sind das hundert Millionen Vergleichsoperationen, statt der wenigen zehntausend, die ein indexbasierter Zugriff benoetigen wuerde. Dieser Unterschied erklaert, warum ein und dieselbe correlated Subquery auf einer kleinen Testdatenbank blitzschnell und auf der Produktionsdatenbank mit echten Datenmengen unertraeglich langsam sein kann.

-- Without an index on orders.customer_id, this triggers
-- a full scan of orders for every single row of customers
EXPLAIN ANALYZE
SELECT c.name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

-- The index turns each inner lookup from O(M) into O(log M)
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

-- Re-run EXPLAIN ANALYZE afterward and compare the "actual time"
-- and "rows removed by filter" values in the plan output

5. Die klassische Performance-Falle bei grossen Tabellen

Die haeufigste Performance-Falle mit correlated Subqueries entsteht nicht durch die Subquery selbst, sondern durch das Zusammenspiel aus fehlendem Index, wachsender Tabellengroesse und einer komplexen inneren Bedingung, die der Optimizer nicht dekorrelieren kann. Eine Subquery, die in der Entwicklungsumgebung mit hundert Testzeilen in Millisekunden laeuft, kann in Produktion mit einer Million Zeilen ploetzlich Minuten dauern, weil die quadratische statt lineare Komplexitaet aus Abschnitt vier erst bei realistischen Datenmengen sichtbar wird.

Ein zweiter, subtilerer Fall entsteht, wenn die innere Abfrage selbst aufwendige Aggregationen oder weitere Joins enthaelt. Wird eine solche Subquery pro aeusserer Zeile neu ausgefuehrt, statt einmalig aggregiert zu werden, vervielfacht sich der Aufwand fuer jede einzelne dieser Aggregationen um den Faktor der aeusseren Zeilenzahl. Dieses Muster faellt in Code-Reviews oft nicht auf, weil die Abfrage syntaktisch harmlos aussieht, obwohl sie inhaltlich sehr aufwendig ist.

-- Expensive: complex aggregation re-evaluated for every outer row
SELECT c.name
FROM customers c
WHERE (
  SELECT COUNT(*)
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  WHERE o.customer_id = c.id
) > 10;

-- Pre-aggregate once instead of re-computing per outer row
SELECT c.name
FROM customers c
JOIN (
  SELECT o.customer_id, COUNT(*) AS item_count
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  GROUP BY o.customer_id
) counts ON counts.customer_id = c.id
WHERE counts.item_count > 10;

6. Umschreiben in einen Join

Die zuverlaessigste Methode, eine langsame correlated Subquery zu beschleunigen, ist die manuelle Umschreibung in einen Join, meist unterstuetzt durch eine vorab aggregierende derived table. Diese Umschreibung erzwingt, dass die innere Abfrage nur einmal ausgefuehrt wird, unabhaengig von der Zeilenzahl der aeusseren Abfrage, und ueberlaesst der Datenbank danach einen regulaeren, meist Hash-basierten Join zwischen zwei bereits fertigen Ergebnismengen. Das Beispiel aus Abschnitt fuenf zeigt dieses Muster bereits: die Aggregation wandert in eine separate, einmal ausgefuehrte derived table.

Fuer den Existenzpruefungs-Fall mit EXISTS ist die direkte Umschreibung in INNER JOIN mit DISTINCT zwar semantisch aehnlich, aber nicht immer die bessere Wahl, weil DISTINCT selbst eine teure Sortier- oder Hash-Operation ist. In den meisten Faellen ist die Empfehlung deshalb differenzierter: EXISTS fuer reine Existenzpruefungen beibehalten, weil moderne Optimizer diese bereits sehr gut in einen Semi-Join umwandeln, und nur skalare oder aggregierende correlated Subqueries aktiv in einen Join mit derived table umschreiben.

-- Correlated subquery for the highest order amount, revisited
SELECT c.name, sub.highest_order
FROM customers c
LEFT JOIN (
  SELECT customer_id, MAX(amount) AS highest_order
  FROM orders
  GROUP BY customer_id
) sub ON sub.customer_id = c.id;
-- the inner aggregation now runs exactly once, not once per customer

7. Umschreiben in eine Window-Funktion

Fuer viele correlated Subqueries, die einen Wert relativ zur aktuellen Zeile berechnen, etwa einen Rang, eine laufende Summe oder den Vergleich mit dem Maximum der eigenen Gruppe, ist eine Window-Funktion die elegantere und meist performantere Alternative zur derived table. Eine Window-Funktion liest die Daten einmal, sortiert sie einmal und berechnet das Ergebnis fuer alle Zeilen in einem einzigen Durchlauf, ohne eine physische zweite Kopie der Tabelle zu benoetigen.

Das Beispiel aus Abschnitt sechs mit dem hoechsten Bestellwert pro Kunde laesst sich direkt mit MAX() OVER (PARTITION BY ...) ausdruecken, was syntaktisch kompakter ist und in aller Regel effizienter ausgefuehrt wird als die entsprechende correlated Subquery, weil die Datenbank die Partitionierung und Aggregation in einem einzigen Scan der Tabelle erledigt.

-- Same result as the correlated subquery in section 2,
-- expressed with a window function instead
SELECT DISTINCT
  c.name,
  MAX(o.amount) OVER (PARTITION BY o.customer_id) AS highest_order
FROM customers c
JOIN orders o ON o.customer_id = c.id;

-- Ranking example: rank each order by amount within its customer
SELECT
  o.customer_id,
  o.id AS order_id,
  o.amount,
  RANK() OVER (PARTITION BY o.customer_id ORDER BY o.amount DESC) AS rank_in_customer
FROM orders o;

8. Wann eine correlated Subquery die richtige Wahl bleibt

Trotz aller Performance-Warnungen ist die correlated Subquery nicht grundsaetzlich zu vermeiden. Fuer reine Existenzpruefungen mit EXISTS oder NOT EXISTS ist sie meist sowohl die lesbarste als auch, dank guter Optimizer-Unterstuetzung, eine performante Loesung, die keiner Umschreibung bedarf. Auch bei kleinen, selten ausgefuehrten Abfragen, etwa administrativen Reports mit einer ueberschaubaren Zeilenzahl, lohnt sich der zusaetzliche Aufwand einer Umschreibung meist nicht, weil der absolute Zeitgewinn gering bleibt.

Die Entscheidung zum Umschreiben sollte deshalb immer datenbasiert getroffen werden: Ein Blick auf den Ausfuehrungsplan mit EXPLAIN ANALYZE zeigt zuverlaessig, ob die Datenbank die correlated Subquery bereits effizient behandelt, etwa als Semi-Join oder Anti-Join, oder ob tatsaechlich ein teurer Nested Loop mit wiederholten Scans entsteht. Erst wenn Letzteres der Fall ist und die betroffene Abfrage regelmaessig oder auf grossen Tabellen laeuft, rechtfertigt sich der Aufwand einer manuellen Umschreibung.

9. Umschreibe-Strategien im Vergleich

Die folgende Tabelle fasst zusammen, welche Umschreibe-Strategie fuer welchen Anwendungsfall einer correlated Subquery typischerweise am besten passt.

Diese Tabelle ersetzt keine Messung, sondern liefert eine erste Einschaetzung. Der zuverlaessige Nachweis, ob eine Umschreibung notwendig ist, entsteht immer erst durch den Vergleich der tatsaechlichen Ausfuehrungsplaene auf realistischen Datenmengen.

10. Zusammenfassung

Eine correlated Subquery unterscheidet sich von einer gewoehnlichen Subquery durch genau einen Punkt: sie referenziert eine Spalte der aeusseren Abfrage und wird deshalb logisch fuer jede aeussere Zeile neu ausgewertet. Dieses mentale Modell erklaert sowohl ihre Staerke bei Zeile-fuer-Zeile-Berechnungen als auch ihr Risiko, wenn der Optimizer die wiederholte Ausfuehrung nicht dekorrelieren kann und stattdessen ein Nested-Loop-Plan mit quadratischer Komplexitaet entsteht.

Existenzpruefungen mit EXISTS profitieren meist von guter automatischer Optimierung und muessen selten umgeschrieben werden. Skalare und aggregierende correlated Subqueries lassen sich zuverlaessig in eine vorab aggregierende derived table oder einen Join umschreiben, waehrend Rang- und Vergleichsberechnungen fast immer besser als Window-Funktion formuliert werden. Der Ausfuehrungsplan mit EXPLAIN ANALYZE ist in jedem Fall die einzig verlaessliche Grundlage fuer die Entscheidung, ob eine Umschreibung ueberhaupt noetig ist.

11. FAQ: Correlated Subqueries verstehen