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

Equality and performance

Use == for exact equality or =~ for case-insensitive string equality. Case-insensitive equality does not remove whitespace or normalize addresses. Multiple conditions must all match. Null join keys do not match other null keys.

Equality joins use hash lookups, including composite and side-local computed keys. Additional conditions are evaluated on candidate matches. Conditions that do not provide a mandatory equality key, such as an inequality or an equality inside an or, use a general comparison path and can be much more expensive.

Filter each input and project the columns needed by the query before joining where possible. For large inputs, memory limits can require temporary partition files; query cancellation and downstream limit release the join's resources. A duplicate key with many rows on both sides still produces every matching combination for inner and outer joins. Use a semi join when only the existence of a match is needed.

LeftTable
| where enabled == true
| project address, region, value
| join kind=inner (
    RightTable
    | project address, region, category
) on $left.address =~ $right.address, $left.region == $right.region

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