CODEWITHSUNDEEP
sqloracle-databaseoracle11gsubqueryquery-optimization
Akki
Akki

Reputation: 37

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'

Upvotes: 0

Views: 189

Answers (2)

GMB
GMB

Reputation: 222582

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)

Upvotes: 4

Littlefoot
Littlefoot

Reputation: 143003

Use only set operators:

SELECT t1.id
  FROM t1
 WHERE t1.col2 = 'A'
MINUS
(SELECT t2.id FROM t2
 UNION
 SELECT t3.id FROM t3
);

Upvotes: 3

Related Questions