Search code examples
sqlsubqueryquery-optimization

I want to optimize SQL query where different columns of same table are compared with columns of another table in or condition

I have 2 tables (t1 , t2) with columns A1, A2, A3. I want to write a SQL query which returns the rows in table t1, for which A1 column value is same as table t2 but the A2 or A3 or both column value is different.

This query does the job, but does not seem to be optimized:

select t1.A1, t1.A2, t1.A3 
from t1 
where t1.A3 not In (select A3 from t2 where t2.A1 = t1.A1) 
   or t1.A2 not In (select A2 from t2 where t2.A1 = t1.A1);
Solution

  • You can do:

    select *
from t1
where exists (
  select 1 
  from t2 
  where t2.a1 = t1.a1 and (t2.a2 <> t1.a2 or t2.a3 <> t1.a3)
)

    To improve the query performance you can add the index:

    create index ix1 on t2 (a1, a2, a3);