The result of [not] in (null) will always be null. To compare to null you need is [not] null or is [not] distinct from null select * from Entity this_ where this_.ID is not null If you want where (ID not in (1,null)) as in your comment you can do where ID is not null … Read more
It’s because of the way NOT IN works. To avoid these headaches (and for a faster query in many cases), I always prefer NOT EXISTS: SELECT * FROM Table1 t1 WHERE NOT EXISTS ( SELECT * FROM Table2 t2 WHERE t1.MAKE = t2.MAKE AND t1.MODEL = t2.MODEL AND t1.[Serial Number] = t2.[serial number]);
This article: NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: SQL Server may be if interest to you. In a couple of words, this query: SELECT d1.short_code FROM domain1 d1 LEFT JOIN domain2 d2 ON d2.short_code = d1.short_code WHERE d2.short_code IS NULL will work but it is less efficient than a NOT … Read more
Query A is the same as: select ‘true’ where 3 = 1 or 3 = 2 or 3 = 3 or 3 = null Since 3 = 3 is true, you get a result. Query B is the same as: select ‘true’ where 3 <> 1 and 3 <> 2 and 3 <> null When … Read more
I always default to NOT EXISTS. The execution plans may be the same at the moment but if either column is altered in the future to allow NULLs the NOT IN version will need to do more work (even if no NULLs are actually present in the data) and the semantics of NOT IN if … Read more