LATERAL join for correlated subqueries in PostgreSQL

Introduction

LATERAL join lets you run a subquery for each row of a table, using values from that row. It helps when you want to get related data that depends on each row.

You want to find the top 3 recent orders for each customer.

You need to get the highest scoring review for each product.

You want to list employees along with their latest project details.

You want to join a table with a function that uses columns from that table.

Syntax

PostgreSQL

SELECT columns
FROM table1
JOIN LATERAL (
  SELECT columns
  FROM table2
  WHERE table2.column = table1.column
  LIMIT n
) alias ON true;

LATERAL allows the subquery to use columns from the main query's current row.

The ON condition is often ON true because the subquery is already filtered inside.

Examples

Get the most recent order for each customer.

PostgreSQL

SELECT c.customer_id, o.order_id, o.order_date
FROM customers c
JOIN LATERAL (
  SELECT order_id, order_date
  FROM orders
  WHERE orders.customer_id = c.customer_id
  ORDER BY order_date DESC
  LIMIT 1
) o ON true;

Get the highest rated review for each product.

PostgreSQL

SELECT p.product_id, r.review_text
FROM products p
JOIN LATERAL (
  SELECT review_text
  FROM reviews
  WHERE reviews.product_id = p.product_id
  ORDER BY rating DESC
  LIMIT 1
) r ON true;

Sample Program

This query finds each customer's latest order by using a LATERAL join to run a subquery for each customer.

PostgreSQL

CREATE TABLE customers (customer_id INT, name TEXT);
CREATE TABLE orders (order_id INT, customer_id INT, order_date DATE);

INSERT INTO customers VALUES (1, 'Alice'), (2, 'Bob');
INSERT INTO orders VALUES
  (101, 1, '2024-01-10'),
  (102, 1, '2024-02-15'),
  (103, 2, '2024-01-20');

SELECT c.customer_id, c.name, o.order_id, o.order_date
FROM customers c
JOIN LATERAL (
  SELECT order_id, order_date
  FROM orders
  WHERE orders.customer_id = c.customer_id
  ORDER BY order_date DESC
  LIMIT 1
) o ON true
ORDER BY c.customer_id;

Important Notes

LATERAL is useful when the subquery needs to know about the current row from the main table.

Without LATERAL, the subquery cannot refer to columns from the outer query.

Use LIMIT inside the lateral subquery to get top results per row.

Summary

LATERAL join runs a subquery for each row of the main table using that row's data.

It helps get related data that depends on each row, like top orders or best reviews.

The subquery can refer to outer query columns, making it very flexible.