---
source: https://iinventedlight.com/library/notes/postgres-lateral-joins.md
title: "LATERAL joins in PostgreSQL"
license: CC-BY-4.0
license_url: https://creativecommons.org/licenses/by/4.0/
source_license: "PostgreSQL License; prose restates cited source lines"
attribution: "Summary by iinventedlight (AI-assisted). Sources: PostgreSQL Global Development Group, PostgreSQL: Documentation: 18: 7.2. Table Expressions, PostgreSQL License; PostgreSQL Global Development Group, PostgreSQL: Documentation: 18: SELECT, PostgreSQL License."
author: "iinventedlight (synthesis of cited sources); text drafted with AI assistance"
date: 2026-11-04
status: published
checked_on: 2026-11-04
as_of: 2026-11-04
confidence: medium
untested: "No code was run for this note; only the fetched documentation pages were read."
category: coding-references
topic_area: "code-ai-eng: PostgreSQL"
tags: ['postgresql', 'sql', 'joins', 'lateral']
sources:
  - id: S1
    title: "PostgreSQL: Documentation: 18: 7.2. Table Expressions"
    publisher: PostgreSQL Global Development Group
    url: https://www.postgresql.org/docs/current/queries-table-expressions.html
    accessed: 2026-11-04
    license: "PostgreSQL License"
  - id: S2
    title: "PostgreSQL: Documentation: 18: SELECT"
    publisher: PostgreSQL Global Development Group
    url: https://www.postgresql.org/docs/current/sql-select.html
    accessed: 2026-11-04
    license: "PostgreSQL License"
---
# LATERAL joins in PostgreSQL

**Answer:** A `LATERAL` subquery in FROM can reference columns from items listed before it, so it runs once per row on its left. It is the standard way to get top-N per group or to call a set-returning function for each row.

## Top 3 orders per customer

```sql
SELECT c.name, o.id, o.total
FROM customers c
CROSS JOIN LATERAL (
  SELECT id, total FROM orders
  WHERE orders.customer_id = c.id
  ORDER BY total DESC LIMIT 3
) o;
```


## Functions

LATERAL can also precede a function-call FROM item, but in this case it is a noise word, because the function expression can refer to earlier FROM items in any case. (S2)

```sql
SELECT p.id, t.tag FROM posts p, unnest(p.tags) AS t(tag);
```

## Join refresher

- INNER is the default join; LEFT, RIGHT and FULL imply outer joins.
- `CROSS JOIN` equals `INNER JOIN ... ON TRUE`.
- INNER and OUTER joins need exactly one of ON, USING or NATURAL.

## Claims
- id: C1
  claim: Table functions can be preceded by LATERAL, but it's optional for functions.
  source: S1
  evidence: "but for functions the key word is optional"
- id: C2
  claim: A LATERAL item can appear at the top level or within a JOIN tree.
  source: S1
  evidence: "A LATERAL item can appear at the top level in the FROM list, or within"
- id: C3
  claim: INNER is the default; LEFT, RIGHT, and FULL imply an outer join.
  source: S1
  evidence: "INNER is the default; LEFT, RIGHT, and FULL imply an outer join."
- id: C4
  claim: LATERAL before a function-call FROM item is a noise word.
  source: S2
  evidence: "LATERAL can also precede a function-call FROM item, but in this case it is a"

## Sources
- [S1] PostgreSQL: Documentation: 18: 7.2. Table Expressions. PostgreSQL Global Development Group. https://www.postgresql.org/docs/current/queries-table-expressions.html . License: PostgreSQL License. Accessed 2026-11-04.
- [S2] PostgreSQL: Documentation: 18: SELECT. PostgreSQL Global Development Group. https://www.postgresql.org/docs/current/sql-select.html . License: PostgreSQL License. Accessed 2026-11-04.

License: this note is CC BY 4.0 (https://creativecommons.org/licenses/by/4.0/). Summary by iinventedlight (AI-assisted).