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...