inner join

  • Article

Applies to: ✅ Azure Data Explorer ✅ Azure Monitor ✅ Microsoft Sentinel

The inner join flavor is like the standard inner join from the SQL world. An output record is produced whenever a record on the left side has the same join key as the record on the right side.

Diagram that shows how the join works.

Syntax

LeftTable | join kind=inner [ Hints ] RightTable on Conditions

Learn more about syntax conventions.

Parameters

Name Type Required Description
LeftTable string ✔️ The left table or tabular expression, sometimes called the outer table, whose rows are to be merged. Denoted as $left.
Hints string Zero or more space-separated join hints in the form of Name = Value that control the behavior of the row-match operation and execution plan. For more information, see Hints.
RightTable string ✔️ The right table or tabular expression, sometimes called the inner table, whose rows are to be merged. Denoted as $right.
Conditions string ✔️ Determines how rows from LeftTable are matched with rows from RightTable. If the columns you want to match have the same name in both tables, use the syntax ON ColumnName. Otherwise, use the syntax ON $left.LeftColumn == $right.RightColumn. To specify multiple conditions, you can either use the "and" keyword or separate them with commas. If you use commas, the conditions are evaluated using the "and" logical operator.

Tip

For best performance, if one table is always smaller than the other, use it as the left side of the join.

Hints

Parameters name Values Description
hint.remote auto, left, local, right See Cross-Cluster Join
hint.strategy=broadcast Specifies the way to share the query load on cluster nodes. See broadcast join
hint.shufflekey=<key> The shufflekey query shares the query load on cluster nodes, using a key to partition data. See shuffle query
hint.strategy=shuffle The shuffle strategy query shares the query load on cluster nodes, where each node processes one partition of the data. See shuffle query

Returns

Schema: All columns from both tables, including the matching keys.
Rows: Only matching rows from both tables.

Example

let X = datatable(Key:string, Value1:long)
[
    'a',1,
    'b',2,
    'b',3,
    'k',5,
    'c',4
];
let Y = datatable(Key:string, Value2:long)
[
    'b',10,
    'c',20,
    'c',30,
    'd',40,
    'k',50
];
X | join kind=inner Y on Key

Output

Key Value1 Key1 Value2
b 3 b 10
b 2 b 10
c 4 c 20
c 4 c 30
k 5 k 50

Note

  • (b,10) from the right side, was joined twice: with both (b,2) and (b,3) on the left.
  • (c,4) on the left side, was joined twice: with both (c,20) and (c,30) on the right.
  • (k,5) from the left and (k, 50) from the right was joined once.

Related content