A normal subquery in FROM can’t reference columns from another table in the same query — LATERAL lifts that restriction, so the subquery runs once per outer row, like a per-row correlated function call. It’s the standard SQL name for what SQL Server calls CROSS APPLY / OUTER APPLY.

-- PostgreSQL, MySQL 8.0.14+, DuckDB: top 3 orders per customer, as a per-row subquery
SELECT c.id, c.name, top_orders.id AS order_id, top_orders.amount
FROM customers c
CROSS JOIN LATERAL (
  SELECT id, amount
  FROM orders o
  WHERE o.customer_id = c.id
  ORDER BY amount DESC
  LIMIT 3
) AS top_orders;

Use LEFT JOIN LATERAL ... ON true to keep customers even when the subquery returns zero rows (a plain/CROSS JOIN LATERAL drops them, like an inner join):

SELECT c.id, c.name, top_orders.id AS order_id, top_orders.amount
FROM customers c
LEFT JOIN LATERAL (
  SELECT id, amount
  FROM orders o
  WHERE o.customer_id = c.id
  ORDER BY amount DESC
  LIMIT 3
) AS top_orders ON true;

-- Snowflake: same idea, comma join instead of CROSS JOIN
SELECT c.id, c.name, top_orders.id AS order_id, top_orders.amount
FROM customers c, LATERAL (
  SELECT id, amount
  FROM orders o
  WHERE o.customer_id = c.id
  ORDER BY amount DESC
  LIMIT 3
) top_orders;

Dialect notes:

SQLite has no LATERAL at all. Fall back to the ROW_NUMBER() + subquery pattern from the top-N-per-group recipe instead.
BigQuery has no LATERAL keyword either. For “top N per group” specifically, use QUALIFY ROW_NUMBER() OVER (...) <= 3 (see the top-N recipe); for a per-row call into an array/table function, use CROSS JOIN UNNEST(...) on an array column instead.
MySQL needs 8.0.14+, and the LATERAL keyword is required — a plain derived table can’t reference c.id without it.

Reach for LATERAL only when a window function isn’t enough. For plain “top N rows per group”, ROW_NUMBER()/QUALIFY (see the top-N-per-group recipe) is simpler and works in all six dialects. LATERAL earns its keep when the per-row logic is more than a rank filter — multiple aggregates from a different grain, a call into a table-valued function per row, or referencing more than one preceding table’s columns at once.

Run a subquery per row with LATERAL / CROSS APPLY