Re: IN vs EXIIST

Henshall, Stuart - WCP wrote:
>
> select distinct invoice_id from invoice_li where received='true'
> AND shipped='false' AND cancelled='false'
> AND
> (NOT EXISTS
> (
> select * from invoice_li AS sq_inv_li where received='false'
> AND cancelled='false' AND invoice_li.invoice_id=sq_inv_li.invoice_id
> )
> OR ship_now='true'
> )
>
> Should work (but is untested).

I'll test it and let you know. Tanks!

> As a side note there doesn't seem a point to having distinct on the
> subquery in its original form either, so this could be removed to reduce
> overhead.

Really? I though that reducing the number of results in the sub-select
would make the query more efficient since the outer query would have
less items to check.

I.e. it would be faster to check if something is in (1,2,3) than if it
is in (1,2,2,2,2,2,2,2,2,2,3). No?

Let me check that new optimized query!

Jc