CODEWITHSUNDEEP
sqloracle-databaseplsql
ozzboy
ozzboy

Reputation: 2720

NOT IN operator issue Oracle

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.

Upvotes: 8

Views: 82473

Answers (2)

Craig
Craig

Reputation: 5820

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.

Upvotes: 24

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Your query, slightly rewritten:

Select a.*
from Table1 a join
     Table2 b 
     on a.tid=b.tid 
where b.createddate=(Select max(createddate) from Table2) and
      a.tid not in (Select distinct tid from Table3)

What this tells me is that the tid with the maximum create date from Table2 is in Table3.

To test this, get the maximum create date from table2. Then get all records in table1 that correspond to this max. You will find that these are also in table3.

If I had to speculate, you might want the max create date per table in Table2, rather than the overall max.

By the way, in Oracle (and most other databases) the distinct in the last subquery is redundant. The database should be smart enough to remove duplicates in this case.

Upvotes: 0

Related Questions