---
title: "LATERAL JOIN"
description: "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."
canonical: "https://motherduck.com/glossary/lateral-join/"
related:
  - title: "Running dual execution (or hybrid) queries | MotherDuck Docs"
    url: "https://motherduck.com/docs/key-tasks/running-hybrid-queries/"
  - title: "DuckLake Architecture Deep Dive"
    url: "https://motherduck.com/blog/ducklake-architecture-deep-dive/"
  - title: "DuckDB SQL | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/"
---

# 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.

```sql
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`.

<glossary-callout guide="duckdb-cheatsheet-full" />

## 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
```sql
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.