Hi Sergey,

Thank you for your response. I discovered the same and also discovered the reason why it worked in SQL Server 2005 was because the database, even though from the same client, it hadn't been converted yet (the database originally had no FK constraints so I had to update all empty FK fields with NULL once the FK constraints were applied). Once I updated the 2005 database the same as I had the 2000 database, the same behavior was also seen in 2005. I don't remember encountering this behavior before, but then again, I probably have and wrote the statement the second way and didn't think twice about it. I think this was a case of wondering if a word is spelled correctly, even though it is, sometimes it just don't look like it. Strange how you mind can play tricks on you sometimes.

Thanks again for the response, Sergey. Must appreciated.

James



>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