> On Wed, Jun 13, 2001 at 10:03:24AM -0400, Bruce Momjian wrote:
> > > > select x from a where v in (select v from b)
> > > > select x from a where exists (select 1 from b where a.v = b.v)
> > >
> > > The latter should be faster than the former on every relational database
> > > system.
> >
> > That surprises me because the subquery is a correlated subquery which
> > are usually slower on other databases that normal subqueries.
>
> To be honest I didn't notice that. :-)
>
> I was just talking about the difference with IN (where you have to compute
> the complete result set) and EXISTS where you just look for one match.
When you use IN and a subquery you _sometimes_ have to execute the
subquery for every row of the outer query. Ouch! It can be optimized
to run the subquery non-correlated and join the correlated values to the
outer query for specific rows. The trick is knowing when the subquery
is going to be run many times and when the subquery is going to be run
for only a few rows so the optimization is not used.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026