PostgreSQL LATERAL joins: top rows and missing matches

A PostgreSQL LATERAL subquery can use values from a preceding item in the FROM clause. This makes it useful when the rows you want to join depend on the current source row, such as the two latest paid orders for each customer.

Use CROSS JOIN LATERAL when a source row should disappear if the subquery returns nothing. Use LEFT JOIN LATERAL ... ON TRUE when the source row should remain with NULL values on the right. The subquery's actual result matters: an aggregate can return a row even when its input is empty.

This guide runs both forms on original customer and order data. All 29 checks passed on PostgreSQL 18.6, covering tied timestamps, missing matches, filter placement, window-function alternatives, and aggregate behavior. These small correctness checks do not establish that LATERAL is faster than another query.

Build a fixture with ties and missing matches

Use a disposable database named lateral_demo where lateral_lab does not already exist. Your role must be able to create the schema and tables. Save the numbered blocks as named files and run them in order. For example: psql -X -qAt -P 'null=<NULL>' -v ON_ERROR_STOP=1 -d lateral_demo -f 01-setup.sql. Add your connection options.

The displayed output uses unaligned, tuples-only psql formatting, with vertical bars separating columns and <NULL> representing SQL NULL. For a separate learning environment, our PostgreSQL with Docker Compose guide explains a container-based setup.

Run 01-setup.sql:

CREATE SCHEMA lateral_lab;
CREATE TABLE lateral_lab.customers (
    customer_id integer PRIMARY KEY,
    name text NOT NULL
);
CREATE TABLE lateral_lab.orders (
    order_id integer PRIMARY KEY,
    customer_id integer NOT NULL REFERENCES lateral_lab.customers,
    placed_at timestamp NOT NULL,
    status text NOT NULL CHECK (status IN ('paid', 'cancelled')),
    amount numeric(10,2) NOT NULL
);
INSERT INTO lateral_lab.customers VALUES
    (1, 'Alpha'), (2, 'Beta'), (3, 'Gamma'), (4, 'Delta');
INSERT INTO lateral_lab.orders VALUES
    (101, 1, '2026-01-01 09:00', 'paid', 10),
    (102, 1, '2026-01-02 09:00', 'paid', 20),
    (103, 1, '2026-01-02 09:00', 'paid', 30),
    (104, 1, '2026-01-03 09:00', 'cancelled', 90),
    (201, 2, '2026-01-02 10:00', 'paid', 40),
    (202, 2, '2026-01-01 10:00', 'cancelled', 80),
    (401, 4, '2026-01-02 11:00', 'cancelled', 70);
SELECT count(*) FROM lateral_lab.customers;
SELECT count(*), sum(amount) FROM lateral_lab.orders;

4
7|340.00

Alpha has three paid orders, including two at the same timestamp, and a newer canceled order. Beta has one paid order and an older canceled order. Gamma has no orders. Delta has an order, but none with paid status. The distinction between no rows and no qualifying rows will be visible in the join results.

The timestamps are synthetic local values without time zones. They are NOT NULL in this fixture, so the ranking examples do not need a separate NULL timestamp policy.

Return the latest two paid orders per customer

Run 02-cross.sql:

SELECT c.customer_id, c.name, recent.order_id, recent.amount
FROM lateral_lab.customers AS c
CROSS JOIN LATERAL (
    SELECT o.order_id, o.placed_at, o.amount
    FROM lateral_lab.orders AS o
    WHERE o.customer_id = c.customer_id AND o.status = 'paid'
    ORDER BY o.placed_at DESC, o.order_id DESC
    LIMIT 2
) AS recent
ORDER BY c.customer_id, recent.placed_at DESC, recent.order_id DESC;

1|Alpha|103|30.00
1|Alpha|102|20.00
2|Beta|201|40.00

The condition o.customer_id = c.customer_id correlates the subquery with the current customer. Filtering, ordering, and LIMIT 2 apply to that customer's qualifying orders. The full result has three rows: two for Alpha and one for Beta. The limit is not two rows for the entire report.

Orders 102 and 103 share a timestamp. Sorting by the unique order_id as a secondary key places 103 first. The inner ORDER BY determines which rows make the limit; the outer ORDER BY specifies the final report order. The LIMIT documentation explains why a predictable subset requires a complete ordering.

Gamma and Delta disappear because the subquery returns no qualifying rows for them. Our equivalent INNER JOIN LATERAL ... ON TRUE query returned the same result. Choose this behavior only when customers without matching orders should be absent.

The LATERAL reference describes which preceding columns a subquery may use. Removing LATERAL while keeping this outer reference failed in our test with SQLSTATE 42P01. Removing the correlation condition instead produced the same global top two paid orders for every customer, which is a different report.

Keep customers with no qualifying orders

Run 03-left.sql:

SELECT c.customer_id, c.name, recent.order_id, recent.amount
FROM lateral_lab.customers AS c
LEFT JOIN LATERAL (
    SELECT o.order_id, o.placed_at, o.amount
    FROM lateral_lab.orders AS o
    WHERE o.customer_id = c.customer_id AND o.status = 'paid'
    ORDER BY o.placed_at DESC, o.order_id DESC
    LIMIT 2
) AS recent ON TRUE
ORDER BY c.customer_id, recent.placed_at DESC, recent.order_id DESC;

1|Alpha|103|30.00
1|Alpha|102|20.00
2|Beta|201|40.00
3|Gamma|<NULL>|<NULL>
4|Delta|<NULL>|<NULL>

LEFT JOIN adds one NULL-extended row for Gamma and one for Delta. ON TRUE introduces no additional join restriction because the customer relationship and paid-status filter are already inside the subquery.

The result has five rows for four customers. A left lateral join does not promise one row per source row: Alpha still contributes two matches. If you need one most-recent order per customer, use LIMIT 1 with a deterministic ordering. If you need a single summary per customer, aggregate deliberately.

Place filters before or after the limit intentionally

The following 04-filter-after.sql first selects each customer's latest order of any status, then filters the joined result:

SELECT c.customer_id, c.name, recent.order_id
FROM lateral_lab.customers AS c
LEFT JOIN LATERAL (
    SELECT o.order_id, o.placed_at, o.status
    FROM lateral_lab.orders AS o
    WHERE o.customer_id = c.customer_id
    ORDER BY o.placed_at DESC, o.order_id DESC
    LIMIT 1
) AS recent ON TRUE
WHERE recent.status = 'paid'
ORDER BY c.customer_id;

2|Beta|201

Only Beta remains. Alpha's newest order is canceled, so the outer filter removes Alpha even though paid orders exist. Gamma's right-hand status is NULL, which also does not satisfy status = 'paid'. Delta's canceled order fails the filter.

For the latest paid order while retaining all customers, apply the paid condition inside the subquery before LIMIT. Run 05-filter-before.sql:

SELECT c.customer_id, c.name, recent.order_id
FROM lateral_lab.customers AS c
LEFT JOIN LATERAL (
    SELECT o.order_id
    FROM lateral_lab.orders AS o
    WHERE o.customer_id = c.customer_id AND o.status = 'paid'
    ORDER BY o.placed_at DESC, o.order_id DESC
    LIMIT 1
) AS recent ON TRUE
ORDER BY c.customer_id;

1|Alpha|103
2|Beta|201
3|Gamma|<NULL>
4|Delta|<NULL>

Alpha now returns order 103, Beta returns 201, and Gamma and Delta remain with NULL order IDs. These queries answer different questions. Moving the paid condition into ON after selecting the latest overall order would preserve an unmatched customer, but would still not search past the canceled order for an earlier paid one.

Compare a window-function alternative

A top-N-per-group report can also rank the qualifying orders first. Run 06-window.sql:

WITH ranked AS (
    SELECT o.*,
           row_number() OVER (
               PARTITION BY o.customer_id
               ORDER BY o.placed_at DESC, o.order_id DESC
           ) AS position
    FROM lateral_lab.orders AS o
    WHERE o.status = 'paid'
)
SELECT c.customer_id, c.name, r.order_id, r.amount
FROM lateral_lab.customers AS c
LEFT JOIN ranked AS r
    ON r.customer_id = c.customer_id AND r.position <= 2
ORDER BY c.customer_id, r.placed_at DESC, r.order_id DESC;

1|Alpha|103|30.00
1|Alpha|102|20.00
2|Beta|201|40.00
3|Gamma|<NULL>|<NULL>
4|Delta|<NULL>|<NULL>

This output matches the LEFT JOIN LATERAL top-two result exactly. The predicate r.position <= 2 belongs in the join condition here, preserving customers without ranked orders. Moving it into an outer WHERE removed Gamma and Delta in our check.

The PostgreSQL window-function tutorial covers row_number and partitioned ordering. Our comparisons also matched for limits zero, one, and three. These comparisons verify result semantics, not a universal preference for either plan.

Choose the form that expresses the task clearly, then inspect the plan for realistic data and parameters. LATERAL is convenient when a source row supplies inputs to a limited lookup. A window expression can be convenient when you already need rankings over a larger set. Neither spelling guarantees lower execution time.

Account for aggregates that return a row on empty input

Run 07-aggregate.sql:

SELECT c.customer_id, totals.paid_count, totals.paid_amount
FROM lateral_lab.customers AS c
CROSS JOIN LATERAL (
    SELECT count(*) AS paid_count, sum(o.amount) AS paid_amount
    FROM lateral_lab.orders AS o
    WHERE o.customer_id = c.customer_id AND o.status = 'paid'
) AS totals
ORDER BY c.customer_id;

1|3|60.00
2|1|40.00
3|0|<NULL>
4|0|<NULL>

All four customers remain despite CROSS JOIN. The aggregate subquery has no GROUP BY, so it produces one result row for each invocation: count(*) is zero and sum(amount) is NULL when there are no qualifying orders. The aggregate function reference documents these empty-input results.

Our separate test added GROUP BY o.customer_id inside that subquery. The empty groups then produced no rows, and CROSS JOIN removed Gamma and Delta. Inspect what the subquery returns rather than assuming that an empty base-table match always means an empty subquery result.

If your reporting rules treat an absent total as zero, apply COALESCE explicitly. Keep the original NULL meaning when absence needs to remain distinguishable from a real zero-valued total.

Consider an index, then inspect the actual plan

For this paid-order lookup, 08-index.sql creates a candidate partial index aligned with the customer predicate and ranking order:

CREATE INDEX orders_paid_recent_idx
ON lateral_lab.orders (customer_id, placed_at DESC, order_id DESC)
WHERE status = 'paid';
ANALYZE lateral_lab.orders;
ANALYZE lateral_lab.customers;

The index includes only paid orders and orders entries by customer, descending timestamp, and descending order ID. Our test verified its definition and confirmed that the left-join result stayed unchanged after creation.

The partial-index documentation explains the predicate requirements. This index does not cover the latest-order-of-any-status query in the same way, and a parameterized predicate needs its own plan review. Do not assume an index will be chosen just because it exists.

We captured a JSON plan without forcing index use, but did not benchmark these seven rows. Use our PostgreSQL EXPLAIN ANALYZE guide to examine plans on a representative workload. Compare selected customer counts, orders per customer, filtering, loop counts, and actual rows before drawing a performance conclusion.

Remove the example objects

After the exercise, run 09-cleanup.sql only in the disposable database:

DROP TABLE lateral_lab.orders;
DROP TABLE lateral_lab.customers;
DROP SCHEMA lateral_lab;

The orders table is removed before customers because of the foreign key; its index is removed with it. Our harness confirmed cleanup, stopped its isolated server, and removed its temporary password file.

The review package contains the SQL, captured results, plan evidence, and verification harness. Before adapting a LATERAL query, decide whether unmatched source rows belong in the report, what happens at ties, and which predicates must run before a per-row limit.