Joins

Joins appear in a one-shot SELECT, which reads every side once; in a STREAM SELECT, which keeps the joined result live; and in the body of a transformation, which keeps a target table current as any side changes. The examples join transfers, balances and token_prices.

Join grammar

SELECT <a>.<field> [AS <name>], <b>.<field> [AS <name>], …
FROM <left> [AS] <a>
     INNER JOIN | LEFT JOIN | RIGHT JOIN | FULL JOIN | CROSS JOIN
   | ASOF LEFT JOIN | LOOKUP JOIN
     <right> [AS] <b>
     [ON <a>.<field> = <b>.<field> [AND …]]
     [HINT <strategy>]

• ON holds equalities between the sides joined by AND. An ASOF LEFT JOIN adds one inequality on a time field (<= or >=). CROSS JOIN takes no ON.
• Outputs are field references or expressions over the joined fields.
• HINT names a physical strategy, such as HINT BROADCAST.

Joins in a one-shot SELECT

A one-shot SELECT reads a consistent snapshot of each side and returns the joined rows.

SELECT t.token AS token, t.amount AS amount, p.price_usd AS price_usd
FROM transfers AS t
INNER JOIN token_prices AS p ON t.chain_id = p.chain_id AND t.token = p.token;

SELECT
  b.address AS address,
  b.token AS token,
  b.amount * p.price_usd AS value_usd
FROM balances AS b
LEFT JOIN token_prices AS p ON b.chain_id = p.chain_id AND b.token = p.token;

Joins chain over several relations in one statement. An ASOF LEFT JOIN picks, for each left row, the latest right row whose time is not after the left row's time:

SELECT t.token AS token, t.amount AS amount, p.price_usd AS price_usd
FROM transfers AS t
ASOF LEFT JOIN token_prices AS p
  ON t.chain_id = p.chain_id
    AND t.token = p.token
    AND p.priced_at <= t.transferred_at;

Joins in a transformation

INNER, LEFT, RIGHT and FULL JOIN

Every side is retained. A change on any side re-derives the affected joined rows, and the target is updated to match.

ASOF LEFT JOIN

Each left row joins the right row, per key, whose time is the latest not after the left row's time; with no such row, the right-side outputs are NULL.

CREATE TRANSFORMATION transfer_value_asof INTO transfer_values AS
SELECT
  t.chain_id AS chain_id,
  t.token AS token,
  t._index AS _index,
  t.amount AS amount,
  p.price_usd AS price_usd
FROM transfers t
ASOF LEFT JOIN token_prices p
  ON p.chain_id = t.chain_id
    AND p.token = t.token
    AND p.priced_at <= t.transferred_at
LATE DIMENSION GATE;

LATE DIMENSION sets what happens when a right-side revision arrives after left rows it would have matched: GATE keeps rows already emitted and applies the revision to later left rows, PARK holds left rows until the dimension catches up, and RETRACT corrects rows already emitted.

LOOKUP JOIN

A LOOKUP JOIN enriches a stream of events with the current value of a keyed relation.

• The event side (left) drives: every event emits exactly one row.
• The lookup (right) is read by key when the event is processed. With no match, the lookup's fields are NULL.
• A change to the lookup applies to the next event for that key; rows already written keep the value they were written with.
• ON equates the lookup's primary key, column for column.

CREATE TRANSFORMATION transfer_value_lookup INTO transfer_values AS
SELECT
  t.chain_id AS chain_id,
  t.token AS token,
  t._index AS _index,
  t.amount AS amount,
  p.price_usd AS price_usd
FROM transfers t
LOOKUP JOIN token_prices p ON p.chain_id = t.chain_id AND p.token = t.token;

After a restart, a LOOKUP JOIN resumes from its event-side cursor.

Computed join outputs

A join output may be an expression over fields of every side, such as an amount times a price. On a keyed LEFT JOIN, a change to either side recomputes the output:

CREATE TRANSFORMATION balance_values INTO balance_values AS
SELECT
  b.chain_id AS chain_id,
  b.address AS address,
  b.token AS token,
  b.amount * p.price_usd AS value_usd
FROM balances b
LEFT JOIN token_prices p ON p.chain_id = b.chain_id AND p.token = b.token;