WHY Follow a LEFT OUTER JOIN with another LEFT OUTER JOIN

Question

Post reply

WHY Follow a LEFT OUTER JOIN with another LEFT OUTER JOIN

YSLGuru

SSC-Insane

Points: 21661

December 6, 2023 at 10:12 pm

#4341130

NOTE: The Topic Title probably isn't very good but I wasn't sure how to title this with a limited amount of text.

I thought I had previously asked about this but according to my history I haven't. I have run across some code that I've never dealt with before and I'm curious if you guys can provide some info/feedback on what this is and why one would do it.

In the below code the LOJ (Left Outer Join) for CAMCHARG is followed not by another table but another LOJ on CAMRULE. This same LOJ followed by another LOJ happens again with DETAIL and TRANS. Why is this used and is there a better way to achieve the same goal

                   LEFT OUTER JOIN CAMCHARG CC  
                   LEFT OUTER JOIN CAMRULE CR    ON (CC.hCamrule = CR.hMy  AND CC.hTenant = CR.hTenant  AND CC.hChargecode = CR.hChargecode) 
                                                 ON (CC.hPostRef = CHG.hMy AND CC.hTenant = CHG.hPerson AND CC.hChargecode = CHG.hRetentionAcct)
                   
                   LEFT OUTER JOIN DETAIL D 
                   LEFT OUTER JOIN TRANS RCT     ON (D.hInvOrRec = RCT.hMy AND RCT.iType IN(6,15) 
                                                                           AND RCT.void = 0 AND RCT.nsf = 0) 
                                                 ON (D.hChkOrChg = CHG.hMy)

This topic was modified 11 months, 3 weeks ago by YSLGuru.

Kindest Regards,

Just say No to Facebook!

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

ratbak Ten Centuries Points: 1358 More actions · Answer 1

Does the code actually work?

That looks confusingly written, if not wrong, because the on statements for the joins do not always directly follow the table they are joining.

The second ON should immediately follow LEFT OUTER JOIN CAMCHARG CC

The fourth ON should immediately follow LEFT OUTER JOIN DETAIL D

-- i.e.,

LEFT OUTER JOIN CAMCHARG CC ON (CC.hPostRef = CHG.hMy AND CC.hTenant = CHG.hPerson AND CC.hChargecode = CHG.hRetentionAcct)
LEFT OUTER JOIN CAMRULE CR ON (CC.hCamrule = CR.hMy  AND CC.hTenant = CR.hTenant  AND CC.hChargecode = CR.hChargecode) 

LEFT OUTER JOIN DETAIL D ON (D.hChkOrChg = CHG.hMy)
LEFT OUTER JOIN TRANS RCT ON (D.hInvOrRec = RCT.hMy AND RCT.iType IN(6,15) AND RCT.void = 0 AND RCT.nsf = 0)

ratbak Ten Centuries Points: 1358 More actions · Answer 2

Removed duplicate post following gateway error message.

YSLGuru SSC-Insane Points: 21661 More actions · Answer 3

This suprised me to and it does work. It's not something I wrote. It's from the vendor who proivides our primary accounting software. My boss asked me to take a lok at it and verify it works the way we were told it works and as I was going thru it I came across this and was like what is this. This is just a specifc example of amore general Q or why/when would someone do a JOIN immediately after another like this. There like nested joins or something.

Thanks

Kindest Regards,

Just say No to Facebook!

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

That kind of code is frequently generated by the "query designer" in SSMS under {Query}{Design Query in Editor} menu selection in SSMS. And, as you found out, it DOES work.

I've never tested it to see if there's a performance or resource usage advantage or disadvantage (I should up and do that someday) because I stopped using the query designer a very long time ago. I also think that it's a bit hard to read and edit without using the query designer to rework it.

IIRC, it occurs on what I call a "skip join". That's what I call it when two tables are joined with more than one column in the join where one of the column goes directly between the two tables and the other goes through a 3rd table. The first leg "skipped" the 3rd table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 5

and some times it even throws in a right outer join ( in the mix of LOJ s )

I have noticed some "nested joins" some times can have a positive performance impact.

You'll find out if you rewrite the query.

Also good to keep in mind, is that it prefers using Left outer joins instead of inner joins, and that can have a huge performance impact too.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Ed B Hall of Fame Points: 3666 More actions · Answer 6

Is this different from nested joins that allow an inner join to the right of a left outer? I recently came across this technique and was shocked that I had never seen it before.

SELECT a.EmployeeID, a.EmployeeCode, b.LocationCode, c.LocationTypeCode
FROM dbo.Employees AS a
LEFT JOIN dbo.Locations AS b
    INNER JOIN dbo.LocationTypes AS c ON b.LocationTypeID = c.LocationTypeID
        ON a.LocationID = b.LocationID

YSLGuru SSC-Insane Points: 21661 More actions · Answer 7

Jeff

Do you know if others would also use the term "Skip Join" so if I googled on SKIP JOIN I'd get info about this or does this not have a formally recognized name/term?

Kindest Regards,

Just say No to Facebook!

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

YSLGuru wrote:

Jeff
Do you know if others would also use the term "Skip Join" so if I googled on SKIP JOIN I'd get info about this or does this not have a formally recognized name/term?

Like I said in my post...

"it occurs on WHAT I CALL a "skip join""

It's a "Moden-ism", for sure. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 9

I would indent the query like this:

FROM CHG
LEFT JOIN CAMCHARG CC  
     LEFT JOIN CAMRULE CR    
            ON CR.hMy = CC.hCamrule
           AND CR.hTenant = CC.hTenant  
           AND CR.hChargecode = CC.hChargecode 
       ON CC.hPostRef = CHG.hMy 
      AND CC.hTenant = CHG.hPerson 
      AND CC.hChargecode = CHG.hRetentionAcct                   
LEFT JOIN DETAIL D 
     LEFT JOIN TRANS RCT     
            ON RCT.hMy = D.hInvOrRec
           AND RCT.iType IN(6,15) 
           AND RCT.void = 0 
           AND RCT.nsf = 0 
       ON D.hChkOrChg = CHG.hMy

It is then a bit more obvious what it's doing. It is actually no different from the join below though it might affect performance depending on what the optimiser does with it.

FROM CHG
LEFT JOIN CAMCHARG CC  
       ON CC.hPostRef = CHG.hMy 
      AND CC.hTenant = CHG.hPerson 
      AND CC.hChargecode = CHG.hRetentionAcct                   
LEFT JOIN CAMRULE CR    
       ON CR.hMy = CC.hCamrule
      AND CR.hTenant = CC.hTenant  
      AND CR.hChargecode = CC.hChargecode 
LEFT JOIN DETAIL D     
       ON D.hChkOrChg = CHG.hMy
LEFT JOIN TRANS RCT     
       ON RCT.hMy = D.hInvOrRec
      AND RCT.iType IN(6,15) 
      AND RCT.void = 0 
      AND RCT.nsf = 0

YSLGuru SSC-Insane Points: 21661 More actions · Answer 10

So is this just another way of doing something like the below? Where the below requires and alias (CC)? And by dropping the alias and the parenthesis you can just do an LOJ to an LOJ like in the code I originally posted? It's less of a question of performance for me versus readability. For me grouping within parenthesis like the below is easier to follow.

FROM CHG LEFT JOIN ( CAMCHARG CC LEFT JOIN CAMRULE CR ON CR.hMy = CC.hCamrule AND CR.hTenant = CC.hTenant 
                                                                              AND CR.hChargecode = CC.hChargecode 
                   ) CC ON CC.hPostRef = CHG.hMy AND CC.hTenant = CHG.hPerson  
                                                 AND CC.hChargecode = CHG.hRetentionAcct

Kindest Regards,

Just say No to Facebook!