CODEWITHSUNDEEP
sqlms-access
abapme
abapme

Reputation: 3

Is there a more performant way of filtering thata out than NOT IN?

I have a table with two columns. One Column is the Material number and the other one is the Language. Some Material numbers have multiple Languages and I would like to only get those that do not have a 'DE' or 'EN' entry. So basically as soon as a material number has a 'DE' or 'EN' in the lang field it should not be displayed.

This is the Query that I tried using: 

select * from table where matnr not in (select matnr from table where lang = "DE" or lang = "EN")

However the Data I am trying to analyze has over a million entries which takes really really long.

The weird thing is that I even 

select top 1 * from table where matnr not in (select matnr from table where lang = "DE" or lang = "EN")

seems to take a really long time.

Am I doing something wrong here? Does anyone have an idea how I could achieve the same thing in a way that is more performant?

Thank you for your help!

Upvotes: 0

Views: 41

Answers (2)

Lee Mac
Lee Mac

Reputation: 16015

You could try using a left join coupled with a where is null clause, e.g.:

select top 1 t1.*
from 
    table t1 left join 
    (
        select distinct t2.matnr
        from table t2 where t2.lang = 'DE' or t2.lang = 'EN'
    ) 
    on t1.matnr = t2.matnr
where 
    t2.matnr is null

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270483

One method is not exists with an index on (matnr, lang):

select t.*
from table as t
where not exists (select 1
                  from table as t2
                  where t2.matnr = t.matnr AND
                        t2.lang = "DE"
                 ) and
      not exists (select 1
                  from table as t2
                  where t2.matnr = t.matnr AND
                        t2.lang = "EN"
                 ) ;

Another would use aggregation and INNER JOIN:

select t.*
from table as t inner join
     (select t2.matnr
      from table as t2
      group by t2.matnr
      having sum(iff(lang in ("DE", "EN"), 1, 0)) = 0
     ) as t2
     on t.matnr = t2.matnr;

Upvotes: 1

Related Questions