Query to find GUID's "not in" null containing subquery returns no results

Question

When I have a query that checks if a column of type uniqueidentifer does not exist in a table that has a null value then I get no results back. If the subquery does not return a null it works fine and it only happens when using not in.

I know I can just do a not null check in my subquery, but I am curious why this does not work.

Query Example:

select a.guid from tableA a where a.guid not in (select b.guid from tableB AS b)

Working Test:

select 1 where newid() not in (select newid())

Broken Test:

select 1 where newid() not in (select null)

Answer 1

Just to provide some additional explanation to billinkc's answer.

If null is a trump card you might be wondering why doesn't WHERE 2 IN (2,3, NULL) exhibit the same behavior?

That one works as expected because it evaluates to (2=2) OR (2=3) OR (2=NULL).

Under the rules of three valued logic for Or-ed conditions if any of them evaluate to true the expression is true. Otherwise if any of them evaluate to unknown the expression is unknown. The only other possibility is that all are false in which case the expression evaluates to false.

In order for a row to be returned in SQL the WHERE clause must evaluate to true rather than false or unknown. The above does that.

The expression 1 NOT IN (2,3, NULL) evaluates to (1 <> 2) AND (1 <> 3) AND (1 <> NULL). When conditions are AND-ed all of them must evaluate to true in order for the expression to evaluate to true.

The presence of the NULL in the list guarantees that there will be at least one UNKNOWN and that this will never be the case. Hence the reason for the " NULL pooches it all" behaviour in this context.

To give an analogy here as to why the NOT IN behaviour makes sense.

Three friends Tom, Dick, and Harry are sitting in a railway carriage with a complete stranger whose name is unknown to them.

If Tom is asked "Is your name different from everyone else's in this carriage?" then it is impossible for him to answer with any certainty.

Even though he knows that Tom <> Dick and Tom <> Harry (so the statement might be true) the veracity of the statement overall hinges on the stranger's name and this is not known.

This is analogous to the SQL 'Tom' NOT IN ('Dick', 'Harry', Null)

Answer 2

Blowing up my comment with an example. It really doesn't matter whether you're using a GUID or any other data type. Your filter should evaluate to a true or false condition. In .net/python/java/etc that's what you get. In the SQL world, we get to have tri-state built into everything. It's true, it's false, it's... I don't know.

When a NULL value enters the mix, it's a trump card. It crashes any other value, true or false and says "tough, this is now unknown". Since it cannot give an authoritative answer, it's going to not provide an answer. Whether you feel this is a broken design or not, it merely is.

-- NULL pooches it all
SELECT 1 AS query1 WHERE 1 NOT IN (SELECT NULL);

-- No null, no problem
SELECT 2 AS query2 WHERE 1 NOT IN (2,3,4);

-- Null, we meet again. With the same result
SELECT 3 AS query3 WHERE 1 NOT IN (2,3,4, NULL);

SQLFiddle

Answer 3

Ignoring the lessons about NULL (which you should observe), NOT IN can be a horrible choice for a left anti-semi join even when there are no NULLs involved. See if you can ever get better performance or a better plan from NOT IN compared to NOT EXISTS:

 select a.guid 
   from dbo.tableA AS a 
   where not exists
   (
     select 1 
       from tableB AS b
       where b.guid = a.guid
   );