CODEWITHSUNDEEP
sqlsql-servert-sql
SkyeBoniwell
SkyeBoniwell

Reputation: 7092

SQL JOIN structure

I'm working on a SQL query that someone wrote years ago and I'm trying to understand why it takes about 30 seconds to run.

The query has a type of join I've never seen before.

I'm hoping someone can point me to a resource or let me know how this join works.

It's not the join syntax that is confusing me, it's how the join is structured with the INNER JOIN right after the LEFT OUTER JOIN.

Here is the line in question:

LEFT OUTER JOIN dbo.JudicialCasePreceptor AS p 
INNER JOIN dbo.Preceptors AS pr 
ON p.UserInfoUID = pr.UID ON e.JudicialCaseUID = p.JudicialCaseUID

Here is the whole query:

SELECT DISTINCT 
     e.JudicialCaseUID, e.DistrictUID, e.DistrictName, e.ShortName, 
     e.UserInfoUID, e.UserEmail, e.CustomDiagnosis, e.UserName, 
     e.DateSubmitted, e.DateUpdated, e.CaseItemUID, e.CaseType, 
     e.SettingName, e.Age, e.PL, e.gender, e.Age_gender, 
     e.CaseTypeID, e.Notes, e.EncounterType, e.FTCCourseID, e.PL AS GroupName, 
     a.Name AS AliasName, p.UserInfoUID AS PreceptorUID, 
     pr.FirstName + ' ' + pr.LastName AS PreceptorName, e.PatientType
FROM dbo.view_CaseInfo AS e 
    INNER JOIN dbo.CaseItem AS a 
        ON a.UID = e.CaseItemUID
    LEFT OUTER JOIN dbo.JudicialCasePreceptor AS p 
                         INNER JOIN dbo.Preceptors AS pr 
        ON p.UserInfoUID = pr.UID ON e.JudicialCaseUID = p.JudicialCaseUID

Thanks!

Upvotes: 0

Views: 1187

Answers (2)

Charles Bretana
Charles Bretana

Reputation: 146499

It would be clearer if the query was indented appropriately and included the parentheses:

 SELECT DISTINCT 
     e.JudicialCaseUID, e.DistrictUID, e.DistrictName, e.ShortName, 
     e.UserInfoUID, e.UserEmail, e.CustomDiagnosis, e.UserName, 
     e.DateSubmitted, e.DateUpdated, e.CaseItemUID, e.CaseType, 
     e.SettingName, e.Age, e.PL, e.gender, e.Age_gender, 
     e.CaseTypeID, e.Notes, e.EncounterType, e.FTCCourseID, e.PL AS GroupName, 
     a.Name AS AliasName, p.UserInfoUID AS PreceptorUID, 
     pr.FirstName + ' ' + pr.LastName AS PreceptorName, e.PatientType
FROM dbo.view_CaseInfo AS e 
    INNER JOIN dbo.CaseItem AS a 
        ON a.UID = e.CaseItemUID
    LEFT OUTER JOIN (dbo.JudicialCasePreceptor AS p 
                      INNER JOIN dbo.Preceptors AS pr 
                            ON p.UserInfoUID = pr.UID)
      ON e.JudicialCaseUID = p.JudicialCaseUID

Imagine that the partial resultset inside the parentheses 

  (dbo.JudicialCasePreceptor AS p 
      INNER JOIN dbo.Preceptors AS pr 
           ON p.UserInfoUID = pr.UID)

was created first, and then this intermediate result set is outer joined to the results of the first inner join ...

on another note, be aware that inner, outer, and as are optional, and once you are familiar with this, the sql is shorter and reads easier without them:

 SELECT DISTINCT 
     e.JudicialCaseUID, e.DistrictUID, e.DistrictName, e.ShortName, 
     e.UserInfoUID, e.UserEmail, e.CustomDiagnosis, e.UserName, 
     e.DateSubmitted, e.DateUpdated, e.CaseItemUID, e.CaseType, 
     e.SettingName, e.Age, e.PL, e.gender, e.Age_gender, 
     e.CaseTypeID, e.Notes, e.EncounterType, e.FTCCourseID, e.PL GroupName, 
     a.Name AliasName, p.UserInfoUID PreceptorUID, 
     pr.FirstName + ' ' + pr.LastName PreceptorName, e.PatientType
FROM dbo.view_CaseInfo e 
    JOIN dbo.CaseItem a 
        ON a.UID = e.CaseItemUID
    LEFT JOIN (dbo.JudicialCasePreceptor AS p 
                  JOIN dbo.Preceptors AS pr 
                      ON p.UserInfoUID = pr.UID)
      ON e.JudicialCaseUID = p.JudicialCaseUID

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Your intuition is correct. The left outer join is being turned into an inner join by this sequence. The conditions in the inner join include p. So, if there are no matching records in p, then p.UserInfoUid will be NULL and the inner join will fail.

Join's are interpreted in reading order (the order they are written in). This for interpretation. The actual execution can be in any order.

Upvotes: 1

Related Questions