How to Use Lateral Joins in PostgreSQL

Lateral joins in PostgreSQL are a powerful feature that allows subqueries on the right side of a join to reference columns from the left side. This enables more flexible and efficient query designs.

What is a Lateral Join?

A lateral join lets you run a subquery for each row of the left-hand table in a join. It’s written using the LATERAL keyword, which tells PostgreSQL that the subquery can reference columns from the main query.

When to Use Lateral Joins

• When you need to perform a subquery that depends on each row from the main table.
• When using functions like jsonb_array_elements() or unnest() that need row context.
• To simplify complex queries with correlated subqueries.

Basic Syntax

SELECT *
FROM table1
JOIN LATERAL (
    SELECT ...
    FROM table2
    WHERE table2.column = table1.column
) subquery ON true;

Example 1: Top Order per Customer

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

This query fetches each customer with their highest total order.

Example 2: Unnesting JSON Arrays

SELECT p.id, p.name, tag
FROM products p,
LATERAL jsonb_array_elements_text(p.tags) AS tag;

This example extracts each tag from a JSONB array stored in the tags column.

Why Use Lateral Joins?

• They simplify query structure compared to correlated subqueries.
• They often improve performance by avoiding repeated calculations.
• They allow combining set-returning functions with row-level context.

Things to Watch Out For

• Always use ON true if the join condition is embedded in the subquery.
• Lateral joins may have performance implications on large datasets — test and analyze your queries.

Summary

Lateral joins are an advanced but essential feature for writing expressive, efficient SQL in PostgreSQL. They allow you to perform row-dependent subqueries and simplify complex queries.

Try adding lateral joins to your toolset — they can save you from convoluted workarounds and boost your SQL power!