Re: UNION versus SUB SELECT

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?
>