LATERAL: a subquery for every row

The problem

“The two most valuable items in each order” is hard with what you have so far. LIMIT 2 applies to a whole query, not to each order. A subquery in FROM that refers to the outer order is rejected:

SELECT o.id FROM orders o, (SELECT * FROM order_items oi WHERE oi.order_id = o.id) x;

ERROR:  invalid reference to FROM-clause entry for table "o"
DETAIL:  There is an entry for table "o", but it cannot be referenced from this part of the query.
HINT:  To reference that table, you must mark this subquery with LATERAL.

PostgreSQL’s hint is the answer.

LATERAL runs the subquery per row

SELECT o.id AS order_id, top.product, top.line_total
FROM orders o
CROSS JOIN LATERAL (
  SELECT p.name AS product, oi.quantity * oi.unit_price AS line_total
  FROM order_items oi JOIN products p ON p.id = oi.product_id
  WHERE oi.order_id = o.id
  ORDER BY line_total DESC
  LIMIT 2
) AS top
WHERE o.customer_id = 1
ORDER BY o.id, top.line_total DESC;

 order_id |           product           | line_total
----------+-----------------------------+------------
      101 | Mechanical keyboard         |    4500.00
      101 | Wireless mouse              |    1200.00
      103 | USB-C hub                   |    2499.00
      103 | Laptop stand                |    1899.00
      108 | Noise-cancelling headphones |   11999.00

For each of Asha’s orders, the subquery finds that order’s items, sorts them and keeps two. Order 103 has three items and shows two; order 108 has one and shows one.

Keep rows where the subquery finds nothing

CROSS JOIN LATERAL drops an outer row when the subquery returns nothing, like an inner join. LEFT JOIN LATERAL … ON true keeps it. Each customer’s most recent order, including customers who have none:

SELECT c.name, last_order.id, last_order.ordered_at
FROM customers c
LEFT JOIN LATERAL (
  SELECT o.id, o.ordered_at FROM orders o
  WHERE o.customer_id = c.id
  ORDER BY o.ordered_at DESC
  LIMIT 1
) AS last_order ON true
ORDER BY c.id;

     name      | id  |       ordered_at
---------------+-----+------------------------
 Asha Rao      | 108 | 2025-07-03 06:00:00+00
 Ben Okafor    | 106 | 2025-06-01 07:20:00+00
 …
 Elena Petrova |     |
 …
 Hugo Martin   |     |

Compare this with the correlated max() version in Practice: compose queries: that one returned two rows for a customer with two orders at exactly the same time. LIMIT 1 returns one, always.

Check yourself

1. Why can’t a plain subquery in FROM refer to o.id from a table earlier in the same FROM?

   Show answer

   Without LATERAL, each subquery in FROM is evaluated independently, before the rows of the other FROM items exist. LATERAL makes it run for each row of the items before it.

   I could explain this without looking

2. What is the difference between CROSS JOIN LATERAL and LEFT JOIN LATERAL … ON true?

   Show answer

   CROSS JOIN LATERAL drops outer rows for which the subquery returns nothing. LEFT JOIN LATERAL … ON true keeps them, with NULLs.

   I could explain this without looking

3. Why does a LATERAL subquery need a supporting index on large tables?

   Show answer

   It runs once per outer row. Without an index matching its filter and sort, every run may scan a large table.

   I could explain this without looking