LATERAL subqueries in PostgreSQL

Introduction

LATERAL subqueries let you use columns from a table in a subquery next to it. This helps when you want to find related data for each row easily.

You want to find the top 1 related record for each row in a table.

You need to join each row with a set of rows that depend on that row's values.

You want to calculate something for each row using a subquery that uses that row's data.

You want to simplify complex joins by using a subquery that can see the outer row.

Syntax

PostgreSQL

SELECT columns
FROM table1
CROSS JOIN LATERAL (
  SELECT columns
  FROM table2
  WHERE table2.column = table1.column
  LIMIT 1
) alias;

LATERAL allows the subquery to access columns from the table before it in the FROM clause.

It is often used with CROSS JOIN or INNER JOIN.

Examples

Find the latest order for each customer.

PostgreSQL

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

Get the department name for each employee using LATERAL join.

PostgreSQL

SELECT e.employee_id, d.department_name
FROM employees e
JOIN LATERAL (
  SELECT department_name
  FROM departments d
  WHERE d.department_id = e.department_id
) d ON true;

Sample Program

This query finds the most recent book for each author using a LATERAL subquery.

PostgreSQL

CREATE TABLE authors (
  author_id SERIAL PRIMARY KEY,
  name TEXT
);

CREATE TABLE books (
  book_id SERIAL PRIMARY KEY,
  author_id INT REFERENCES authors(author_id),
  title TEXT,
  published_year INT
);

INSERT INTO authors (name) VALUES
('Alice'), ('Bob');

INSERT INTO books (author_id, title, published_year) VALUES
(1, 'Book A1', 2020),
(1, 'Book A2', 2022),
(2, 'Book B1', 2021);

SELECT a.name, b.title, b.published_year
FROM authors a
CROSS JOIN LATERAL (
  SELECT title, published_year
  FROM books
  WHERE author_id = a.author_id
  ORDER BY published_year DESC
  LIMIT 1
) b;

Important Notes

LATERAL subqueries run once per row of the preceding table.

They are very useful when the subquery depends on the outer query's row.

Summary

LATERAL lets subqueries use columns from tables before them in FROM.

Use it to get related or calculated data per row easily.

Works well with CROSS JOIN or INNER JOIN.