Geek asks and answers

NOT IN Oracle Operator Problem

Here is my request:

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 that I know that this should return some valid output, but that is not the case. The problem with the last line in a.tid not in (select excellent tid Table 3); , if I replace the Select select tid from table 3 with hard-coded values, for example ('T001', 'T002', 'T003', 'T004'), then it works fine and returns the data.

What's wrong? Am I missing something? Please, help.

+5
source share
2 answers

Try the following:

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);

, tid 3, . , Oracle null - " , ". Oracle , , , , , "" . , , : http://docs.oracle.com/cd/B28359_01/server.111/b28286/conditions013.htm

- :

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);

, .

+12

, :

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)

, tid 2 3.

, 2. 1, . , 3.

, 2, .

, Oracle ( ) . , .

0

More articles:


All Articles