"Not in" suddenly stops working??? 2

[jenlion]

I have a view that has been running for six months with no problems. Suddenly today it stops working.

I have a table tsoPackage that has a packagekey int. I have another table (call it tableB) with a packagekey varchar(20). They both must be this data type -- an application requires packagekey int, and UPS only writes back character values to sql.

I'm selecting a bunch of stuff from tsoPackage where packagekey not in (select packagekey from tableB).

This used to work FINE. We're up to packagekey 51470 now. Now, selecting * from tsoPackage where packagekey not in (select packagekey from tableB) returns nothing, even though the packagekey is not in tableB.

I tried, for kicks, selecting convert(int,packagekey) as Packagekey into #t, and then select * from tsopackage where packagekey not in (select packagekey from #t), and THAT doesn't get me anything, so it's not the int-varchar thing.

This makes NO sense to me. Why would "not in" suddenly fail for everything?

For now I've modified the view to select stuff from tsopackage p left join TableB b on p.packagekey = b.packageey where b.packagey is null. That's working fine.

But I'm concerned. This shouldn't just quit working.

Here's another weird one. If I select * from tableB, there are 41705 records. If I select * from tableB where packagekey in (select packagekey from tableB), I only get 41704. Change the "in" to "not in" and there are no results. Where's the missing record??

Someone point me in the right direction here? Never seen this??

Thanks!

 

[gmmastros]

I bet it's a null problem. I encourage you to read this:

http://sqlblog.com/blogs/denis_gobo/archive/2008/02/20/5169.aspx

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jenlion]

George! I think you are right. Somehow, since I posted, the problem appears fixed -- if I select * from tableb where packagekey in (select packagekey from tableb), the # of results is the same as select * from tableb. And, "not in" works again.

But, I checked the tabledef, and nulls ARE allowed. My bet is something null got in there somehow, and has since been removed.

THANK YOU for the pointer on that. I didn't realize that a table with a null value would preclue using not in, a favorite shortcut of mine. I will keep that in mind for future designs -- I can write better queries or insist on better tables. Woohoo! Learned something useful today! Thanks!

 

[markros]

Funny, I was just going to suggest exactly this link after reading it a moment ago

 

[markros]

Actually, the other link

http://blogs.lessthandot.com/index..../the-ten-most-asked-sql-server-questions--1#4

 

[jenlion]

That's a great link too. I'm going to use that the next time a coworker asks me to do something for them: Here, use this!

Thanks!