Re: IN vs EXISTS equivalence

On Aug 11, 2008, at 3:40 PM, Gregory Stark wrote:
> "Decibel!" <decibel@decibel.org> writes:
>> On Aug 8, 2008, at 3:23 PM, Tom Lane wrote:
>>> * has no set operations (UNION etc), grouping, set-returning  
>>> functions
>>> in the SELECT list, LIMIT, or a few other funny cases
>>
>>
>> Couldn't union/union all be treated as
>>
>> EXISTS(a)
>> OR EXISTS(b)
>
> Kind of confused by what you mean here. Can you give an example?
>
> The usual transformation to consider with UNION is to transform
>
> SELECT ... WHERE x OR y
>
> into
>
> SELECT ...
>  WHERE x
> UNION ALL
> SELECT ...
>  WHERE y AND NOT x

It was a case of the union being inside the EXISTS subquery, ie:

WHERE EXISTS (SELECT * FROM a UNION SELECT * FROM b)

AFAIK that's identical to

WHERE EXISTS (SELECT * FROM a) OR EXISTS (SELECT * FROM b)

But as Tom mentioned, as soon as you have it you can't answer it with  
a simple join, so it doesn't matter...
-- 
Decibel!, aka Jim C. Nasby, Database Architect  decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828