odd issue with IN statement

Some info

We recently moved a database to SQL2005 (start of year) a program I wrote is having a small issue(not rejecting unmatched orders) and I narrowed it to one command statement (that I tried directly in the management studio)

I have two databases on the server (a live and a test DB) on the test DB (same tables and views as live but the data is a bit different) side there is no issue, while on the live side it is not working.

the command is

update wip set haserr=[order xref] where [order xref] not in (select [order xref] from m_orders)

on the test database if I add in the wip table a bad order number [order xref] it won't find it and it flags the haserr column and a reject statement is created for that order.

On the live database the haserr is not changed and so the filling of the order is not rejected when it needs to. It looks to be an issue with the "not in" command

PS at first I thought it was an update issue, but if I do

select * form wip where [order xref] not in (select [order xref] from m_orders)

edit: did some more testing

update wip set haserr=[order xref] where [order xref] in (select [order xref] from m_orders)

works

update wip set haserr=[order xref] where [order xref] not in (select [order xref] from m_orders)

does not

I get null

My knee-jerk reaction to this is to change NOT IN syntax into NOT EXISTS syntax.

update wip

set haserr=[order xref]

from wip a

inner join m_orders b

on a.[order xref] = b.[order xref]

There are issues with NOT IN when your field is null that can cause the results to be different than the way you might intuitively expect. When the result is a NULL it cannot be determined whether the record is IN or NOT IN the set because the NULL means unknown. There have been previous threads that discuss this; I will try to find a couple.

Here are a couple of posts:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=299702&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=607796&SiteID=1

|||

It is bcs of your null value on m_orders table..

(Ex)

Select * From MYTable Where Id in (1,3,4,null)

Will return 1,3,4

Select * From MyTable Where Id not in (1,3,4,null)

will not return any value

Why?

The not in (1,3,4,null) = not (Id =1 or Id =3 or Id=4 or Id=null)

= id <> 1 and Id <> 3 and Id <> 4 And id <> null

= True And True And True And NULL

= True And NULL

= FALSE

ie, ALWAYS FALSE

The in (1,3,4) = (Id =1 or Id =3 or Id=4 or Id=null)

= True or False Or null

= True Or NULL

= True

So change your query as follow as,

update wip set haserr=[order xref] where [order xref] not in (select [order xref] from m_orders Where [order xref] is NOT NULL)

|||NULL fails it, NULL values cant be compared.........