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