Not In Query suddenly stops working

Question

Post reply

Not In Query suddenly stops working

Kirk Lugar

SSC Journeyman

Points: 78
More actions
July 21, 2008 at 7:52 am

#70467

We have been running the following query for about a year.
insert into dbo.users select distinct user_id as UserID from dbo.unsorted
where user_id not in (select userid from dbo.users)
On Friday the 'Not In' portion of this query stopped returning any values and thus the insert query started returning 0 inserts. I would manually run the select portion of this query and it would not return anything. However, I could manually find records that were in one table and not the other.
I changed the 'not in' to a left join and it would insert the records normally.
One last piece of evidence..the system admin of the sql server box change some user rights of the AD group that the installer of SQL server was a member of. I'm not sure that had anything to do with it. We went ahead and had him change it back but the 'not in' query is still not working correctly.
Thanks!!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

RyanRandall SSChampion Points: 13623 More actions · Answer 1

There's not a null userid in dbo.users, is there?

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Kirk Lugar SSC Journeyman Points: 78 More actions · Answer 2

Now that you mention it Ryan..there is one lowly null in that table.

RyanRandall SSChampion Points: 13623 More actions · Answer 3

Hehe - lowly indeed. There's your problem.

Some reading material...

http://sqlblogcasts.com/blogs/jonsayce/archive/2007/12/03/using-where-not-in-select-with-nulls.aspx

http://drsql.spaces.live.com/blog/cns%2180677FB08B3162E4%21777.entry

http://sqlblog.com/blogs/hugo_kornelis/archive/2007/09/22/dr-unknown-or-how-i-learned-to-stop-worrying-and-love-the-null.aspx

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Kirk Lugar SSC Journeyman Points: 78 More actions · Answer 4

Thanks Ryan! As soon as I saw your post and I found that null it all came crashing back to me. 🙂 That table didn't SUPPOSED to have nulls in there and frankly I didn't look. My bad. :0

tbeadle SSCommitted Points: 1635 More actions · Answer 5

You may have already done this...but I would recommend you change the table definition for this column that should not allow nulls.

lienok Newbie Points: 9 More actions · Answer 6

thank you soo much .. I found after one month NULL record.. :w00t::)

my nightmare is over:hehe: