Using CROSS JOIN LATERAL in Postgres

Introduction

In standard SQL, subqueries located in the JOIN clause operate independently, they cannot see or use data from tables located before (to the left of) it.
When you add the LATERAL keyword, Postgres allows the subquery on the right to directly access column values of each row in the table on the left.
Differences between Join types are as follows
- Standard CROSS JOIN takes the Cartesian product of 2 tables independent of each other (a multiplication of 2 tables without needing a condition)
- CROSS JOIN LATERAL operates like a for-each loop in programming, with each row in the left table, Postgres runs the subquery on the right to perform dynamic calculations repeatedly based on values from the left table and joins the results together.
- INNER JOIN is the intersection between 2 datasets, it only retains rows in 2 tables when both satisfy a specific join condition, the condition in ON is mandatory (unlike CROSS JOIN which does not require passing a condition)
Can be combined to form other Join types such as: LEFT JOIN LATERAL, RIGHT JOIN LATERAL, INNER JOIN LATERAL, only FULL OUTER JOIN LATERAL is not supported

Correlated Subqueries

Before LATERAL, for a Subquery in the SELECT clause to reference an outer row, we used Correlated Subqueries, including the following types

These are 2 classic Subquery forms that have existed for a long time and have limitations such as
- Scalar Subquery: used in the SELECT clause
  - Only returns 1 column and 1 row, if more data is returned it will throw an error
  - If correlated comparison exists, each row from the outer table requires running the Subquery once, it cannot trigger algorithms like when used with Join, leading to lower performance
- Forms using IN / EXISTS: Using IN / EXISTS in the WHERE clause
  - This is a Predicate Filter used only to check existence conditions, not to retrieve data from secondary tables
  - Processing Top N per group is inefficient and does not support expanding Array/JSON like when using LATERAL
Using LATERAL: Used in FROM / JOIN clauses, created with advantages such as
- Allows Subquery to return multiple columns and rows
- Operates as a Data Source so it can combine with data in the original table
- Handles Top N per group efficiently using LIMIT to cap specific row counts, avoiding redundant processing
- Supports expanding Array/JSON, this is the only tool that can use set-returning functions like unnest, jsonb_to_recordset, generate_series on each data row of the main table.

Use cases

Below are the most common use cases for LATERAL

Top N per group
- This is the most classic problem. Suppose you want to get the 2 latest orders for each customer.
- If using Window Function like ROW_NUMBER, the query is usually long and must execute over the entire orders table. Using LATERAL is much cleaner and more optimized
Unpacking JSON or Array
- When working with JSONB or ARRAY type columns, you need to turn an array of elements into rows corresponding to the original record.
- Commonly used alongside Set-Returning Functions like unnest
Reusing complex calculation expressions: When you have long calculation formulas in SELECT and want to reuse them in WHERE or ORDER BY clauses, you can write them concisely using LATERAL

Detail

Please create tables as follows

CREATE TABLE customers (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE products (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_name VARCHAR(150) NOT NULL,
    base_price NUMERIC(12,2) NOT NULL,
    discount_pct NUMERIC(4,2) DEFAULT 0.00, 
    tags TEXT[],
    variant_attributes JSONB
);

CREATE TABLE orders (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id INT NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
    order_date TIMESTAMPTZ NOT NULL,
    amount NUMERIC(12,2) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'completed'
);

CREATE INDEX idx_orders_customer_date_desc ON orders (customer_id, order_date DESC) INCLUDE (amount);

After seeding data, we use the following queries to verify

Top N per group

-- Query 1
SELECT c.id AS customer_id, c.name, o.id AS order_id, o.order_date, o.amount
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.id IN (
    SELECT sub.id 
    FROM orders sub 
    WHERE sub.customer_id = c.id AND status = 'completed'
    ORDER BY sub.order_date DESC 
    LIMIT 1
)
ORDER BY c.id, o.order_date DESC;

-- Query 2
SELECT 
    c.id AS customer_id,
    c.name,
    o.id AS order_id,
    o.order_date,
    o.amount
FROM customers c
CROSS JOIN LATERAL (
    SELECT id, order_date, amount
    FROM orders 
    WHERE customer_id = c.id AND status = 'completed'
    ORDER BY order_date DESC
    LIMIT 1
) o
ORDER BY c.id, o.order_date DESC;

The 2 queries above retrieve the latest order for each customer using different approaches

Query 1: This approach uses Correlated Subquery, which has limitations when using IN/EXISTS as it can only retrieve 1 column in the Subquery, requiring complex query implementation if you want to retrieve multiple columns

Uses Seq Scan on the customers table to create customer_list
Loop retrieving Subquery ID & Creating Hash Table (Nested Loop + Hash Join), for each customer it performs a series of complex nested operations:
- Runs Subquery: Uses index idx_orders_customer_date_desc to find the ID of 1 latest order satisfying condition status = 'completed' and creates a Hash Table
- Uses Index Scan under condition o.customer_id = c.id to create list1
- Uses Hash Semi Join between list1 and the existing Hash Table to create list2
Uses Nested Loop Inner Join between customer_list and list2

Query 2: This is a more optimal solution using CROSS JOIN LATERAL

Outer Loop: Scans customer table using Index Scan on customers_pkey.
Inner Loop: Loops and retrieves the latest order
- For each fetched customer, uses Nested Loop to run the LATERAL subquery
- Uses Index idx_orders_customer_date_desc to quickly locate orders matching customer_id = c.id
- Applies filter condition status = 'completed'.
- Thanks to the LIMIT node, as soon as it finds the first matching order (which is the latest due to Index sorting by date), it stops searching immediately for that customer.

LEFT/CROSS/INNER JOIN LATERAL

These are queries to distinguish between LEFT/CROSS/INNER JOIN LATERAL

-- Query 1
SELECT 
    c.id AS customer_id,
    c.name,
    latest_order.id AS latest_order_id,
    latest_order.amount
FROM customers c
LEFT JOIN LATERAL (
    SELECT id, amount
    FROM orders
    WHERE customer_id = c.id
    ORDER BY order_date DESC
    LIMIT 1
) latest_order ON true;

-- Query 2
SELECT 
    c.id AS customer_id,
    c.name,
    latest_order.id AS latest_order_id,
    latest_order.amount
FROM customers c
CROSS JOIN LATERAL (
    SELECT id, amount
    FROM orders
    WHERE customer_id = c.id
    ORDER BY order_date DESC
    LIMIT 1
) latest_order;

-- Query 3
SELECT 
    c.id AS customer_id,
    c.name,
    latest_order.id AS latest_order_id,
    latest_order.amount
FROM customers c
INNER JOIN LATERAL (
    SELECT id, amount
    FROM orders
    WHERE customer_id = c.id
    ORDER BY order_date DESC
    LIMIT 1
) latest_order ON true;

Export report listing all customers along with their latest order.

Query 1: Uses LEFT JOIN LATERAL, keeping customers who have NOT purchased yet

Query 2: Uses CROSS JOIN LATERAL, only retrieving customers who ALREADY HAVE orders

Query 3: Uses INNER JOIN LATERAL, syntax equivalent to CROSS JOIN but includes an ON clause

Unpacking JSONB & Array

-- Query 1
SELECT 
    p.id AS product_id,
    p.product_name,
    tag.name AS tag_name,
    tag.ordinal AS tag_position
FROM products p
CROSS JOIN LATERAL unnest(p.tags) WITH ORDINALITY AS tag(name, ordinal)
ORDER BY product_id

-- Query 2
SELECT 
    p.id AS product_id,
    p.product_name,
    v.color,
    v.size,
    v.stock
FROM products p
LEFT JOIN LATERAL jsonb_to_recordset(p.variant_attributes) AS v(
    color TEXT,
    size TEXT,
    stock INT
) ON true
ORDER BY product_id

These queries analyze product data to create standardized data tables for inventory statistics.

Query 1: Unpacks TEXT array of tags column using unnest function, WITH ORDINALITY and CROSS JOIN LATERAL

Outer Loop: Index Scan on products table (using products_pkey)
Inner Loop: Runs unnest for each retrieved row
- unnest function flattens the tags array column into a list of rows
- Uses WITH ORDINALITY to number starting from 1 for each generated tag row, while tag(name, ordinal) is an alias with name as tag created from unnest function and ordinal as index value
- Thus using unnest and WITH ORDINALITY also creates a list from tags array to JOIN
Next, uses Nested Loop Join for the 2 generated lists

Query 2: Unpacks JSONB of variant_attributes column using jsonb_to_recordset function and LEFT JOIN LATERAL

Outer Loop: Index Scan on products table (using products_pkey)
Inner Loop: Runs jsonb_to_recordset for each retrieved row
- variant_attributes column is JSONB with JSON Array data
- Each item in array is split into 1 row with columns color, size, stock
Next, uses Nested Loop Join for the 2 generated lists

Calculated Columns

SELECT 
    p.id,
    p.product_name,
    p.base_price,
    calc1.discount_amount,
    calc1.discounted_price,
    calc2.vat_amount,
    calc2.final_price
FROM products p
CROSS JOIN LATERAL (
    SELECT 
        (p.base_price * p.discount_pct) AS discount_amount,
        (p.base_price * (1 - p.discount_pct)) AS discounted_price
) calc1
CROSS JOIN LATERAL (
    SELECT
        (calc1.discounted_price * 0.10) AS vat_amount,
        (calc1.discounted_price * 1.10) AS final_price
) calc2
WHERE calc2.final_price BETWEEN 100 AND 300
ORDER BY calc2.final_price DESC

This is a case of reusing complex calculation expressions
If you do not use LATERAL, you must rewrite formulas multiple times in SELECT, WHERE and ORDER BY or nest multiple levels of CTE / Subquery.
We use CROSS JOIN LATERAL which supports chaining to calculate values in multiple steps as follows
- First, calculate discount_amount and discounted_price
- Next, use those results to further calculate vat_amount and final_price
- You can easily reuse calculated columns directly across SELECT/WHERE/ORDER BY