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