Joins

Relational joins

NYXDB supports inner, left, right, full, and cross joins:

SELECT t.id, t.market, i.display_name
FROM trades AS t
INNER JOIN instruments AS i
  ON t.market = i.market;

SELECT a.account_id, b.balance
FROM accounts AS a
LEFT JOIN balances AS b
  ON a.account_id = b.account_id;

JOIN without a qualifier is an inner join. CROSS JOIN has no ON predicate:

SELECT m.market, d.day
FROM markets AS m
CROSS JOIN calendar_days AS d;

Qualify duplicate column names and use stable aliases in production queries.

ASOF join

An ASOF join matches equality keys and then chooses the nearest compatible row on the earlier side of one ordered inequality. It is useful for attaching the latest quote known when an event occurred:

SELECT t.id, t.observed_at, q.bid, q.ask
FROM trades AS t
LEFT ASOF JOIN quotes AS q
  ON t.market = q.market
 AND t.observed_at >= q.observed_at;

The join condition must contain supported equality keys plus exactly one valid ASOF inequality. Ambiguous or unsupported predicate shapes are rejected. ASOF JOIN and LEFT ASOF JOIN are supported forms.

LATEST join

A latest join resolves the right side by its keyed current state:

SELECT e.account_id, e.amount, a.tier
FROM ledger_events AS e
LEFT LATEST JOIN account_state AS a
  ON e.account_id = a.account_id;

The right source must be keyed. LATEST JOIN and LEFT LATEST JOIN let the planner use that key contract rather than treating the source as an arbitrary history relation.

Operational guidance

• Keep join keys type-compatible.
• Push selective predicates close to their source.
• Bound result cardinality and operator memory at the server.
• Use EXPLAIN to confirm the bound shape.
• Measure large or skewed joins with production-like distributions before launch.

Joins execute inside one NYXDB process. They do not imply distributed exchange or cross-node fault tolerance.