When is NULL not a value

Recently I’ve seen a few posts about the behavior of NULL values in NOT IN clauses, well I did some investigating. 

So the problem can only really be explained by an example so please read on

I’ve created a test table mytable and dropped some test data in it

--Create some test data

CREATE table mytable (
  companyid int NOT NULL,
  name varchar(200) NOT NULL,
  company varchar(200) NULL
)

INSERT dbo.mytable VALUES( 1, 'fred bloggs', 'freds autos')
INSERT dbo.mytable VALUES( 2, 'Mickey Mouse', 'Disney')
INSERT dbo.mytable VALUES( 2, 'Minnie Mouse', 'Disney')
INSERT dbo.mytable VALUES( 3, 'Daffy Duck', null)

This select below, you would expect it to returns any rows where the company is disney

SELECT * FROM dbo.mytable AS m WHERE company IN ('disney')

And it does

This select should return any company that is not disney

SELECT * FROM dbo.mytable AS m WHERE company NOT IN ('disney')

And it does return company 1 ‘freds autos’ but it hasn’t returned company 3 where the company name is null 

Thus as you might guess the below queries aren’t going to return anything

SELECT * FROM dbo.mytable AS m WHERE company IN (NULL)

SELECT * FROM dbo.mytable AS m WHERE company NOT IN (NULL)

And they don’t, searching for a null value during an IN or NOT IN isnt going to behave as expected.

Thus using two values in the IN clause basically behaves the same 

SELECT * FROM dbo.mytable AS m WHERE company IN ('disney',null)

SELECT * FROM dbo.mytable AS m WHERE company NOT IN ('disney',null)

So what’s going wrong, firstly this is bad practice and also confusing to read and understand unless you’re an expert in your logic operations and using IN and NOT IN. 

But mainly the NULL is what’s wrong, It’s not acting as a value and thus you can’t compare it as a value.

lets run the result with ANSI_NULLS set OFF

SET ANSI_NULLS OFF

SELECT * FROM dbo.mytable AS m WHERE company IN ('disney')

SELECT * FROM dbo.mytable AS m WHERE company NOT IN ('disney')

SELECT * FROM dbo.mytable AS m WHERE company IN (NULL)

SELECT * FROM dbo.mytable AS m WHERE company NOT IN (NULL)

SELECT * FROM dbo.mytable AS m WHERE company IN ('disney',null)

SELECT * FROM dbo.mytable AS m WHERE company NOT IN ('disney',null)

So as you can see below its now all behaving correctly the NULL is acting as a value and you can compare directly against it

The official definition of ANSI_NULL 

When you compare two NULL expressions, the result depends on the ANSI_NULLS setting:

If ANSI_NULLS is set to ON, the result is NULL¹, following the ANSI convention that a NULL (or unknown) value is not equal to another NULL or unknown value.

If ANSI_NULLS is set to OFF, the result of NULL compared to NULL is TRUE.

Comparing NULL to a non-NULL value always results in FALSE².

However, both ¹ and ² are incorrect – the result of both comparisons is UNKNOWN.