From BOL 2005 for IN
Caution:
Any null values returned by subquery or expression that are compared to test_expression using IN or NOT IN return UNKNOWN. Using null values in together with IN or NOT IN can produce unexpected results.

>
>I am having a problem I have not encountered before. I am working with a new client database in SQL Server 2000, SP4. When I run the following query, zero results are returned:
>
>

>SELECT * FROM pcalot
>WHERE clotpk NOT IN (SELECT cbcnlotfk FROM pcabcn)
>

>
>However, if I perform one of the following, I get the expected result set returned:
>
>

>SELECT * FROM pcalot
>WHERE clotpk NOT IN (SELECT cbcnlotfk FROM pcabcn WHERE cbcnlotfk IS NOT NULL)
>
>OR
>
>SET ANSI_NULLS OFF
>SELECT * FROM pcalot
>WHERE clotpk NOT IN (SELECT cbcnlotfk FROM pcabcn)
>

>
>Something else, I have the same database on another machine running SQL Server 2005 and the first query statement:
>

>SELECT * FROM pcalot
>WHERE clotpk NOT IN (SELECT cbcnlotfk FROM pcabcn)
>

>returned the expected results no matter what the setting of SET ANSI_NULLS.
>
>Does anybody know why SQL Server 2000 is behaving this way. I haven't seen this before until working with this specific database. Is there some type of setting I am overlooking that is different between the database in SQL Server 2000 and SQL Server 2005 or did I get a brain cramp and this has been the behavior in SQL Server 2000 all along?
>
>Any help would be greatly appreciated.
>
>Thanks in advance,
>
>James