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

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)

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)

StackExchange Database Administrators Q#48643, answer score: 11