# Joins

> Joins in one-shot SELECT, in streaming queries and in transformations — INNER, LEFT, RIGHT, FULL, CROSS, ASOF LEFT and LOOKUP.

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](/docs/sql/create-transformation), which keeps a target table current as any
side changes. The examples join `transfers`, `balances` and `token_prices`.

## Join grammar

```text
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.

```sql
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;
```

```sql
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:

```sql
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.

```sql
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.

```sql
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:

```sql
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;
```

---

Source: https://nyxdb.io/docs/sql/joins/