LATERAL JOIN keyword

LATERAL JOIN allows a subquery on the right-hand side of a join to reference columns from tables that appear earlier in the FROM clause. Conceptually the subquery is evaluated once for every outer row, with the outer columns acting as parameters. This unlocks queries that would otherwise require correlated subqueries, window functions, or self-joins, such as:

• Top-N rows per group (e.g. the three largest fills for each order).
• Per-row aggregates that depend on values from the outer row.
• Dynamic filters whose thresholds come from the outer row.
• Combining a SAMPLE BY, LATEST ON, or ASOF JOIN with per-row parameters from the outer table.

It is a variant of the JOIN keyword and shares many of its execution traits. Under the hood, QuestDB rewrites correlated lateral subqueries into standard joins, so they execute as set-based operations rather than nested loops.

Syntax

LATERAL is a modifier on JOIN. It can be combined with INNER, LEFT, and CROSS joins. Right and full outer variants are not supported.

INNER lateral

SELECT ...
FROM left_table [alias]
[INNER] JOIN LATERAL (subquery) [alias] [ON condition];

LEFT lateral (unmatched outer rows kept, right columns become NULL)

SELECT ...
FROM left_table [alias]
LEFT [OUTER] JOIN LATERAL (subquery) [alias] [ON condition];

CROSS lateral (no ON clause)

SELECT ...
FROM left_table [alias]
CROSS JOIN LATERAL (subquery) [alias];

Comma syntax (implicit CROSS)

SELECT ...
FROM left_table [alias],
     LATERAL (subquery) [alias];

The subquery body can reference columns from any table that appears to its left in the FROM clause. References to outer columns must be qualified with the outer alias (e.g. o.id) when the inner subquery would otherwise resolve the name to one of its own columns.

note

LATERAL always requires a parenthesised subquery. A bare table reference after JOIN LATERAL is rejected with LATERAL requires a subquery.

LATERAL is only valid with INNER, LEFT, or CROSS joins. Using it with RIGHT or FULL OUTER joins fails with LATERAL is only supported with INNER, LEFT, or CROSS joins.

INNER vs CROSS LATERAL

For most practical purposes, INNER JOIN LATERAL and CROSS JOIN LATERAL (including the comma syntax) behave identically. The correlation between the outer row and the subquery is expressed inside the subquery's WHERE clause, not in an ON condition.

The only difference is that INNER JOIN LATERAL allows an optional ON clause while CROSS JOIN LATERAL does not. In practice, ON is rarely needed because the whole point of LATERAL is that the subquery itself filters using outer-row columns.

LEFT JOIN LATERAL is distinct: it preserves outer rows that have no matching subquery results, returning NULL for the subquery's columns.

Examples

The examples in this section all run against the following schema and data, unless a section explicitly defines additional tables:

CREATE TABLE orders (
    id        INT,
    desk      SYMBOL,
    min_qty   DOUBLE,
    ts        TIMESTAMP
) TIMESTAMP(ts) PARTITION BY DAY;

CREATE TABLE fills (
    id        INT,
    order_id  INT,
    qty       DOUBLE,
    ts        TIMESTAMP
) TIMESTAMP(ts) PARTITION BY DAY;

INSERT INTO orders VALUES
    (1, 'eq',  15.0, '2024-01-01T00:00:00.000000Z'),
    (2, 'fi',  35.0, '2024-01-01T01:00:00.000000Z'),
    (3, 'cmd',  5.0, '2024-01-01T02:00:00.000000Z');

INSERT INTO fills VALUES
    (1, 1, 10.0, '2024-01-01T00:10:00.000000Z'),
    (2, 1, 20.0, '2024-01-01T00:40:00.000000Z'),
    (3, 1, 30.0, '2024-01-01T01:10:00.000000Z'),
    (4, 2, 40.0, '2024-01-01T01:10:00.000000Z'),
    (5, 2, 50.0, '2024-01-01T01:40:00.000000Z');

Order 1 (equities desk) has three fills, order 2 (fixed income) has two fills, and order 3 (commodities) has none.

Per-row scan with INNER JOIN LATERAL

For each order, return every matching fill. The commodities desk has no fills and is dropped because this is an inner join:

SELECT o.id, o.desk, t.qty
FROM orders o
JOIN LATERAL (
    SELECT qty FROM fills WHERE order_id = o.id
) t
ORDER BY o.id, t.qty;

id	desk	qty
1	eq	10.0
1	eq	20.0
1	eq	30.0
2	fi	40.0
2	fi	50.0

Preserving outer rows with LEFT JOIN LATERAL

LEFT JOIN LATERAL keeps every outer row even when the subquery is empty:

SELECT o.id, o.desk, t.qty
FROM orders o
LEFT JOIN LATERAL (
    SELECT qty FROM fills WHERE order_id = o.id
) t
ORDER BY o.id, t.qty;

id	desk	qty
1	eq	10.0
1	eq	20.0
1	eq	30.0
2	fi	40.0
2	fi	50.0
3	cmd	null

When the subquery aggregates with count(*), missing groups are reported as 0 rather than NULL so totals stay numeric. Other aggregates such as sum() keep their natural empty-set semantics and return NULL:

SELECT o.id, t.cnt
FROM orders o
LEFT JOIN LATERAL (
    SELECT count(*) AS cnt FROM fills WHERE order_id = o.id
) t
ORDER BY o.id;

id	cnt
1	3
2	2
3	0

Top-N per group

A common use of LATERAL JOIN is selecting the top-N rows per outer row, which is awkward with GROUP BY alone. Pair an ORDER BY with LIMIT inside the subquery — both apply independently to each outer row's matches:

SELECT o.id, o.desk, t.qty
FROM orders o
JOIN LATERAL (
    SELECT qty
    FROM fills
    WHERE order_id = o.id
    ORDER BY qty DESC
    LIMIT 2
) t
ORDER BY o.id, t.qty DESC;

id	desk	qty
1	eq	30.0
1	eq	20.0
2	fi	50.0
2	fi	40.0

Per-row aggregates

Aggregating inside the subquery returns one row per outer row:

SELECT o.id, o.desk, t.total_qty
FROM orders o
JOIN LATERAL (
    SELECT sum(qty) AS total_qty
    FROM fills
    WHERE order_id = o.id
) t
ORDER BY o.id;

id	desk	total_qty
1	eq	60.0
2	fi	90.0

This particular query is equivalent to a regular GROUP BY join — the inner subquery only references the outer row through an equality. LATERAL JOIN becomes essential when the subquery needs the outer row in less trivial ways, as in the next example.

Dynamic filters using outer-row values

The subquery can reference any outer column in its WHERE, including in non-equality predicates. Below, each order's min_qty is used as a per-row threshold for the fills it sums:

SELECT o.id, o.desk, t.total_qty
FROM orders o
JOIN LATERAL (
    SELECT sum(qty) AS total_qty
    FROM fills
    WHERE order_id = o.id
      AND qty > o.min_qty
) t
ORDER BY o.id;

id	desk	total_qty
1	eq	50.0
2	fi	90.0

The equities desk fills above 15 are 20 and 30 (sum 50); the fixed income desk fills above 35 are 40 and 50 (sum 90). The commodities desk has no fills and is dropped by the inner join.

Window functions inside LATERAL

The subquery may contain window functions. The window's PARTITION BY and ORDER BY are evaluated independently for each outer row, so a single OVER (ORDER BY ts) becomes a per-order running total:

SELECT o.id, t.qty, t.running_total
FROM orders o
JOIN LATERAL (
    SELECT qty,
           sum(qty) OVER (ORDER BY ts) AS running_total
    FROM fills
    WHERE order_id = o.id
) t
ORDER BY o.id, t.qty;

id	qty	running_total
1	10.0	10.0
1	20.0	30.0
1	30.0	60.0
2	40.0	40.0
2	50.0	90.0

SAMPLE BY inside LATERAL

The following examples use the fx_trades and core_price tables from the QuestDB demo instance.

Each outer row gets its own sampled time-series. Here we compute a 1-hour volume profile per symbol for the most recently traded symbols:

Per-symbol hourly volumeDemo this query

SELECT t.symbol, sub.ts, sub.volume
FROM (
    SELECT * FROM fx_trades
    LATEST ON timestamp PARTITION BY symbol
) t
JOIN LATERAL (
    SELECT timestamp AS ts, sum(quantity) AS volume
    FROM fx_trades
    WHERE symbol = t.symbol
      AND timestamp IN '$now-6h..$now'
    SAMPLE BY 1h
) sub
ORDER BY t.symbol, sub.ts;

LATEST ON inside LATERAL

LATEST ON ... PARTITION BY returns the latest record per partition. Inside a LATERAL subquery, the partitions are computed independently for each outer row. Here, for each symbol we find the latest trade on each side (buy/sell):

Latest trade per side for each symbolDemo this query

SELECT t.symbol, sub.side, sub.price, sub.quantity
FROM (
    SELECT DISTINCT symbol FROM fx_trades
    WHERE timestamp IN '$now-1h..$now'
) t
JOIN LATERAL (
    SELECT side, price, quantity
    FROM fx_trades
    WHERE symbol = t.symbol
    LATEST ON timestamp PARTITION BY side
) sub
ORDER BY t.symbol, sub.side;

ASOF JOIN inside LATERAL

Combine LATERAL with ASOF JOIN to enrich each symbol's recent trades with the prevailing bid/ask from the core_price table. For each symbol's latest trade, the subquery finds the top 3 trades by quantity in the last minute and attaches the corresponding quote:

Top fills with prevailing quotes per symbolDemo this query

SELECT
    t.symbol,
    sub.timestamp,
    sub.side,
    sub.price,
    sub.quantity,
    sub.ecn,
    sub.bid_price,
    sub.ask_price
FROM (
    SELECT * FROM fx_trades
    LATEST ON timestamp PARTITION BY symbol
) t
JOIN LATERAL (
    SELECT
        f.timestamp, f.side, f.price,
        f.quantity, f.ecn,
        c.bid_price, c.ask_price
    FROM fx_trades f
    ASOF JOIN core_price c
        ON (f.symbol = c.symbol AND f.ecn = c.ecn)
    WHERE f.symbol = t.symbol
        AND f.timestamp IN '$now-1m..$now'
    ORDER BY f.quantity DESC
    LIMIT 3
) sub;

For each symbol, the three largest recent trades are returned alongside the prevailing bid and ask from the same ECN at the time of the trade.

UNION ALL of correlated branches

Each branch of a UNION / UNION ALL may reference outer columns independently. The example below splits each order's fills into "small" and "large" buckets in a single subquery:

SELECT o.id, t.qty, t.bucket
FROM orders o
JOIN LATERAL (
    SELECT qty, 'small' AS bucket FROM fills
        WHERE order_id = o.id AND qty < 30
    UNION ALL
    SELECT qty, 'large' AS bucket FROM fills
        WHERE order_id = o.id AND qty >= 30
) t
ORDER BY o.id, t.qty;

id	qty	bucket
1	10.0	small
1	20.0	small
1	30.0	large
2	40.0	large
2	50.0	large

Standalone LATERAL (implicit CROSS)

LATERAL may appear directly in a comma-separated FROM list. This is equivalent to CROSS JOIN LATERAL:

SELECT o.id, t.qty
FROM orders o,
     LATERAL (SELECT qty FROM fills WHERE order_id = o.id) t
ORDER BY o.id, t.qty;

This produces the same id/qty pairs as the per-row scan example above — only the syntax differs.

Multiple correlation columns

The subquery can correlate on more than one outer column at the same time. This example uses an alternate schema to show how a master/detail relationship keyed on (mm_id, symbol) can drive a per-row aggregate:

CREATE TABLE master (
    mm_id  INT,
    symbol STRING,
    ts     TIMESTAMP
) TIMESTAMP(ts) PARTITION BY DAY;

CREATE TABLE detail (
    mm_id  INT,
    symbol STRING,
    qty    DOUBLE,
    ts     TIMESTAMP
) TIMESTAMP(ts) PARTITION BY DAY;

SELECT m.mm_id, m.symbol, t.total
FROM master m
LEFT JOIN LATERAL (
    SELECT sum(qty) AS total
    FROM detail
    WHERE mm_id = m.mm_id
      AND symbol = m.symbol
) t
ORDER BY m.mm_id;

Restrictions

• LATERAL requires a parenthesised subquery — bare table references are rejected.
• LATERAL may only modify INNER, LEFT, or CROSS joins. RIGHT JOIN LATERAL and FULL OUTER JOIN LATERAL are not supported.
• CROSS JOIN LATERAL does not accept an ON clause, in line with regular CROSS JOIN.

How it executes

QuestDB's optimiser rewrites correlated lateral subqueries into standard joins during query planning, so they execute set-based rather than as a per-row nested loop. The rewrite preserves semantics for GROUP BY, SAMPLE BY, DISTINCT, LIMIT, window functions, LATEST ON, and set operations (UNION / INTERSECT / EXCEPT). When every correlation reduces to an equality, the lateral is further reduced to a plain hash join.

You can inspect the rewritten plan with EXPLAIN to see the join strategy QuestDB chose for a given query.