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 cLEFT OUTER JOIN Order oON o.CustomerID = c.IDLEFT OUTER JOIN Basket bON 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 withNOT EXISTS
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.
Nope it's perfectly fine to do, though if you find yourself writing the same queries/procedures over and over again using the same joins to the same tables,it maybe a candidate for creating a View just to simplify you're queries in future, and to reduce the number of touch points you'd need to change if you're schema changes