Re: IN or EXISTS?? faster one

On 19 Dec 2002 13:09:47 -0000, "Prachi Jain"
<prachijain3@rediffmail.com> wrote:
>I am using too many subqueries in my queries. I have read some
>FAQs that using EXISTS is faster than IN. Is that correct?? I
>tried to get the total runtime using EXPLAIN ANALYZE, but i got
>total runtime for the query with IN but not for the query with
>EXISTS.

What do you mean by "EXPLAIN ANALYZE ... not for the query with
EXISTS"?  Was there an error?

>EXPLAIN ANALYZE Select * from bom where depot_id in ( SELECT
>depot_id from depot where company_name ='SOME' );
>
>EXPLAIN ANALYZE  Select * from bom WHERE EXISTS ( SELECT depot_id
> from depot where company_name ='SOME' and depot.depot_id =
>bom.depot_id );

Assuming depot_id is unique in depot, you could also write

    SELECT bom.*
      FROM bom INNER JOIN depot d ON bom.depot_id=d.depot_id
     WHERE d.company_name ='SOME';

or

    SELECT bom.*
      FROM bom, depot d
     WHERE bom.depot_id = d.depot_id
       AND d.company_name = 'SOME';

and let the query optimizer find a good plan.

Servus
 Manfred