Re: select IN problem

On 23 Feb 2002, Andrew McMillan wrote:
>
> SELECT td.transaction_id FROM transactions_detail td
> WHERE EXISTS (SELECT transaction_id FROM transactions t
>    WHERE t.transaction_id = td.transaction_id
>      AND t.enter_date > CAST('2002-02-02' AS DATE );
>
> Could well work better.  The problem you are likely to be encountering
> is that IN (...) will not use an index.
>
> To see the query plans generated by the different SQL, use 'EXPLAIN <sql
> command>' - it is _well_ worth coming to grips with what EXPLAIN can
> tell you.
>
> You could also be better with a plan that did a simple JOIN and
> DISTINCT:
>
> SELECT DISTINCT td.transaction_id
> FROM transactions_detail td, transactions t
> WHERE t.enter_date > '2002-02-02'
>   AND td.transaction_id = t.transaction_id;
>
> Regards,
>                     Andrew.
>

Ok, the transactions table does have an index, so that must be the problem
there, but should it give an error or will it eventually return something?

Thanks, #2 worked as I should have tried something like that earlier.
Still a bit rusty with my sql queries ...

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Doug Silver
Network Manager
Quantified Systems, Inc
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~