CROSS JOIN and LATERAL

CROSS JOIN pairs every row with every row; LATERAL lets a subquery refer to the row on its left.

Syntax

FROM a CROSS JOIN b                 -- |a| × |b| rows
FROM a, b                           -- same thing (old style)
FROM a CROSS JOIN LATERAL (SELECT ... WHERE x = a.x ... LIMIT n) s
FROM a LEFT JOIN LATERAL (...) s ON true

A cross join returns the Cartesian product: every combination. 10 × 10 rows = 100 rows. Use it on purpose to build grids (every product × every month) and fill gaps with LEFT JOIN.
An accidental Cartesian product (missing ON, or FROM a, b without a WHERE link) is a classic bug that explodes row counts.
Cross joining a one-row result (e.g. a total) attaches that value to every row.
LATERAL: a subquery in FROM normally cannot see other FROM items. With LATERAL it is evaluated per left row and may reference its columns — the neat way to get top N per group. Use LEFT JOIN LATERAL ... ON true to keep left rows with no result. Set-returning function calls in FROM are implicitly lateral.

Examples

Grid of categories × statuses starter

SELECT c.category, s.status
FROM (SELECT DISTINCT category FROM products) c
CROSS JOIN (VALUES ('processing'), ('shipped'), ('delivered'), ('cancelled')) AS s(status)
ORDER BY c.category, s.status;

Every category paired with every status — a scaffold to left join counts onto.

Share of total with a one-row cross join starter

SELECT p.category, sum(o.quantity) AS units,
       round(100.0 * sum(o.quantity) / t.total_units, 1) AS pct_units
FROM orders o
JOIN products p USING (product_id)
CROSS JOIN (SELECT sum(quantity) AS total_units FROM orders) t
GROUP BY p.category, t.total_units
ORDER BY units DESC;

The single total row is attached to every row before grouping.

Top 2 products per category with LATERAL shop

SELECT c.category_name, top.product_name, top.unit_price
FROM categories c
CROSS JOIN LATERAL (
  SELECT p.product_name, p.unit_price
  FROM products p
  WHERE p.category_id = c.category_id
  ORDER BY p.unit_price DESC, p.product_id
  LIMIT 2
) AS top
ORDER BY c.category_name, top.unit_price DESC;

The subquery runs once per category; categories without products disappear (use LEFT JOIN LATERAL ... ON true to keep them).

Pitfalls

An accidental cross join from a missing join condition.
Cross joining two large tables (row count multiplies).
Using CROSS JOIN LATERAL when left rows with no match must stay (use LEFT JOIN LATERAL ... ON true).

Other databases

For orientation only: SQL Quest executes PostgreSQL.

SQL Server: LATERAL is written CROSS APPLY / OUTER APPLY.
Oracle: Supports LATERAL and CROSS/OUTER APPLY (12c+).
MySQL: LATERAL derived tables since 8.0.14.