Is too many Left Joins a code smell?

Question

If you have for example > 5 left joins in a query is that a code smell that there is ...

• something wrong with your design?
• you're doing too much in one query?
• you're database is too normalized?

Answer 1

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

Answer 2

In the sense that it's something you could/should investigate I'd say yes. It's likely you can get better utility and maintenance by factoring some views out of that.

In the sense that it's "bad code" no, this could quite easily be reasonable especially for larger DBs and modern databases will likely optimise any inefficiencies out.