Is too many Left Joins a code smell?

It’s a perfectly legitimate solution for some designs.

Say you have a hierarchy of one-to-many relations like Customer – Order – Basket – Item – Price, etc., which can be unfilled on any level: a Customer may have no Orders, an Order can have no Baskets, etc.

In this case you issue something like:

SELECT  *
FROM    Customer c
LEFT OUTER JOIN
        Order o
ON      o.CustomerID = c.ID
LEFT OUTER JOIN
        Basket b
ON      b.OrderID = c.ID
…

Note that it may be inefficient in some cases, and may be replaced with EXISTS or NOT EXISTS (if you only want to figure out that the corresponding records exist or do not exist in other tables).

See this article in my blog for performance details:

• Finding incomplete orders – how to benefit from replacing LEFT JOIN‘s with NOT EXISTS