innerunique join

  • Article

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

The innerunique join flavor removes duplicate keys from the left side. This behavior ensures that the output contains a row for every combination of unique left and right keys.

By default, the innerunique join flavor is used if the kind parameter isn't specified. This default implementation is useful in log/trace analysis scenarios, where you aim to correlate two events based on a shared correlation ID. It allows you to retrieve all instances of the phenomenon while disregarding duplicate trace records that contribute to the correlation.

Diagram that shows how the join works.

Syntax

LeftTable | join kind=innerunique [ 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: All deduplicated rows from the left table that match rows from the right table.

Examples

Use the default innerunique join

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

Output

Key Value1 Key1 Value2
b 2 b 10
c 4 c 20
c 4 c 30

Note

The keys 'a' and 'd' don't appear in the output, since there were no matching keys on both left and right sides.

The query executed the default join, which is an inner join after deduplicating the left side based on the join key. The deduplication keeps only the first record. The resulting left side of the join after deduplication is:

Key Value1
a 1
b 2
c 4

Two possible outputs from innerunique join

Note

The innerunique join flavor may yield two possible outputs and both are correct. In the first output, the join operator randomly selected the first key that appears in t1, with the value "val1.1" and matched it with t2 keys. In the second output, the join operator randomly selected the second key that appears in t1, with the value "val1.2" and matched it with t2 keys.

let t1 = datatable(key: long, value: string)  
    [
    1, "val1.1",  
    1, "val1.2"  
];
let t2 = datatable(key: long, value: string)  
    [  
    1, "val1.3",
    1, "val1.4"  
];
t1
| join kind = innerunique
    t2
    on key

Output

key value key1 value1
1 val1.1 1 val1.3
1 val1.1 1 val1.4 
let t1 = datatable(key: long, value: string)  
    [
    1, "val1.1",  
    1, "val1.2"  
];
let t2 = datatable(key: long, value: string)  
    [  
    1, "val1.3", 
    1, "val1.4"  
];
t1
| join kind = innerunique
    t2
    on key

Output

key value key1 value1
1 val1.2 1 val1.3
1 val1.2 1 val1.4
  • Kusto is optimized to push filters that come after the join, towards the appropriate join side, left or right, when possible.
  • Sometimes, the flavor used is innerunique and the filter is propagated to the left side of the join. The flavor is automatically propagated and the keys that apply to that filter appear in the output.
  • Use the previous example and add a filter where value == "val1.2" . It gives the second result and will never give the first result for the datasets:
let t1 = datatable(key: long, value: string)  
    [
    1, "val1.1",  
    1, "val1.2"  
];
let t2 = datatable(key: long, value: string)  
    [  
    1, "val1.3", 
    1, "val1.4"  
];
t1
| join kind = innerunique
    t2
    on key
| where value == "val1.2"

Output

key value key1 value1
1 val1.2 1 val1.3
1 val1.2 1 val1.4

Get extended sign-in activities

Get extended activities from a login that some entries mark as the start and end of an activity.

let Events = MyLogTable | where type=="Event" ;
Events
| where Name == "Start"
| project Name, City, ActivityId, StartTime=timestamp
| join (Events
    | where Name == "Stop"
        | project StopTime=timestamp, ActivityId)
    on ActivityId
| project City, ActivityId, StartTime, StopTime, Duration = StopTime - StartTime

let Events = MyLogTable | where type=="Event" ;
Events
| where Name == "Start"
| project Name, City, ActivityIdLeft = ActivityId, StartTime=timestamp
| join (Events
        | where Name == "Stop"
        | project StopTime=timestamp, ActivityIdRight = ActivityId)
    on $left.ActivityIdLeft == $right.ActivityIdRight
| project City, ActivityId, StartTime, StopTime, Duration = StopTime - StartTime

Related content