SELECT * from @container where id NOT IN (List of ids)

Hello
I’m trying to do a compare between to identical tables structures to launch after some specific kitchen.

I have 2 identical tables structures t1@InMemoryStorageand t2@sqlcontainer

Doing a:

select mem.* from t1@InMemoryStorage mem
where mem.id NOT IN (select sql.id from t2@sqlcontainer sql)

give me some issues…

if t2@sqlcontainer is empty then the above query will return 0 result whereas it should return all rows of t1. If I run:

select mem.* from t1@InMemoryStorage mem
where mem.id NOT IN (NULL)

then I get all t1 results, which is OK.

when I delete some rows in t2@sqlcontainer from another sql client (SSMS) I do not get correct results: sometimes records are found, sometimes not.

I tried SET invantive-use-cache false but that does not changes anything.

I found a turnaround by doing a left outer join

select mem.* from t1@InMemoryStorage mem
left outer join t2@sql sql on mem.id = sql.id where sql.id is NULL

Am I doing wrong with the WHERE NOT IN clause or is it not supported to have a NOT IN from a select statement ?

Thanks