Lateral Join in PostgreSQL: What It Is and How to Use It

In PostgreSQL, a LATERAL join allows a subquery in the FROM clause to refer to columns of tables listed before it. This means the subquery can use values from each row of the preceding table, enabling row-by-row processing inside a join.

How It Works

Imagine you have a list of people and for each person, you want to find some related data that depends on that person's information. A LATERAL join lets you run a small query for each person, using their data as input. It's like having a helper who looks at each person and then fetches matching details just for them.

Normally, subqueries in the FROM clause can't see the rows from tables listed before them. But with LATERAL, the subquery can access columns from the previous tables, making it possible to do more dynamic and flexible joins. This is useful when you want to join each row with a set of rows that depend on it.

Example

This example shows how to use LATERAL to find the top 2 orders for each customer based on order amount.

sql

CREATE TABLE customers (id SERIAL PRIMARY KEY, name TEXT);
CREATE TABLE orders (id SERIAL PRIMARY KEY, customer_id INT, amount INT);

INSERT INTO customers (name) VALUES ('Alice'), ('Bob');
INSERT INTO orders (customer_id, amount) VALUES
  (1, 100), (1, 200), (1, 50),
  (2, 300), (2, 150);

SELECT c.name, o.id AS order_id, o.amount
FROM customers c
JOIN LATERAL (
  SELECT * FROM orders o WHERE o.customer_id = c.id ORDER BY amount DESC LIMIT 2
) o ON true
ORDER BY c.id, o.amount DESC;

Output

 name  | order_id | amount 
-------+----------+--------
 Alice |        2 |    200
 Alice |        1 |    100
 Bob   |        4 |    300
 Bob   |        5 |    150
(4 rows)

When to Use

Use LATERAL joins when you need to run a query that depends on each row of another table. For example, if you want to find the top N related records per row, or if you want to call a function that returns a set of rows based on each row's data.

Common real-world cases include fetching recent transactions per user, getting related items per category, or expanding JSON arrays stored in a column into rows while using values from the main table.

Key Points

• LATERAL allows subqueries to access columns from preceding tables in the FROM clause.
• It enables row-by-row processing inside joins.
• It is useful for queries that need to fetch related data dynamically per row.
• Use LATERAL with JOIN, LEFT JOIN, or even in the FROM clause alone.

Key Takeaways

LATERAL lets a subquery use columns from tables listed before it in the FROM clause.

It enables running a query for each row of another table, like a per-row helper.

Use it to fetch related rows dynamically, such as top N matches per main row.

LATERAL works with JOINs and is powerful for flexible, row-dependent queries.