CODEWITHSUNDEEP
sqlselectfirebirdnotin
Luiz Martins
Luiz Martins

Reputation: 21

How to improve select with not in on firebird

select mid from aplicacao
where mid not in
(
select distinct mid from aplicacao
inner join prod_app on prod_app.mid=aplicacao.mid
where prod_app.coditem=1
)

I try to search for a solution on firebird to improve this query but unfortunatelly didn´t have success. Please could someone help me?

Upvotes: 1

Views: 6689

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 109001

The most common solution for IN (and NOT IN) performance problems is to use EXISTS (or NOT EXISTS) instead:

select mid from aplicacao
where not exists (
  select 1 from prod_app
  where prod_app.mid = aplicacao.mid and prod_app.coditem=1
)

Another solution is to use a LEFT JOIN and filter on non-existence of the right side:

select mid from aplicacao
left join prod_app
  on prod_app.mid = aplicacao.mid and prod_app.coditem=1
where prod.app.coditem is null

Note that additional filter conditions on prod_app (like prod_app.coditem=1) need to be part of the join condition, not of the where clause.

Upvotes: 5

Related Questions