NOT IN operator issue Oracle

Question

Here is my query:

Select a.* from Table1 a, Table2 b
Where 
a.tid=b.tid and 
b.createddate=(Select max(createddate) from Table2) and
a.tid not in (Select distinct tid from Table3);

The problem is I know this should return some valid output but it does not. The issue us with the last line in the a.tid not in (Select distinct tid from Table3); if I replace Select distinct tid from Table3 with hard coded values like ('T001','T002','T003','T004') then it works fine and returns data.

Whats wrong? Am I missing something? Please help.

Answer 1

Try this:

Select a.* from Table1 a, Table2 b
Where 
a.tid=b.tid and 
b.createddate=(Select max(createddate) from Table2) and
a.tid not in (Select tid from Table3 where tid is not null);

As all the people in the comments mentioned, if there is at least one row with a null value for tid in table3 you will get no rows returned. This is because to Oracle null is like saying "I don't know what this value is". Oracle can't say with certainty that the value you are searching for is definitely not in your sub-select because it doesn't know what this "not-known" value actually is. Also, the documentation says it works that way: http://docs.oracle.com/cd/B28359_01/server.111/b28286/conditions013.htm

Another option would be to write the query as:

Select a.* from Table1 a, Table2 b
Where 
a.tid=b.tid and 
b.createddate=(Select max(createddate) from Table2) and
not exists (Select null from Table3 t3 where t3.tid = a.tid);

The handling of nulls is one of the major differences between not exists and not in.