pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedIN, EXISTS or ANY?
5+ messages / 3 participants
[nested] [flat]
* IN, EXISTS or ANY?
@ 2002-04-29 04:44 Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 5+ messages in thread
From: Josh Berkus @ 2002-04-29 04:44 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: IN, EXISTS or ANY?
@ 2002-04-29 04:54 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2002-04-29 04:54 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: IN, EXISTS or ANY?
@ 2002-04-29 05:24 Josh Berkus <josh@agliodbs.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Josh Berkus @ 2002-04-29 05:24 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: IN, EXISTS or ANY?
@ 2002-10-09 00:51 Keith Gray <keith@heart.com.au>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 5+ messages in thread
From: Keith Gray @ 2002-10-09 00:51 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: IN, EXISTS or ANY?
@ 2002-10-09 03:53 Josh Berkus <josh@agliodbs.com>
parent: Keith Gray <keith@heart.com.au>
0 siblings, 0 replies; 5+ messages in thread
From: Josh Berkus @ 2002-10-09 03:53 UTC (permalink / raw)
To: Keith Gray <keith@heart.com.au>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2002-10-09 03:53 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-04-29 04:44 IN, EXISTS or ANY? Josh Berkus <josh@agliodbs.com>
2002-04-29 04:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-04-29 05:24 ` Josh Berkus <josh@agliodbs.com>
2002-10-09 00:51 ` Keith Gray <keith@heart.com.au>
2002-10-09 03:53 ` Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox