Re: UNION versus SUB SELECT

UNION and subselect both performed better than EXISTS for this particular
case.

On Thu, Nov 21, 2013 at 12:31 PM, desmodemone <desmodemone(at)gmail(dot)com> wrote:

> Hi Robert, could you try with "exists" ?
>
> SELECT c.*
> FROM contacts c
> WHERE exists ( SELECT 1 FROM phone p WHERE p.addr =? and p.contact_id=
> c.id )
> OR exists (SELECT 1 FROM email e WHERE e.addr = ? and e.contact_id=c.id);
>
>
>
>
>
> 2013/11/21 Robert DiFalco <robert(dot)difalco(at)gmail(dot)com>
>
>> I have found this:
>>
>> SELECT c.*
>> FROM contacts c
>> WHERE c.id IN ( SELECT p.contact_id FROM phone p WHERE p.addr = ? )
>> OR c.id IN (SELECT e.contact_id FROM email e WHERE e.addr = ? );
>>
>> To have a worse plan than:
>>
>> SELECT * FROM contacts where id IN (
>> ( SELECT c.id FROM contacts c
>> JOIN phone p ON c.id = p.contact_id AND p.addr = ?
>> UNION
>> SELECT c.id FROM contacts c
>> JOIN email e ON c.id = e.contact_id AND e.addr = ? );
>>
>> Maybe this is no surprise. But after discovering this my question is
>> this, is there another option I dont' know about that is logically the same
>> that can perform even better than the UNION?
>>
>
>