Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

NOT IN or IN?

I have been having a hard time believing my eyes when I noticed that these queries give different results:

1. select * from TableA a where a.a_id not in (select b.a_id from TableB b)

2. select * from TableA a where not exists (select b.a_id from TableB b where a.a_id=b.a_id )

3. select a.a_id, b.a_id from TableA a
left join TableB b
on a.a_id = b.a_id
where b.a_id is null


1. returns nothing whereas 2. and 3. return the same result set

Isn't it that in theory 1., 2. and 3. must give exactly same result?

Why is that 1. is giving different result than 2. and 3.?

As it turns out TableB contained some entries with NULLs in a_id column, so when NOT IN was evaluated, the presence of NULL in the list of returned values broke the logic, and statement was incorrectly interpreted

Googling gave me the reference to a similar post: http://sqlwithmanoj.wordpress.com/2011/02/15/not-in-not-exists-joins-with-null-values/

oh, devious NOT IN, how art thou misleading...

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.