Search code examples
sqloracleoracle11gsubqueryquery-optimization

Optimized query to get IDs not present in other tables

I have three tables. T1 is the master and stores unique ID. I want to get the IDs which are not present in t2 and t3 but are there in t1 I have written a query . Not sure if it is the correct and optimizes one. Need help to write the query in a better way than this as there is huge data in all the tables.

Select t1.ID 
  from t1 
 where ID not in ( 
                  Select distinct t2.ID from t2
                   Union
                  Select distinct t3.ID from t3
                 )
   and col2 ='A'
Solution

  • I would use not exists:

    select t1.*
from t1
where
    col2 = 'A'
    and not exists(select 1 from t2 where t2.id = t1.id)
    and not exists(select 1 from t3 where t3.id = t1.id)

    This query should benefit the following indexes:

    t1(col2, id)
t2(id)
t3(id)