Relix

Language reference

Lateral Join (LATERAL / OUTER LATERAL)

Syntax

left LATERAL fn(arg, …)
left LATERAL ( relation )
left OUTER LATERAL fn(arg, …)
left OUTER LATERAL ( relation )

Customers LATERAL ordersFor(customer_id)
Customers OUTER LATERAL (λ 1 (τ amount DESC (σ customer_id = Customers.customer_id (Orders))))

Description

A LATERAL join invokes a table-valued function (TVF) once per row of the left relation, with arguments that may reference columns from that row. The output is the left row concatenated with every row the TVF returns for it. A left row that matches no TVF rows simply produces no output rows (inner-join semantics).

This is the correlated cousin of a plain TVF call: where ordersFor(2) always returns the same orders, Customers LATERAL ordersFor(customer_id) personalises the call for each customer.

The right side can also be a relation written in place, in parentheses: a subquery that names the left row's columns qualified, Customers LATERAL (σ customer_id = Customers.customer_id (Orders)). OUTER LATERAL keeps a left row whose right side has no rows, once, with the right columns NULL, as a left outer join does.

Technical Description

For each row r in left:

  1. Evaluate the argument expressions against r (they may reference any left column).
  2. Call fn(evaluated args) to obtain a relation TVF_r.
  3. Emit one output row for each row t in TVF_r: r || t (left columns then TVF columns).

The output schema is the left schema concatenated with the right side's heading. If the right side is empty for a given left row, that left row contributes no output rows under LATERAL, and one row with NULL right columns under OUTER LATERAL.

A relation on the right. left LATERAL (relation) evaluates the relation once per left row. Inside it, a qualified name R.c whose relation it does not read is a column of the left row; an unqualified name is the relation's own. The left row's values are bound into the relation and it is planned once for each distinct set of them, its rows reused for a repeat. A NULL value is bound as NULL, so it matches nothing, as in SQL. A relation that reads nothing of the left row is joined as a product (LATERAL-001, below).

SQL's spellings. CROSS APPLY is LATERAL; OUTER APPLY, LEFT LATERAL and LEFT JOIN LATERAL … ON TRUE are OUTER LATERAL. The parser refuses SQL's words and names the Relix one.

A function's arguments must be scalar expressions; argument expressions referencing columns from the left relation are fully supported. Arguments may also be constants or arithmetic expressions combining constants and left-side columns.

Every scalar type may be an argument: NUMBER, STRING, BOOLEAN, and the four temporal types DATE, TIME, TIMESTAMP and DURATION. The value is carried into the function body as a literal of its own type, so a DATE argument arrives as a DATE rather than as its text, and a boolean argument may be received by a BOOLEAN parameter.

LATERAL shares the same precedence as ⋈ (natural join) and is left-associative:

A ⋈ B LATERAL f(x)  →  (A ⋈ B) LATERAL f(x)

The TVF is defined with def name(p: T, …): RELATION := { … }; (see def … : RELATION). The planner inlines the TVF body per-row at execution time.

Examples

Golden path: each customer's orders

def ordersFor(cid: NUMBER): RELATION := { σ customer_id = cid (Orders) };

Orders := [| order_id | customer_id | amount |
           | 1        | 2           | 100    |
           | 2        | 3           | 50     |
           | 3        | 2           | 75     |];

Customers := [| customer_id | name  |
              | 2           | Alice |
              | 3           | Bob   |];

query { Customers LATERAL ordersFor(customer_id) };

Result — Alice is paired with her two orders; Bob with his one:

customer_idnameorder_idamount
2Alice1100
2Alice375
3Bob250

Filtering the LATERAL output

query { σ amount > 60 (Customers LATERAL ordersFor(customer_id)) };

Result — only Alice's large orders survive:

customer_idnameorder_idamount
2Alice1100
2Alice375

Projecting over a LATERAL join

query { π name, amount (Customers LATERAL ordersFor(customer_id)) };

Two-parameter correlated TVF

def topOrders(cid: NUMBER, lim: NUMBER): RELATION :=
    { σ customer_id = cid ∧ amount >= lim (Orders) };

Customers := [| customer_id | name  | min_amount |
              | 2           | Alice | 80         |
              | 3           | Bob   | 40         |];

query { Customers LATERAL topOrders(customer_id, min_amount) };

Each customer's min_amount column is passed as the second argument, giving each customer a personalised threshold.

Left-associativity with other joins

def f(v: NUMBER) : RELATION := { σ amount = v (Orders) };

query { (A ⋈ B) LATERAL f(x) };   -- explicit grouping
query { A ⋈ B LATERAL f(x) };     -- same: left-associative at join precedence

What the engine does with a repeated or constant argument

A LATERAL join is expensive in a way its syntax hides: the TVF body is re-planned for each left row, not just re-executed. Two things cut that down, and neither changes an answer.

An uncorrelated LATERAL is decorrelated. When no argument reads a left column and the function body is reproducible, every left row would instantiate the same relation, so the optimizer rewrites the join to a plain product over a single TVF call (LATERAL-001):

Customers LATERAL ordersFor(2)   →   Customers × ordersFor(2)

One plan, one execution. The bigger gain is that a × is a shape every other rule understands: a selection above it can become a theta join, filters push into the left input, and the planner costs it normally — none of which it can do with a LATERAL, whose function body is invisible to tree traversal. See optimizer for the rule's exact conditions.

A repeated argument tuple is memoized. When the arguments genuinely are correlated, the engine caches the planned body — and, for a reproducible body within a bounded budget, the rows it produced — under the argument tuple. A lateral over a low-cardinality foreign key hits the same values on row after row, and each repeat then costs a map lookup instead of a planner run. The cache is per query execution and bounded in both entries and retained rows, so a correlation whose values are nearly all distinct falls back to the plain per-row path rather than growing.

Neither applies to a function whose body is not reproducible. A body reading Rand() or NOW(), drawing an unseeded SAMPLE, or calling a view or nested TVF that does, is evaluated once per left row exactly as written — the decorrelation rule declines to fire, and the memo keeps only the plan, which is reusable because planning evaluates nothing. Add a SEED to a sampling body if you want it treated as reproducible.

Worked Example

The manual's usual customers and orders, joined through a function instead of a predicate:

Query
Customers := [
| customer_id | name  |
|-------------|-------|
| 1           | Alice |
| 2           | Bob   |
| 3           | Cara  |
];

Orders := [
| order_id | customer_id | amount |
|----------|-------------|--------|
| 100      | 1           | 120    |
| 101      | 1           | 80     |
| 102      | 2           | 45     |
];

def ordersFor(cid: NUMBER) : RELATION := { σ customer_id = cid (Orders) };

query { Customers LATERAL ordersFor(customer_id) };
Result
 customer_id  name   order_id  customer_id_r  amount
 ───────────  ─────  ────────  ─────────────  ──────
           1  Alice       100              1     120
           1  Alice       101              1      80
           2  Bob         102              2      45
(3 rows)
What the engine did

Rewrites applied

None — the optimiser found nothing to improve.

Physical plan

LateralJoin ordersFor(customer_id)  ~3 rows
└─ Scan Customers  ~3 rows

ordersFor was called once per customer, each time with that customer's own customer_id — which is the whole difference from ordersFor(1), a call that would return the same rows no matter which row it was joined to. Cara produced no rows and therefore contributes none: a lateral join is an inner join.

The case a plain join cannot do. Because the function body is evaluated per outer row, a λ inside it means "per outer row", not "overall" — so the top-order-per-customer question needs no window and no self-join:

Query
def topOrderFor(cid: NUMBER) : RELATION := {
    λ 1 (τ amount DESC (σ customer_id = cid (Orders)))
};

query { Customers LATERAL topOrderFor(customer_id) };
Result
 customer_id  name   order_id  customer_id_r  amount
 ───────────  ─────  ────────  ─────────────  ──────
           1  Alice       100              1     120
           2  Bob         102              2      45
(2 rows)
What the engine did

Rewrites applied

None — the optimiser found nothing to improve.

Physical plan

LateralJoin topOrderFor(customer_id)  ~3 rows
└─ Scan Customers  ~3 rows

One row per customer who has any order, each the largest. Written as an ordinary join with λ 1 above it, the limit would apply to the joined result and return a single row overall.

The same, written in place, keeping Cara. The relation can be written on the right without a function, naming the customer's column qualified, and OUTER keeps the customer who has no order:

Query
query { Customers OUTER LATERAL (λ 1 (τ amount DESC (σ customer_id = Customers.customer_id (Orders)))) };
Result
 customer_id  name   order_id  customer_id_r  amount
 ───────────  ─────  ────────  ─────────────  ──────
           1  Alice       100              1     120
           2  Bob         102              2      45
           3  Cara   NULL      NULL           NULL
(3 rows)
What the engine did

Data flow

Customers
customer_idname
1Alice
2Bob
3Cara
Orders
order_idcustomer_idamount
1001120
101180
102245
Result
customer_idnameorder_idcustomer_id_ramount
1Alice1001120
2Bob102245
3CaraNULLNULLNULL

Rewrites applied

  • LIM-003 Limit over sort fused into TOP (top-N) limit over sort fused into TOP 1 (bounded heap instead of a full sort)

Physical plan

LateralJoin OUTER (expression)(Customers.customer_id)  ~3 rows
└─ Scan Customers  ~3 rows

Limitations

  • A function's arguments must be scalar and non-NULL: an argument evaluating to NULL, or to a nested struct/array value, produces a runtime error naming the value's kind. There is no literal that denotes NULL, and substituting one that did not mean NULL would run the function on a value the outer row never held.
  • Pushdown: over two tables of one database, a lateral whose right side filters a table by the left row is pushed as a join (SUBQ-006), and the first row of an ordered one, λ 1 (τ k (σ θ (S))), as the dialect's LEFT JOIN LATERAL … LIMIT 1 or OUTER APPLY. Any other lateral, and every function-call lateral, runs in the engine, its right side's tables still read through pushdown.
  • No recursion: a TVF may not call itself (directly or transitively) via LATERAL.
  • A base relation is assumed stable for the duration of a query. The rewrites above reuse one evaluation of the function body, which re-reads whatever tables it scans. That is the same assumption ×, every hash join, ÷ and the set operations already make when they materialise one side and replay it.
  • No alias: the right side's columns join the row under their own names, a clashing one suffixed _r; SQL's LATERAL (…) AS x is refused.

Alternatives

A plain TVF call (Relation ⋈ myFn(constant)) works when the argument does not depend on the outer row. Use LATERAL when the per-row column reference is the point.

See Also

def … : RELATION, natural-join, theta-join, semi-join, subquery