Performance optimization: Alternative to LEFT JOIN the same table twice I would like to extend the following query with the commented out LEFT JOIN. However, those lines make the query noticeably slower.
The query is used for an API to lookup user IDs by phone number and things need to be fast. So, is there a more performant alternative to joining the same table twice with different ON clauses?

set statistics time on 

SELECT
    ivr.ID
FROM ivr
LEFT OUTER JOIN ADDR_MASTER am1
    ON am1.ID = ivr.ID
        AND am1.ADDR_TYPE = 'WORK' --WORK PHONE (only adds 35ms to lookup time)
--LEFT OUTER JOIN ADDR_MASTER am2
--  ON am2.ID = ivr.ID
--      AND am2.ADDR_TYPE = 'HOME' --HOME PHONE (fallback if not in IVR, adds 100+ ms :(
WHERE (PHONE1 = '0123456789'
OR PHONE2 = '0123456789'
OR PHONE3 = '0123456789'
OR am1.PHONE = '0123456789'
--OR am2.PHONE = '0123456789'
)

set statistics time off


Here are the plans: 


Old
New
I think this will do what you are asking.

SELECT
    ivr.ID
FROM ivr
LEFT OUTER JOIN ADDR_MASTER am1
    ON am1.ID = ivr.ID
        AND am1.ADDR_TYPE IN ('WORK','HOME')
WHERE (PHONE1 = '0123456789'
OR PHONE2 = '0123456789'
OR PHONE3 = '0123456789'
OR am1.PHONE = '0123456789'
)


That said, there are a few things you could do differently.

This might be better, not sure, but as long as it's not slower it's probably easier to maintain.

SELECT
    ivr.ID
FROM ivr
LEFT OUTER JOIN ADDR_MASTER am1
    ON am1.ID = ivr.ID
        AND am1.ADDR_TYPE IN ('WORK','HOME')
WHERE '0123456789' IN (PHONE1, PHONE2, PHONE3, am1.PHONE)


Now, assuming that you do in fact only need the ivr.ID (at least not anything from ADDR_MASTER) you could do this:

SELECT
    ivr.ID
FROM ivr
WHERE '0123456789' IN (PHONE1, PHONE2, PHONE3)
    OR EXISTS ( SELECT 1 FROM ADDR_MASTER am1
                WHERE am1.ID = ivr.ID
                    AND am1.ADDR_TYPE IN ('WORK','HOME')
                    AND am1.PHONE = '0123456789' )


I'd try various combinations to see which if any of these are actually faster.