Re: slow query - where not in

On Fri, Mar 28, 2003 at 09:38:50 -0600,
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)

This will probably work better in 7.4.

For now there are several ways to rewrite this query.

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)

You can use set difference to calculate the result:
select shoporder from sodetailtabletrans except all select
shoporder from soheadertable

If there are no null values for shoporder in one of sodetailtabletrans
or soheadertable you can user an outer join with a restriction that limits
the rows of interest to those that don't match:
select sodetailtabletrans.shoporder from sodetailtabletrans left join
soheadertable using (shoporder) where soheadertable.shoporder is null