How to break down nested JOIN in SQL Server 2012 in order to read it correctly

Question

I have a stored procedure that was not created by me.

In some stored procedures there are nested joins that I am having a hard time to understand.

How would be the easier way to write it in order to understand the logic? And also why those being used? To improve performance only?

SELECT *
FROM      
    dbo.tblQuotes AS tblQuotes_1 
LEFT OUTER JOIN
    dbo.lstLines ON tblQuotes_1.LineGUID = dbo.lstLines.LineGUID 
RIGHT OUTER JOIN
    dbo.tblUsers 
RIGHT OUTER JOIN
    dbo.tblQuotes 
RIGHT OUTER JOIN
    dbo.tblClaims_Claim ON dbo.tblQuotes.ControlNo = dbo.tblClaims_Claim.ControlNo 
RIGHT OUTER JOIN
    dbo.tblNoteEntries 
LEFT OUTER JOIN
    dbo.tblNoteRecipients ON dbo.tblNoteEntries.ID = dbo.tblNoteRecipients.EntryGUID 
INNER JOIN
    dbo.tblNoteDiaries ON dbo.tblNoteEntries.ID = dbo.tblNoteDiaries.EntryGUID 
INNER JOIN
    dbo.tblNoteEntities ON dbo.tblNoteEntries.NoteGUID = dbo.tblNoteEntities.NoteGUID 
INNER JOIN
    dbo.tblNoteStore ON dbo.tblNoteEntries.NoteGUID = dbo.tblNoteStore.ID 
                     AND dbo.tblNoteDiaries.NoteGUID = dbo.tblNoteStore.ID 
                     AND dbo.tblNoteEntities.NoteGUID = dbo.tblNoteStore.ID 
INNER JOIN
    dbo.lstNoteTypes ON dbo.tblNoteStore.Type = dbo.lstNoteTypes.NoteTypeID 
       ON dbo.tblClaims_Claim.ClaimGuid = dbo.tblNoteEntities.AssociatedEntityGUID 
       ON dbo.tblUsers.UserGUID = dbo.tblNoteRecipients.UserGUID 
       ON tblQuotes_1.ControlGuid = dbo.tblNoteEntities.ControlGuid 
LEFT OUTER JOIN
    dbo.tblMaxQuoteIDs ON tblQuotes_1.QuoteID = dbo.tblMaxQuoteIDs.MaxQuoteID 
LEFT OUTER JOIN
    dbo.tblInsureds ON dbo.tblNoteEntities.AssociatedEntityGUID = dbo.tblInsureds.InsuredGUID   

Answer 1

If readability is your goal then it's worth noting that you don't need nesting or RIGHT JOINs at all. The following

• is mathematically equivalent
• reads consistently left to right (assuming that's the way you like to read)
• Has no nesting
• Has no RIGHT JOINs

• Has fewer unnecessary characters and lines

  SELECT *
    FROM dbo.tblNoteStore              nt_str
             JOIN dbo.lstNoteTypes     nt_typ ON nt_str.[Type] = nt_typ.NoteTypeID
             JOIN dbo.tblNoteDiaries   nt_dia ON nt_str.ID = nt_dia.NoteGUID
             JOIN dbo.tblNoteEntities   entit ON nt_str.ID = entit.NoteGUID
             JOIN dbo.tblNoteEntries    entri ON nt_str.ID = entri.NoteGUID
                                                 and nt_dia.EntryGUID = entri.ID 
                                                 and entit.NoteGUID = entri.NoteGUID
        LEFT JOIN dbo.tblNoteRecipients recip ON entri.ID = recip.EntryGUID
        LEFT JOIN dbo.tblUsers            usr ON recip.UserGUID = usr.UserGUID
        LEFT JOIN dbo.tblInsureds       insur ON entit.AssociatedEntityGUID = insur.InsuredGUID
        LEFT JOIN dbo.tblClaims_Claim   claim ON entit.AssociatedEntityGUID = claim.ClaimGUID
        LEFT JOIN dbo.tblQuotes         qts_1 ON entit.ControlGUID = qts_1.ControlGUID
        LEFT JOIN dbo.lstLines          lines ON qts_1.LineGUID = lines.LineGUID
        LEFT JOIN dbo.tblMaxQuoteIDs   max_qt ON qts_1.QuoteID = max_qt.MaxQuoteId
        LEFT JOIN dbo.tblQuotes         qts_2 ON claim.ControlNo = qts_2.ControlNo 
        ;
  

Answer 2

I usually transform the list into an indented list, as if the various joins were nested IF statements. For example:

SELECT *
FROM dbo.tblQuotes AS tblQuotes_1 
LEFT OUTER JOIN dbo.lstLines 
    ON tblQuotes_1.LineGUID = dbo.lstLines.LineGUID 
RIGHT OUTER JOIN dbo.tblUsers 
    RIGHT OUTER JOIN dbo.tblQuotes 
        RIGHT OUTER JOIN dbo.tblClaims_Claim 
            ON dbo.tblQuotes.ControlNo = dbo.tblClaims_Claim.ControlNo 
        RIGHT OUTER JOIN dbo.tblNoteEntries 
            LEFT OUTER JOIN dbo.tblNoteRecipients 
                ON dbo.tblNoteEntries.ID = dbo.tblNoteRecipients.EntryGUID 
            INNER JOIN dbo.tblNoteDiaries 
                ON dbo.tblNoteEntries.ID = dbo.tblNoteDiaries.EntryGUID 
            INNER JOIN dbo.tblNoteEntities 
                ON dbo.tblNoteEntries.NoteGUID = dbo.tblNoteEntities.NoteGUID 
            INNER JOIN dbo.tblNoteStore 
                ON dbo.tblNoteEntries.NoteGUID = dbo.tblNoteStore.ID 
                AND dbo.tblNoteDiaries.NoteGUID = dbo.tblNoteStore.ID 
                AND dbo.tblNoteEntities.NoteGUID = dbo.tblNoteStore.ID 
            INNER JOIN dbo.lstNoteTypes 
                ON dbo.tblNoteStore.Type = dbo.lstNoteTypes.NoteTypeID 
            ON dbo.tblClaims_Claim.ClaimGuid = dbo.tblNoteEntities.AssociatedEntityGUID 
        ON  dbo.tblUsers.UserGUID = dbo.tblNoteRecipients.UserGUID 
    ON tblQuotes_1.ControlGuid = dbo.tblNoteEntities.ControlGuid 
LEFT OUTER JOIN dbo.tblMaxQuoteIDs 
    ON tblQuotes_1.QuoteID = dbo.tblMaxQuoteIDs.MaxQuoteID 
LEFT OUTER JOIN dbo.tblInsureds 
    ON dbo.tblNoteEntities.AssociatedEntityGUID = dbo.tblInsureds.InsuredGUID

I then move any of the out-of-place ON conditions into their correct location, if I can do it without destroying the logic of the query. In this case, I can't do it without some significant analysis.

NOTE: I honestly hate this type of join sequence, and if you know the tables and relationships, you can probably reorganize this to be a simple list of joins instead of the odd nested join structure. But that depends on two important things:

1. Was this done to optimize the performance of the query?

Even now, in the latest version of SQL Server I have worked with (2014), there are some queries that can be optimized by moving the table references around in your JOIN list. If this is why the list was reorganized into this nested structure, you could be seriously damaging performance by undoing the special organization. Of course, if this was written in an earlier version of SQL Server originally, you may want to untangle these joins to see if the query optimizer now handles this structure well.

2. Was this done because of some flaw in data normalization that actually requires the nested JOIN list to work at all?

Never underestimate the stupidity of your predecessors (For examples, go visit The Daily WTF and browse around for a few minutes.) If the data structures can be related in such a way as to require awkward and denormalized JOINs, they will be related in such a way. I haven't actually seen this reason validated, but I know in my gut that some data structure somewhere out there requires this.