leftanti join

Article
08/12/2024

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

The leftanti join flavor returns all records from the left side that don't match any record from the right side. The anti join models the "NOT IN" query.

Aliases: anti, leftantisemi

Syntax

LeftTable | join kind=leftanti [ 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 the left table.
Rows: All records from the left table that don't match records from the right table.

Example

Run the query

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 kind=leftanti Y on Key

Output

Key	Value1
a	1

Partager via