Practical application of EXISTS vs. LEFT JOIN to find addresses for delivery

Our end users have many ways of shipping product to our customers. They can use the customer address in one table ([customer]), a specific delivery address in another ([dropship]), or yet another delivery address in another table (for customers with multiple locations) ([delivery]). These all have roughly the same structure, with the same column names for the same data points (e.g. [address] is the address in all tables).

Currently, our logic for finding the delivery address of an order is as follows:

SELECT
    Address = COALESCE(r.address, d.address, c.address) 
FROM [order] o 
JOIN customer c
    ON o.customerid = c.customerid
LEFT JOIN delivery d
    ON o.customerid = d.customerid
    AND o.delivaddressid = d.delivaddress
LEFT JOIN dropship r
    ON o.orderid = r.orderid;

Based on conversations and not a few blog posts, EXISTS seems to be preferred.

However, this is a new paradigm to me and I'm trying to wrap my brain around it. Do I just replace the LEFT JOINs with

WHERE EXISTS 
    (select 1 from delivery d
        where o.customerid = d.customerid 
        and o.delivaddressid = d.delivaddress)

This won't allow me to use the data from [delivery] unless I toss it in the FROM clause and create a cartesian product, right?

While you can use EXISTS to determine whether, say, a dropship address is present, you can't use it to tell what the actual address is. EXISTS doesn't return any data. For that, you'll need the join.

A better design might be well-served here, for example a single Addresses table with a type. But as I understand it your model is currently not flexible.

StackExchange Database Administrators Q#30923, answer score: 5