Re: slow query - where not in

From: "Michael Paesold" <mpaesold(at)gmx(dot)at>
To: "Bruno Wolff III" <bruno(at)wolff(dot)to>, "Jeremiah Elliott" <jelliott(at)artcobell(dot)com>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: slow query - where not in
Date: 2003-03-28 16:53:46
Message-ID: 028e01c2f54a$9af3d010$3201a8c0@beeblebrox
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Bruno Wolff III <bruno(at)wolff(dot)to> wrote:

> Jeremiah Elliott <jelliott(at)artcobell(dot)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

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Ulli Mueckstein 2003-03-28 17:04:47 calling analyze from a stored procedure in C
Previous Message Greg Stark 2003-03-28 16:20:29 Re: slow query - where not in