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.
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?
regards, tom lane