Thread: IN, EXISTS or ANY?

Folks,

I was wondering if there is any difference in execution speed for the
following three statements:

WHERE case_id IN (SELECT case_id FROM case_clients         WHERE matter_no = '123.122342');
or:

WHERE case_id = ANY (SELECT case_id FROM case_clients         WHERE matter_no = '123.122342');
or

WHERE EXISTS ( SELECT case_id FROM case_clients         WHERE matter_no = '123.122342'         AND case_id =
cases.case_id);

... or does the parser handle all three exactly the same way?

-Josh Berkus

"Josh Berkus" <josh@agliodbs.com> writes:
> I was wondering if there is any difference in execution speed for the
> following three statements:

> WHERE case_id IN (SELECT case_id FROM case_clients
>              WHERE matter_no = '123.122342');
> or:

> WHERE case_id = ANY (SELECT case_id FROM case_clients
>              WHERE matter_no = '123.122342');
> or

> WHERE EXISTS ( SELECT case_id FROM case_clients
>              WHERE matter_no = '123.122342'
>              AND case_id = cases.case_id);

IN is the same as = ANY (cf. row_expr production in
src/backend/parser/gram.y for implementation, or SQL92 8.4 rule 4
for specification; there ain't *no* difference).

But EXISTS is an entirely different animal which is often faster
... isn't that in the FAQ?
        regards, tom lane

Tom,

> But EXISTS is an entirely different animal which is often faster
> ... isn't that in the FAQ?

OK, still true, then hey?  So for subqueries where I expect a
medium-to-large result set, I should use EXISTS.  Got it.

BTW, just tried the new CURSOR implementation for PL/pgSQL for the
first time.  Nice work, guys!

-Josh

Josh Berkus wrote:

>>But EXISTS is an entirely different animal which is often faster
>>... isn't that in the FAQ?


There is no reference to EXISTS in the SELECT documentation??

Is this explained somewhere else?



-- 

Keith Gray
Technical Services Manager
Heart Consulting Services

Keith,

> >>But EXISTS is an entirely different animal which is often faster
> >>... isn't that in the FAQ?
> 
> 
> There is no reference to EXISTS in the SELECT documentation??
> 
> Is this explained somewhere else?

Hmmm ....  found this using the Index for the online docs:

http://www.us.postgresql.org/users-lounge/docs/7.2/postgres/functions-subquery.html

Have fun.

-Josh