behavior of in operator with null values

[S.S.]

I don't quite understand the expected behavior of the in operator when
used with a list that contains the null value. The following is some
short sample code, which returns an empty result set. My expection
would be a one row result set:

create table TestTable (
TestColumn nvarchar(10)
)

insert into TestTable (TestColumn) values ('Test')

select * from TestTable where TestColumn not in (null)

Obviously this is a silly example but scales to any list that is a
query that could return null.

Can anyone clarify, or point me to some good docs on the behavior of
the in operator? BOL didn't seem to have anything specific on this
behavior.

TIA.

[Serge Rielau]

S.S. wrote:
> I don't quite understand the expected behavior of the in operator when
> used with a list that contains the null value. The following is some
> short sample code, which returns an empty result set. My expection
> would be a one row result set:
>
>
> create table TestTable (
> TestColumn nvarchar(10)
> )
>
> insert into TestTable (TestColumn) values ('Test')
>
> select * from TestTable where TestColumn not in (null)

Let's rewrite the query:
SELECT * FROM TestTable WHERE NOT (testColumn = NULL)

A row is returned if the WHERE clause is TRUE
In order for this to be TRUE testColumn = NULL must be FALSE

However 'Test' = NULL is UNKNOWN and which is different from FALSE
That is why there is an IS NULL predicate.

Here is a treatise on the dilemma of NULL:
http://www.dbdebunk.com/page/page/1706814.htm

Cheers
Serge
--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

[Plamen Ratchev]

Take a look at the first case in this article, it explains the issue in detail with different methods to handle it:
http://www.simple-talk.com/sql/t-sql-programming/ten-common-sql-programming-mistakes/

--
Plamen Ratchev
http://www.SQLStudio.com