6

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?

ypercubeᵀᴹ
  • 97,895
  • 13
  • 214
  • 305
swasheck
  • 10,665
  • 4
  • 47
  • 88
  • 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

1 Answers1

5

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.

Aaron Bertrand
  • 180,303
  • 28
  • 400
  • 614