I’m trying to do a compare between to identical tables structures to launch after some specific kitchen.
I have 2 identical tables structures
select mem.* from t1@InMemoryStorage mem where mem.id NOT IN (select sql.id from t2@sqlcontainer sql)
give me some issues…
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.
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 ?