WHY Follow a LEFT OUTER JOIN with another LEFT OUTER JOIN

  • 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 7 months, 1 week ago by  YSLGuru.

    Kindest Regards,

    Just say No to Facebook!
  • 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)

     

  • Removed duplicate post following gateway error message.

  • 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!
  • 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)

  • 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

  • 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

     

  • 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!
  • 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)

  • 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
  • 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!

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

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