Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-performance by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group