Re: IN or EXISTS?? faster one

Manfred Koizar wrote:
> 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

that's also assuming d.company_name is distinct in depot. Otherwise
you'll get repeated bom.*s for each d it matches. A "distinct" would
solve this.

>     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.

Scott