Full Outer join in KQL

Question

KQL doesn't seem to have an equivalent for the SQL FULL OUTER JOIN. I want to return all records that don't intersect, in an SQL join it would look like this:

Looking at the join documentation for KQL it seems as though there is no equivalent. What's the best way to achieve this in KQL?

Am I correct in thinking the way to do this would be a union of a leftanti join and a rightanti join?

Answer 1

Yes, you are right, you need to do union of leftanti and rightanti joins:

Example KQL query:

let X1 = datatable(Key:string, Value1:long)
[
    'a',1,
    'b',2,
    'b',3,
    'c',4
];
let Y1 = datatable(Key:string, Value2:long)
[
    'b',10,
    'c',20,
    'c',30,
    'd',40
];
let right= X1 | join kind=rightanti Y1 on Key;
let left= X1 | join kind=leftanti Y1 on Key;
right 
| union left

Output:

Fiddle.