LATERAL JOIN

What is a LATERAL JOIN?

LATERAL allows a subquery in the FROM clause to reference columns from tables that appear before it in the FROM list. Without LATERAL, subqueries in FROM cannot see the outer query's columns.

Syntax

SELECT a.*, b.*
FROM table_a a
CROSS JOIN LATERAL (
  SELECT * FROM table_b b
  WHERE b.a_id = a.id
  ORDER BY b.created_at DESC
  LIMIT 3
) AS b;

SELECT a.*, b.*
FROM table_a a
LEFT JOIN LATERAL (
  SELECT * FROM table_b b
  WHERE b.a_id = a.id
  LIMIT 1
) AS b ON true;

When to Use

• Getting the top-N related rows per parent row
• Replacing correlated subqueries in SELECT for better performance
• Calling set-returning functions with per-row arguments

Key Points

1. Row-Dependent — The LATERAL subquery can use columns from preceding FROM items.
2. CROSS JOIN LATERAL — Excludes parent rows with no matches.
3. LEFT JOIN LATERAL — Keeps all parent rows (NULLs for no matches).
4. ON true — For LEFT JOIN LATERAL, use ON true since the condition is inside the subquery.
5. Performance — Often more efficient than correlated subqueries in SELECT.