LATERAL Joins fuer komplexe, korrelierte Abfragen

1. Warum ein normaler Join hier nicht ausreicht

Ein gewoehnlicher JOIN verknuepft zwei Tabellen ueber eine Bedingung, aber die rechte Seite des Joins ist eine feste Menge, die unabhaengig von der aktuellen Zeile der linken Seite berechnet wird. Eine Subquery in der FROM-Klausel unterliegt derselben Einschraenkung: Sie wird einmal fuer die gesamte Abfrage ausgewertet und kann nicht auf einzelne Spaltenwerte der umgebenden Zeilen zugreifen. Genau diese Einschraenkung loest ein LATERAL Join auf.

Ein LATERAL Join markiert eine Subquery so, dass sie fuer jede Zeile der vorangehenden Tabelle neu ausgewertet werden darf, mit Zugriff auf die Spaltenwerte genau dieser Zeile. Das ist der entscheidende Unterschied zu einer normalen Subquery in FROM: Ohne LATERAL muesste die Subquery vollstaendig unabhaengig von der aeusseren Abfrage sein, mit LATERAL wird sie effektiv zu einer Funktion, die pro Zeile der linken Tabelle einmal aufgerufen wird.

Der Name LATERAL Join stammt aus dem SQL-Standard, PostgreSQL, MySQL 8.0.14+ und andere implementieren ihn unter genau diesem Schluesselwort. SQL Server kennt dieselbe Funktionalitaet unter den Namen CROSS APPLY und OUTER APPLY, die semantisch LATERAL mit INNER JOIN beziehungsweise LEFT JOIN entsprechen. Oracle unterstuetzt seit Version 12c ebenfalls LATERAL und alternativ CROSS APPLY.

2. Die Syntax von LATERAL und CROSS APPLY

Die Syntax eines LATERAL Joins in PostgreSQL folgt dem Muster FROM tabelle_a a, LATERAL (SELECT ... FROM tabelle_b WHERE tabelle_b.spalte = a.spalte) b oder explizit mit JOIN LATERAL ... ON true. Die Klammer der Subquery kann dabei auf Spalten von a zugreifen, was in einer normalen, nicht-lateralen Subquery zu einem Fehler fuehren wuerde, weil a zu diesem Zeitpunkt im Abfrageplan noch nicht in Scope waere.

In SQL Server entfaellt das Schluesselwort LATERAL, stattdessen wird CROSS APPLY oder OUTER APPLY direkt anstelle von JOIN verwendet, ohne zusaetzliche ON-Bedingung, weil die Korrelation bereits innerhalb der referenzierten Subquery steht. Das Verhalten ist funktional identisch: Fuer jede Zeile der linken Tabelle wird die rechte Subquery mit dem aktuellen Zeilenkontext neu ausgewertet.

-- PostgreSQL / MySQL 8: LATERAL join syntax
SELECT c.customer_id, c.name, recent.order_id, recent.amount
FROM customers c
JOIN LATERAL (
    SELECT order_id, amount
    FROM orders o
    WHERE o.customer_id = c.customer_id
    ORDER BY o.order_date DESC
    LIMIT 1
) recent ON true;

-- SQL Server: CROSS APPLY syntax, functionally equivalent
SELECT c.customer_id, c.name, recent.order_id, recent.amount
FROM customers c
CROSS APPLY (
    SELECT TOP 1 order_id, amount
    FROM orders o
    WHERE o.customer_id = c.customer_id
    ORDER BY o.order_date DESC
) recent;

3. Top-N pro Gruppe: der klassische Anwendungsfall

Der mit Abstand haeufigste Anwendungsfall fuer einen LATERAL Join ist die Top-N-pro-Gruppe-Abfrage: Fuer jede Zeile einer Tabelle sollen die N zugehoerigen Zeilen einer zweiten Tabelle gefunden werden, sortiert nach einem Kriterium, etwa die drei neuesten Bestellungen pro Kunde oder die zwei guenstigsten Angebote pro Produkt. Diese Aufgabe ist mit einem gewoehnlichen JOIN nur ueber Umwege loesbar, mit einem LATERAL Join dagegen direkt und lesbar.

Die Subquery innerhalb des LATERAL Joins enthaelt dabei genau die Logik, die man auch fuer einen einzelnen Kunden von Hand schreiben wuerde: filtern nach der korrelierten ID, sortieren nach dem gewuenschten Kriterium, mit LIMIT N beziehungsweise TOP N begrenzen. Diese pro-Kunde-Logik wird dann automatisch fuer jede Zeile der aeusseren Tabelle wiederholt, ohne dass eine Window Function mit anschliessendem Filter auf den Rang noetig ist.

-- Top 3 most recent orders per customer, using LATERAL
SELECT c.customer_id, c.name, o.order_id, o.order_date, o.amount
FROM customers c
JOIN LATERAL (
    SELECT order_id, order_date, amount
    FROM orders o
    WHERE o.customer_id = c.customer_id
    ORDER BY o.order_date DESC
    LIMIT 3
) o ON true
ORDER BY c.customer_id, o.order_date DESC;

4. LEFT JOIN LATERAL und OUTER APPLY fuer leere Ergebnisse

Ein JOIN LATERAL verhaelt sich standardmaessig wie ein INNER JOIN: Kunden ohne passende Zeile in der korrelierten Subquery, etwa Kunden ohne Bestellungen, fallen komplett aus dem Ergebnis. Wer alle Kunden sehen moechte, auch jene ohne Bestellhistorie, benoetigt LEFT JOIN LATERAL ... ON true in PostgreSQL beziehungsweise OUTER APPLY in SQL Server. In beiden Faellen erscheint der Kunde weiterhin in der Ausgabe, die Spalten der Subquery sind dann mit NULL befuellt.

Diese Unterscheidung ist in der Praxis kritisch fuer die Korrektheit von Berichten: Eine Abfrage, die eigentlich alle Kunden inklusive ihrer letzten Aktivitaet zeigen soll, aber versehentlich JOIN LATERAL statt LEFT JOIN LATERAL verwendet, liefert ein Ergebnis mit fehlenden Kunden, ohne dass ein Fehler geworfen wird. Der Fehler faellt oft erst auf, wenn eine Zeilenzahl im Bericht nicht mit der bekannten Gesamtkundenzahl uebereinstimmt.

-- LEFT JOIN LATERAL keeps customers with no orders, columns become NULL
SELECT c.customer_id, c.name, o.order_id, o.order_date
FROM customers c
LEFT JOIN LATERAL (
    SELECT order_id, order_date
    FROM orders o
    WHERE o.customer_id = c.customer_id
    ORDER BY o.order_date DESC
    LIMIT 1
) o ON true;

-- SQL Server equivalent: OUTER APPLY
SELECT c.customer_id, c.name, o.order_id, o.order_date
FROM customers c
OUTER APPLY (
    SELECT TOP 1 order_id, order_date
    FROM orders o
    WHERE o.customer_id = c.customer_id
    ORDER BY o.order_date DESC
) o;

5. LATERAL mit Tabellenfunktionen kombinieren

Ein weiterer starker Anwendungsfall fuer einen LATERAL Join ist die Kombination mit mengenwertigen Funktionen, die selbst pro Zeile unterschiedliche Argumente erhalten sollen, etwa das Aufsplitten einer kommagetrennten Spalte mit unnest in PostgreSQL oder STRING_SPLIT in SQL Server. Ohne LATERAL beziehungsweise CROSS APPLY muesste die Funktion mit einem festen Argument aufgerufen werden, mit LATERAL erhaelt jede Zeile ihren eigenen, korrelierten Funktionsaufruf.

Auch parametrisierte Tabellenfunktionen, die eine variable Anzahl Argumente pro Zeile benoetigen, etwa eine Distanzberechnung mit den Koordinaten der jeweiligen Zeile als Parameter, lassen sich nur mit LATERAL sauber ausdruecken. Diese Faelle sind seltener als Top-N-pro-Gruppe, aber ohne LATERAL praktisch nicht in einer einzelnen SQL-Anweisung loesbar, ohne auf prozedurale Erweiterungen auszuweichen.

-- Splitting a comma-separated column per row using LATERAL and unnest
SELECT p.product_id, p.product_name, tag
FROM products p,
LATERAL unnest(string_to_array(p.tags, ',')) AS tag
WHERE p.tags IS NOT NULL;

-- SQL Server: CROSS APPLY with STRING_SPLIT per row
SELECT p.product_id, p.product_name, tag.value
FROM products p
CROSS APPLY STRING_SPLIT(p.tags, ',') AS tag
WHERE p.tags IS NOT NULL;

6. LATERAL gegen die Window-Function-Loesung

Die Top-N-pro-Gruppe-Aufgabe laesst sich auch ohne LATERAL Join mit einer Window Function loesen: ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) in einer CTE, gefolgt von einem WHERE rn <= 3 in der aeusseren Abfrage. Beide Ansaetze liefern bei korrekter Formulierung dasselbe Ergebnis, unterscheiden sich aber in Ausfuehrungsstrategie und Lesbarkeit je nach Situation.

Der LATERAL Join ist meist die klarere Wahl, wenn N klein ist und der Optimierer den LIMIT-Wert direkt in die Subquery hineinschieben kann, was oft einen Index-Scan mit fruehem Abbruch pro Gruppe ermoeglicht, statt die gesamte Tabelle zu nummerieren. Die Window-Function-Loesung wiederum ist im Vorteil, wenn zusaetzlich zur Rangfolge weitere Fensterberechnungen wie laufende Summen benoetigt werden, da sie ohnehin schon einen vollstaendigen Scan mit Sortierung durchfuehrt und diese Zusatzinformation ohne weiteren Join mitliefert.

-- Same top-3-per-customer result using a window function instead of LATERAL
WITH ranked_orders AS (
    SELECT order_id, customer_id, order_date, amount,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id ORDER BY order_date DESC
           ) AS rn
    FROM orders
)
SELECT customer_id, order_id, order_date, amount
FROM ranked_orders
WHERE rn <= 3
ORDER BY customer_id, order_date DESC;

-- The window function scans and sorts every row in every partition,
-- while the LATERAL version can stop after 3 rows per customer
-- if an index on (customer_id, order_date DESC) exists

7. Performance und Ausfuehrungsplan

Der grosse praktische Vorteil eines LATERAL Joins bei kleinen N-Werten ist, dass der Optimierer die LIMIT-Klausel innerhalb der Subquery ausnutzen kann. Bei einem passenden Index auf (customer_id, order_date DESC) muss die Datenbank fuer jeden Kunden nur die ersten drei Zeilen des Index lesen und kann dann sofort abbrechen, statt alle Bestellungen dieses Kunden zu lesen und zu sortieren. Bei tausenden Kunden mit jeweils hunderten Bestellungen ist dieser Unterschied erheblich.

Die Window-Function-Alternative muss dagegen in der Regel alle Zeilen aller Gruppen einmal vollstaendig einlesen und sortieren, bevor der Rangfilter angewendet werden kann, da ROW_NUMBER() erst nach der vollstaendigen Partitionierung und Sortierung ausgewertet wird. Bei grossem N relativ zur Gruppengroesse gleicht sich dieser Unterschied wieder an, weshalb ein Blick in EXPLAIN ANALYZE bei beiden Varianten vor einer endgueltigen Entscheidung ratsam ist.

8. Unterschiede zwischen den Datenbanksystemen

PostgreSQL unterstuetzt LATERAL seit Version 9.3 vollstaendig, sowohl explizit mit JOIN LATERAL als auch implizit bei jeder Subquery in FROM, die auf vorherige Tabellen der FROM-Klausel zugreift, sofern das Schluesselwort gesetzt ist. MySQL unterstuetzt LATERAL seit Version 8.0.14, mit denselben Grundregeln wie PostgreSQL. SQL Server hat kein LATERAL-Schluesselwort, bietet aber mit CROSS APPLY und OUTER APPLY seit SQL Server 2005 dieselbe Funktionalitaet unter anderem Namen.

Oracle unterstuetzt LATERAL seit Version 12c und bietet zusaetzlich CROSS APPLY und OUTER APPLY als Alternativsyntax, was den Umstieg von SQL Server erleichtert. Wichtig fuer portablen Code: Wer zwischen PostgreSQL und SQL Server wechseln muss, kann nicht einfach LATERAL durch CROSS APPLY ersetzen, ohne die ON true-Bedingung zu entfernen und LIMIT durch TOP zu ersetzen, die Grundlogik bleibt aber identisch.

9. Typische Fehler bei LATERAL Joins

Der haeufigste Fehler ist das Vergessen von ON true beziehungsweise einer aequivalenten Bedingung nach der lateralen Subquery in PostgreSQL, wenn die eigentliche Korrelationsbedingung bereits innerhalb der Klammer steht. Ohne diese Bedingung meldet die Datenbank einen Syntaxfehler, weil ein expliziter JOIN LATERAL formal eine ON-Klausel erwartet, selbst wenn diese inhaltlich nichts mehr zu filtern hat.

Ein zweiter Fehler ist die Verwechslung von JOIN LATERAL und LEFT JOIN LATERAL in Berichten, die vollstaendig sein muessen, wie im Abschnitt zu Outer-Lateral-Joins beschrieben. Ein dritter Fehler ist der Versuch, einen LATERAL Join auf eine Subquery anzuwenden, die selbst bereits eine Aggregation ueber mehrere Zeilen der aeusseren Tabelle vornimmt, was zu unerwarteten, meist zu niedrigen Aggregatwerten fuehrt, weil die Aggregation pro Zeile statt pro Gruppe berechnet wird.

10. Zusammenfassung

Ein LATERAL Join loest ein Problem, an dem gewoehnliche Joins und Subqueries scheitern: den Zugriff auf Spaltenwerte der aktuellen Zeile innerhalb einer korrelierten Subquery in FROM. Damit werden Top-N-pro-Gruppe-Abfragen, dynamische Tabellenfunktionsaufrufe und mehrspaltige, pro Zeile berechnete Ergebnisse direkt und lesbar formulierbar, in PostgreSQL und MySQL 8 mit dem Schluesselwort LATERAL, in SQL Server mit CROSS APPLY und OUTER APPLY.

Gegenueber einer Window-Function-Loesung ist ein LATERAL Join bei kleinen N-Werten oft die performantere Wahl, weil der Optimierer den LIMIT in die Subquery hineinschieben und einen Index-Scan mit fruehem Abbruch nutzen kann. Bei Bedarf an zusaetzlichen Fensterberechnungen ist die Window-Function-Variante im Vorteil. Die Wahl zwischen JOIN LATERAL und LEFT JOIN LATERAL entscheidet, ob Zeilen ohne Treffer in der Subquery aus dem Ergebnis fallen oder mit NULL-Werten erhalten bleiben.

11. FAQ: LATERAL Joins