Kusto equivalent of SQL NOT IN

Question

I am trying to identify what records exist in table 1 that are not in table 2 (so essentially using NOT IN)

let outliers =
Table 2
| project UniqueEventGuid;
Table 1
|where UniqueEventGuid !in  (outliers)
|project UniqueEventGuid

but getting 0 records back even though I know there are orphans in table 1. Is the !in not the right syntax?

Thanks in advance!

Answer 1

!in operator

"In tabular expressions, the first column of the result set is selected."

In the following example I intentionally ordered the column such that the query will result in error due to mismatched data types.

In your case, the data types might match, so the query is valid, but the results are wrong.

let t1 = datatable(i:int, x:string)[1,"A", 2,"B", 3,"C" ,4,"D" ,5,"E"];
let t2 = datatable(y:string, i:int)["d",4 ,"e",5 ,"f",6 ,"g",7];
t1
| where i !in (t2)

Relop semantic error: SEM0025: One of the values provided to the '!in' operator does not match the left side expression type 'int', consider using explicit cast

Fiddle

If that is indeed the case, you can reorder the columns or project only the relevant one.
Note the use of double brackets.

let t1 = datatable(i:int, x:string)[1,"A", 2,"B", 3,"C" ,4,"D" ,5,"E"];
let t2 = datatable(y:string, i:int)["d",4 ,"e",5 ,"f",6 ,"g",7];
t1
| where i !in ((t2 | project i))

i	x
1	A
2	B
3	C

Fiddle

Another option is to use leftanti join

let t1 = datatable(i:int, x:string)[1,"A", 2,"B", 3,"C" ,4,"D" ,5,"E"];
let t2 = datatable(y:string, i:int)["d",4 ,"e",5 ,"f",6 ,"g",7];
t1
| join kind=leftanti t2 on i

i	x
2	B
3	C
1	A

Fiddle