Re: IN or EXISTS?? faster one

On 19 Dec 2002 13:09:47 -0000, "Prachi Jain"
<prachijain3(at)rediffmail(dot)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