LATERAL/FLATTEN explodes arrays — re-introducing fan-out deliberately, closed by re-aggregation (P06 inverted).

A plain subquery in FROM is sealed — it can't reference sibling FROM items; LATERAL unseals it, making the subquery a function of the current row. That turns 'top N per group' from a window-then-filter into a literal statement of intent, and generalizes where windows strain: per-row LIMITs with different N, nearest-timestamp lookups, sampling per entity. Costs are per-row conceptually (engines optimize; plans tell). UNNEST is a lateral in disguise.

SELECT
  s,
  item
FROM
  sales ORDER BY
      n DESC
    LIMIT
      2
  ;

SELECT
  sh.s,
  t.item
FROM
  shops sh,
  LATERAL (
    SELECT
      item,
      n
    FROM
      sales sa
    WHERE
      sa.s = sh.s
    ORDER BY
      n DESC
    LIMIT
      2
  ) t
ORDER BY
  sh.s,
  t.n DESC;

A global LIMIT 2 returned the two overall top sellers, not each shop's — a LATERAL subquery in FROM sees the current row, so 'for each shop, its top 2' is one statement of intent; a sealed subquery can't.

LATERAL for per-row-question shapes; the window spelling where the engine lacks LATERAL; plan-check on big inputs.

LATERAL (Postgres/DuckDB), CROSS/OUTER APPLY (SQL Server) — same concept.