join

Combines rows from two tables by matching values on a specified key column. Supports multiple join kinds.

Spec

<left_table>
| join kind=<join_kind> (<right_table>) on <key_column>

Parameters

join_kind    - The type of join (see below). Default is inner.
right_table  - The right-side table expression, enclosed in parentheses.
key_column   - The column name to match between left and right tables.

Join Kinds

Kind	Description
innerunique	Inner join with left-side deduplication on the key (default).
inner	Rows that match in both tables.
leftouter	All left rows; matched right columns or null.
rightouter	All right rows; matched left columns or null.
fullouter	All rows from both tables; null where no match.
leftsemi	Left rows that have a match in the right table.
leftanti	Left rows that have no match in the right table.
rightsemi	Right rows that have a match in the left table.
rightanti	Right rows that have no match in the left table.
cross	Cartesian product of both tables (no on clause required).

Return Value

A table with columns from both sides, combined according to the join kind.

Example

Inner join

datatable(Id:int64, Name:string)[1, "Alice", 2, "Bob", 3, "Carol"]
| join kind=inner (
    datatable(Id:int64, Score:int64)[1, 85, 2, 92, 4, 70]
) on Id

Id	Name	Score
1	Alice	85
2	Bob	92

Left anti join

datatable(Id:int64, Name:string)[1, "Alice", 2, "Bob", 3, "Carol"]
| join kind=leftanti (
    datatable(Id:int64, Score:int64)[1, 85, 2, 92]
) on Id

Id	Name
3	Carol