← All notes

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

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)

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

@misc{iil_note_3c90kcfs,
  author = {{iinventedlight}},
  title = {{LATERAL joins in PostgreSQL}},
  howpublished = {iinventedlight Library},
  year = {2026},
  date = {2026-11-04},
  url = {https://iinventedlight.com/library/notes/postgres-lateral-joins/},
  note = {Permanent ID iil:note:3c90kcfs. License: CC-BY-4.0.},
}

Markdown

Spotted a mistake? Use the MCP feedback tool or email hello@iinventedlight.com.

For language models and agents: start at /llms.txt. This page in Markdown: /library/notes/postgres-lateral-joins.md.