Can in line views in oracle sql contain "not in" clause in the query?

Question

For example

select *
from t1 
inner join (select * from t2 where t2.id not in (select ID from t2 where city="Paris"))

I tried searching Google. There a lot of examples but none of them uses not in. Plus there are no restrictions specified for an in line view.

score 0 · Accepted Answer · answered Jul 11 '19 at 10:56

Oracle calls subqueries in the FROM clause "inline views".

These are generic SELECT queries. They can contain NOT IN with subqueries. The problem with your query is a lack of ON clause and the use of double quotes for a string constant:

select *
from t1 inner join
     (select *
      from t2
      where t2.id not in (select ID from t2 where city = 'Paris')
---------------------------------------------------------^ single quotes
     ) t2
     on t1.? = t2.?
-----^ on clause

Note: I would discourage you from using NOT IN with subqueries, because they do not work as expected if any returned values are NULL. (If that is the case, then no rows are returned.)

I advise using NOT EXISTS instead.

Can in line views in oracle sql contain "not in" clause in the query?

1 Answers1