CODEWITHSUNDEEP
sqloptimizationruntime
Johnny Banana
Johnny Banana

Reputation: 123

Optimize "NOT IN"

this is my PostgreSQL code:

      Select 'MOBILE' as source, NULL as vtr, NULL as conversions, 
NULL as w, x,y,z from table_1
        where (x,y,z)
            not in        (select x,y,z from table_2)
        UNION
        select 'DESKTOP' as source, vtr, conversions, w,x,y,z
           from table_1;

However, this is waaaay too expensive in terms of duration. Is there a "clean" solution for this? I basically want to exclude all rows in table_1, which also occur in table_2. However, table_2 has 3 more columns (vtr, conversions and w) which is why I do what I'm currently doing. Thanks guys! Found some things here on SOF that didn't really suit my case.

Upvotes: 0

Views: 52

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

I would start with not exists:

Select 'MOBILE' as source, NULL as vtr, NULL as conversions, NULL as w, x, y, z
from table_1 t1
where not exists (select 1 from table_2 t2 where t2.x = t1.x and t2.y = t1.y and t2.z = t1.z)
union all
select 'DESKTOP' as source, vtr, conversions, w, x, y, z
from table_1;

For this, you want to be sure you have an index on table_2(x, y, z).

Upvotes: 1

kgzdev
kgzdev

Reputation: 2885

You can use LEFT JOIN:

Select 'MOBILE' as source, NULL as vtr, NULL as conversions, 
NULL as w, x,y,z from table_1
LEFT JOIN table_2
ON table_1.x = table_2.x and table_1.y = table_2.y and table_1.z = table_2.z
WHERE table_2.x IS NULL and table_2.y IS NULL and table_2.z IS NULL
UNION
select 'DESKTOP' as source, vtr, conversions, w,x,y,z
from table_1;

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

you can try by using join

  Select 'MOBILE' as source, NULL as vtr, NULL as conversions, 
NULL as w, x,y,z from table_1 t1 left join table_2 t2
  on t1.x=t2.x and t1.y=t2.y and t1.z=t2.z
where t2.x  is null  and t2.y is null and t2.z is null 
union
select 'DESKTOP' as source, vtr, conversions, w,x,y,z
from table_1

Upvotes: 1

Related Questions