Re: IN vs EXIIST

Henshall, Stuart - WCP wrote:
>
> It might be interesting to try removing the distinct clause from the
> first query and seeing what difference that makes, both real and
> supposed. Also maybe try seeing what difference it would make to the
> EXISTS sub query to have distinct invoice_id rather than *.

Removing or addin a distinct did not change the results but ...

Your query with EXISTS is this:

psql TMP -c "select count(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'
) "

I am no SQL expert so I took it as face value (especially since I don't
quite underst EXISTS).

But I removed this "AND invoice_li.invoice_id=sq_inv_li.invoice_id "
from the subquery and I got the correct count *and* the same speed as
the IN query ...

Jc