Re: UNION versus SUB SELECT

[desmodemone <desmodemone@xxxxxxxxx>]

Could you please attache the plan with explain buffers verbose?

thank you

2013/11/21 Robert DiFalco <robert.difalco@xxxxxxxxx>

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

On Thu, Nov 21, 2013 at 12:31 PM, desmodemone <desmodemone@xxxxxxxxx> 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.difalco@xxxxxxxxx>

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?