Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)

Working Test:

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

Broken Test:

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

share|improve this question
3  
You don't understand NULL. en.wikipedia.org/wiki/Null_(SQL) The query you have written is logically the same as select 1 where 1 not in (null). 1 IS NOT IN NULL because NULL is unknown. –  Phil Aug 24 '13 at 0:55
    
Yeah. Exactly, but if you try those two test sql statements they give different results. I know what null is... –  zeal Aug 24 '13 at 1:20
    
The second test should return 1 because newid() is not null, but it returns nothing. –  zeal Aug 24 '13 at 1:22
4  
You may know what null is, but may I humbly suggest you do not fully understand the implications of it. When null is applied to anything, it cannot be answered. Your broken test is evaluating "true not in unknown" The Unknown pooches everything and thus you get no results –  billinkc Aug 24 '13 at 1:26

4 Answers 4

up vote 8 down vote accepted

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)

share|improve this answer
    
Your explanation does help me understand this, thanks. However, I still find this odd because in any other languages I've used the "stranger" essentially has no name therefor his name cannot be "Tom". It makes sense now also why they use is rather than = to check for nulls. –  zeal Aug 24 '13 at 22:39
    
@zeal - NOT EXISTS behaves more like that. SQL Fiddle. No rows are returned from SELECT * FROM OtherPeople WHERE Name = 'Tom' and NOT EXISTS on an empty set evaluates to true. You might also find the truth tables for Three-valued logic useful. –  Martin Smith Aug 24 '13 at 23:10

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

share|improve this answer

Ignoring the lessons about NULL (which you should observe), NOT IN is a horrible choice for a left anti-semi join. Try:

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

As an aside, your not in version mentioned b.guid, but I see no table aliased as b.

share|improve this answer
SELECT a.guid
FROM   tableA a
WHERE  a.guid IN (SELECT a.guid
                  FROM   tableA a
                         LEFT JOIN tableB b
                  WHERE  b.guid IS NULL) 
share|improve this answer
2  
This isn't valid syntax. Also if it was valid it would still require some explanation as to how it relates to the question. I take it that you meant SELECT a.guid FROM tableA a LEFT JOIN tableB b ON a.guid = b.guid WHERE b.guid IS NULL –  Martin Smith Aug 25 '13 at 11:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.