Re: slow query - where not in

Bruno Wolff III <bruno@wolff.to> wrote:

> Jeremiah Elliott <jelliott@artcobell.com> wrote:
> > here is the query that is killing me:
> >
> > select shoporder from sodetailtabletrans where shoporder not in(select
> > shoporder from soheadertable)
>

> If there are no null values for shoporder in soheadertable or
> sodetailtabletrans you can use not exists instead of not in:
> select shoporder from sodetailtabletrans where shoporder not exists(select
> shoporder from soheadertable)

I think this should rather be:

SELECT shoporder FROM sodetailtabletrans
  WHERE NOT EXISTS (
    SELECT 1 FROM soheadertable
    WHERE soheadertable.shoporder = sodetailtabletrans.shoporder
    )

Regards,
Michael Paesold