> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >> On Wed, Jun 13, 2001 at 12:23:15PM +1000, Martijn van Oosterhout 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.
>
> However, the second form is easily able to make use of an index on b.v,
> whereas the first form is impossible to optimize unless you are able to
> rewrite it into some weird form of JOIN.
Assuming the index is there, yes, but again, do we use the index or run
the query. This has the same dynamics as index/heap scan and is
probably more complicated to figure out accurately.
> BTW, I just realized that the "weird form of JOIN" would have to be
> much stranger than I previously thought. The result of IN depends not
> only on whether the subselect's output has any matches to the current
> test value, but also on whether the subselect's output has any NULLs.
> So it's not simply a matter of doing a join with a special rule about
> producing no more than one output tuple per outer-query tuple. How
> would you check for the NULLs?
I thought NOT IN was the only one that was concerned about any NULL?
--
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
