When to Use Lateral Join in PostgreSQL: Explained with Examples

Use LATERAL JOIN in PostgreSQL when you need to join each row from one table to a set of rows produced by a function or subquery that depends on that row. It allows the subquery to reference columns from the outer query, enabling more dynamic and flexible queries.

How It Works

Imagine you have a list of people and for each person, you want to find their top 3 favorite books. Normally, a join combines rows from two tables independently. But with LATERAL JOIN, the second table or subquery can use data from each row of the first table to customize its results.

Think of it like ordering food at a restaurant: the waiter asks your name first, then brings you a personalized menu based on your preferences. The LATERAL keyword lets the database do this personalized matching for each row.

This means the subquery runs once per row of the main query, using that row's data to produce related results. This is very useful when the related data depends on the outer row's values.

Example

This example shows how to use LATERAL JOIN to get 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 o2
  WHERE o2.customer_id = c.id
  ORDER BY o2.amount DESC
  LIMIT 2
) o ON true
ORDER BY c.name, o.amount DESC;

Output

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

When to Use

Use LATERAL JOIN when you need to run a subquery or function for each row of a main query and that subquery depends on the outer row's data. This is common when:

• You want to select top N related rows per group, like top orders per customer.
• You need to call a set-returning function that uses columns from the main query.
• You want to filter or calculate values dynamically based on each row.

For example, in reporting, you might want to find the latest comments per user or the closest location per event. LATERAL makes these queries simpler and more efficient than alternatives.

Key Points

• LATERAL allows subqueries to reference columns from the outer query.
• It runs the subquery once per row of the main query.
• Useful for top-N per group, dynamic filtering, and calling functions with outer references.
• Improves query flexibility and expressiveness in PostgreSQL.

Key Takeaways

Use LATERAL JOIN to run subqueries that depend on each row of the main query.

It is ideal for selecting top N related rows per group or dynamic calculations.

LATERAL makes queries more flexible by allowing outer references inside subqueries.

It runs the subquery once per outer row, enabling personalized results.

Use it to simplify complex queries involving functions or filtering per row.