Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
3  
If you actually need to return the address from one of those tables, you can't use EXISTS. Is there any room here for improved design (e.g. a single Addresses table with type)? You'll still need to include that table but it will be a much more digestible query. –  Aaron Bertrand Dec 21 '12 at 18:36
1  
If you want only one column (the address) from a left joined table you could use an inline subquery (2 for getting the columns from the 2 tables) and the FROM clause would be a bit cleaner (only customer and order). But it would probably yield different execution plan and I doubt it would be more efficient. –  ypercube Dec 22 '12 at 10:25
add comment

1 Answer

up vote 5 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.