LATERAL JOIN

A LATERAL join lets a subquery in the FROM clause reference columns from earlier tables in the same FROM clause, enabling per-row correlated logic without a scalar subquery.

Overview

LATERAL allows a subquery inside a FROM clause to reference columns from tables that appear earlier in the same FROM clause -- something an ordinary subquery in FROM cannot do, since normal subqueries are evaluated independently of the rest of the query. This effectively gives you a per-row "for each" loop inside SQL, useful for unnesting, top-N-per-group logic, and calling table-returning functions with arguments that vary per outer row.

Copy code
SELECT *
FROM range(3) t(i), LATERAL (SELECT i + 1) t2(j);

Here, the subquery SELECT i + 1 references i from the table before it in the FROM clause -- only possible because of LATERAL.

DuckDB detects LATERAL automatically

Unlike PostgreSQL, where the LATERAL keyword must be written explicitly for a subquery to reference preceding tables, DuckDB automatically detects when a FROM-clause subquery needs lateral semantics and enables it -- the LATERAL keyword is optional in DuckDB, though writing it explicitly still documents intent clearly.

Common use case: top-N per group

Copy code
SELECT c.customer_id, top_orders.order_id, top_orders.order_total
FROM customers c,
LATERAL (
  SELECT order_id, order_total
  FROM orders o
  WHERE o.customer_id = c.customer_id
  ORDER BY order_total DESC
  LIMIT 3
) AS top_orders;

This returns each customer's 3 largest orders -- a pattern that's awkward to express with a plain window function alone once you also need to join back other per-customer context.

Lateral joins vs correlated scalar subqueries

A correlated subquery in the SELECT list can only return a single value per row. A lateral join in FROM can return multiple rows and columns per outer row, which is what makes it strictly more flexible for "for each outer row, compute a small result set" logic.

Related terms

JOIN clause →

How do SQL JOINs work? Learn INNER, LEFT, RIGHT, FULL OUTER, CROSS, and ASOF joins with syntax examples — plus how to join directly against CSV and Parquet files.

EXISTS →

EXISTS tests whether a subquery returns at least one row, commonly used to check for the presence of related records without caring about their actual values.

USING clause →

The USING clause specifies join columns by name when both tables share identically named columns, joining on equality and returning a single copy of each shared column.

IN operator →

The IN operator tests whether a value matches any value in a given list or subquery, providing a concise alternative to multiple OR conditions.

NATURAL JOIN →

A NATURAL JOIN automatically joins two tables on all columns that share the same name, without an explicit ON or USING clause.

WHERE clause →

The WHERE clause filters rows in a SQL query based on a boolean condition, keeping only rows for which the condition evaluates to true.

FAQS

No -- DuckDB automatically detects when a FROM-clause subquery needs to reference an earlier table and applies lateral semantics, though writing LATERAL explicitly is still good practice for readability.

A correlated subquery in the SELECT list can only return one value per outer row; a lateral join in the FROM clause can return multiple rows and columns per outer row, similar to a per-row loop.

Fetching the top N related rows per group (e.g., each customer's 3 largest orders) or calling a table-returning function whose arguments depend on a preceding table's columns.