[ACCEPTED]-Is too many Left Joins a code smell?-left-join

It's a perfectly legitimate solution for 11 some designs.

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

In this case 7 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 6 be inefficient in some cases, and may be 5 replaced with EXISTS or NOT EXISTS (if you only want to 4 figure out that the corresponding records 3 exist or do not exist in other tables).

See 2 this article in my blog for performance 1 details:

• Finding incomplete orders - how to benefit from replacing LEFT JOIN's with NOT EXISTS

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

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

Nope it's perfectly fine to do, though if 7 you find yourself writing the same queries/procedures 6 over and over again using the same joins 5 to the same tables, it maybe a candidate 4 for creating a View just to simplify you're 3 queries in future, and to reduce the number 2 of touch points you'd need to change if 1 you're schema changes

A lot of times you can alleviate the visual 6 smell by creating helper views, I do not 5 think there is a hard and fast rule of how 4 many left joins are considered bad.

Unlike 3 procedural coding, breaking down SQL into 2 little bits and pieces can result in inefficient 1 queries.

Your Results My Vary

Anything out of the ordinary could be a 7 code-smell for anything. Like Quassnoi said 6 it could be perfectly legitimate. It's not 5 uncommon for really in-depth reports to 4 require a crazy amount of joins to piece 3 together the information correctly. That 2 doesn't mean that the developer should looking 1 at denormalizing their database.

It is pretty much impossible for someone 6 to answer a question as general as this 5 and to attempt to create such an arbitrary 4 rule would pointless.

Left joins are a perfectly 3 acceptable type of join which map onto a 2 very common need: get me all x's, if they 1 have associated y's then get those too.

No, not at all. It's perfectly legitimate 18 to construct a database design that uses 17 a significant number of left joins on some 16 queries.

Having said that I would generally 15 prefer to construct the database so that 14 the number of cases where outer joins are 13 required is limited as experience tends 12 to suggest that (complex) queries that use 11 them are more error prone and likely to 10 give rise to maintenance problems.

As an 9 interesting historical aside, the early 8 versions of IBM's DB2, when it ran on mainframes 7 only, did not support outer joins (Oracle 6 and Ingress both did at the time which was 5 a major selling point). This lead to some 4 interesting issues in database design as 3 it was necessary to ensure that all expected 2 data access requirements for the database 1 could be solved using just inner joins.

I would contend that having to use many 39 joins (e.g. deal with normalized data) is 38 not a code smell, but rather an indication 37 you might not be working in the right place. In 36 my experience, those that are concerned 35 about the number of joins in queries are 34 developing too much in the database and 33 not enough in the applications and reports 32 that expose the data. The data structures 31 must be flexible enough to support a myriad 30 of uses and this is why normalization, to 29 one degree or another, is important.

In building 28 today's enterprise applications, developers 27 can leverage yesterday's accomplishments 26 to work at abstract levels high above technologies 25 like SQL, and even XML, in order to deliver 24 more value with less work. There are tools, i.e. Report 23 Writers, Code Generators, ORMs, entity frameworks, etc., that 22 abstract away the low level work of constructing 21 SQL manually, and will perform the joins 20 for you. Most are aware of the SQL dialect 19 being used (for example, Oracle 9 vs MySQL 18 3) and can generate the SQL syntax that 17 is most efficient for that dialect; meaning 16 they can probably create the joins better 15 than you can.

However, these tools work 14 very poorly, or not at all, in a relational 13 environment without sufficient normalization. For 12 me, this is where a "development" smell 11 manifests itself; if an established data 10 access tool can't understand the relations 9 under which I've structured my data, I probably 8 need to look for a more normalized way to 7 create those relationships and therein reap 6 benefits far exceeding just the use of the 5 tool. Typically, somewhere between 2nd 4 and 3rd normal form is the sweet-spot; although invariably 3 there tend to be small areas of relational 2 data where a higher degree of normalization 1 makes sense and adds value.

Cheers, Travis