Search code examples
sqloracle-databaseselectmultiple-select

Multiple SELECT with NOT IN - inner Select statement returns empty and the whole SELECT returns empty

There are two table TABLE1 and TABLE2 in which there is a common field ID. I wanted to retrieve values from TABLE2 that doesnot match in TABLE1 based on ID value.

select * from TABLE2 where subject = 1 and ID NOT IN (select ID from TABLE1 where subject = 1)

Sample:

TABLE1
ID SUBJECT
1 1

TABLE2
ID SUBJECT
1 1
2 1

The expected result is 2 and it works fine.

But when TABLE1 is empty or the inner select ID from TABLE1 where subject = 1 returns empty, the whole select statement returns empty. But the expected result is 1, 2

Is there any way to achieve this ?

Solution

  • Use a left join

    select t2.* 
from table2 t2
left outer join table1 t1 on t1.id = t2.id and t1.subject = 1
where t2.subject = 1
and t1.id is null

    See a good explanation of joins