Odd SQL behavior, I'm wondering why this works the way it does.

Question

Consider the following Transact sql.

DECLARE @table TABLE(val VARCHAR(255) NULL)

INSERT INTO @table (val) VALUES('a')
INSERT INTO @table (val) VALUES('b')
INSERT INTO @table (val) VALUES('c')
INSERT INTO @table (val) VALUES('d')
INSERT INTO @table (val) VALUES(NULL)

select val 
from @table
where val not in ('a') 

I would expect this to return

b, c, d, NULL

but instead it returns

b, c, d

Why is this the case? Is NULL not evaluated? Is NULL somehow in the set 'a'?

Answer 1

to get the null row try:

select val 
from @table
where val not in ('a') OR val IS NULL

try these:

select 1 where null in (null)
select 1 where null in (1)
select 1 where 1 in (null)

all return zero rows

null=null is always false and null=anythng is always false, when testing the NULL row the WHERE clause is WHERE NULL in ('a'), so it is always false.

Answer 2

Null can't be directly compared to anything.

try

select val  
from @table 
where coalesce(val, '') not in ('a')  

Answer 3

Try this and you will get a different answer:

set ansi_nulls off;
DECLARE @table TABLE(val VARCHAR(255) NULL)

INSERT INTO @table (val) VALUES('a')
INSERT INTO @table (val) VALUES('b')
INSERT INTO @table (val) VALUES('c')
INSERT INTO @table (val) VALUES('d')
INSERT INTO @table (val) VALUES(NULL)

select val 
from @table
where val not in ('a')

You have ansi_nulls on and so it treats nulls accordingly.

Answer 4

You can't do equality with NULL in this way.

Consider this

select case when 'a' = NULL then 1 else 0 end
select case when 'a' <> NULL then 1 else 0 end

For above you'll get

result
------
  0
  0

For your query try

 where isnull(val,'') not in ('a') 

Answer 5

The Answer

By default, NULL, to SQL, means "unknown". So, any comparison to "unknown" is "unknown".

For example, if a stranger is standing next to Bob, and you want to ask the question, "Is the stranger's name the same as Bob's?". The answer is unknown.

Since you're asking, "Return any records where the value is not 'A'". When it gets to that NULL column, SQL still says, "I don't know". Since the expression doesn't evaluate to true, that record isn't returned.

The Solution

Any expression that compares NULL to a constant will always be NULL, so use the special IS NULL and IS NOT NULL to test for NULL values.

This will get you b, c, d, and NULL:

SELECT val 
FROM @table
WHERE val NOT IN ('a') OR val IS NULL

The Workaround

You can change the default behavior for NULLs:

SET ANSI_NULLS OFF

Answer 6

This topic is covered well in SQL For Smarties (see page 293) http://books.google.co.uk/books?id=Hi9fMnOoRtAC&pg=PA274&lpg=PA274&dq=celko+isnull&source=bl&ots=tsaWY9U9Zl&sig=akGLCcBA7vnDDACJFaiQh8Zh0hg&hl=en&ei=QZqsS5ypA4G64Qb3lfi3Dw&sa=X&oi=book_result&ct=result&resnum=4&ved=0CBoQ6AEwAw#v=snippet&q=null%20%22not%20in%22&f=false