LOJ followed By LOJ - What is this and is it best practices

Question

LOJ followed By LOJ - What is this and is it best practices

SSC-Insane

Points: 21669

February 14, 2023 at 6:33 pm

I came across something today I'd never seen before and I've been working with SQL for over a decade. The Query has 1 LOJ for TABLECC immediately followed by another LOJ for TABLER then within parentheses the join conditions.

What exactly is this kind of approach?
Is it best practices or is it something rarely used to accommodate some kind of specials result which is why I've never seen it done before?
Is there some name/term to refer to this because I couldn't find anything on Google using LEFT OUTER JOIN followed by LEFT OUTER JOIN that was relative to this.

LEFT OUTER JOIN TABLEC CC LEFT OUTER JOIN TABLER CR  ON (CC.hFKey1 = CR.hPKey AND CC.hFKey2 = CR.hFKey2  
                                                                              AND CC.hFKey3 = CR.hKey3 
                                                           )  
                                                        ON (CC.hFKey4 = CHG.hPKey AND CC.hFKey2 = CHG.hFKey1  
                                                                                  AND CC.hFKey3 = CHG.hKey2

Kindest Regards,

Just say No to Facebook!

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

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

ScottPletcher SSC Guru Points: 101200 More actions · Answer 1

You can defer the ON clause for a JOIN until after other JOINs. Normally you wouldn't do that unless it's absolutely necessary.

LEFT OUTER JOIN TABLEC CC ON (CC.hFKey4 = CHG.hPKey AND CC.hFKey2 = CHG.hFKey1

AND CC.hFKey3 = CHG.hKey2)

LEFT OUTER JOIN TABLER CR ON (CC.hFKey1 = CR.hPKey AND CC.hFKey2 = CR.hFKey2

AND CC.hFKey3 = CR.hKey3)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

YSLGuru SSC-Insane Points: 21669 More actions · Answer 2

ScottPletcher wrote:

You can defer the ON clause for a JOIN until after other JOINs. Normally you wouldn't do that unless it's absolutely necessary.
LEFT OUTER JOIN TABLEC CC ON (CC.hFKey4 = CHG.hPKey AND CC.hFKey2 = CHG.hFKey1 AND CC.hFKey3 = CHG.hKey2)
LEFT OUTER JOIN TABLER CR ON (CC.hFKey1 = CR.hPKey AND CC.hFKey2 = CR.hFKey2 AND CC.hFKey3 = CR.hKey3)

OMG was that a quick response like as if you were just waiting for me to hit send.
So is it safe to say based on your answer that this is something rarely used?
Do you know if there is any phrase or way of wording the search on Google to get info about this kind join?

Kindest Regards,

Just say No to Facebook!

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 3

The only time I've seen one like that is when someone use the "Query Builder" functionality and only in what I refer to as a "Skip Join" where Table A is joined to Table C and Table A is joined to Table B which is then joined to Table C. Even the, it seems a bit unpredictable.

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

YSLGuru SSC-Insane Points: 21669 More actions · Answer 4

Jeff - is this something you'd personally recommend trying to avoid in queries? Clearly it's a valid query because it does run but I can't help but wonder if like with the cursor its something that should be frowned down upon and only used when there is no non-cursor way to do teh same thing. I have to deal with a lot of SQL code using cursors because the software vendor doesn't really respect SQL and get's it's developers to read "SQL in 24 hours or less" and considers them properly trained in SQL and so it's common to see them using the CURSOR because that's what they are comfortable with, that row by [agonizing] row to coding.

Kindest Regards,

Just say No to Facebook!

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 5

No. I don't avoid it. I never write things that way but I don't change them if I come across them. As you say, it doesn't cause any issues except a serious "Spock eyebrow". 😀

--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: 135551 More actions · Answer 6

I haven't encountered a developer yet, who wrote such a query himself, without using the "query designer".

I've seen some cases where it actually provided a performance advantage, however, I plead against it, because it is an unnatural twist, which is often semantically difficult for us humans to understand.

Then again, once I started interpreting such a query with my developer(s) and realy walk it over symantically, in many cases they found a leap which had to be fixed and provided us better performance and correct results. Proving once more, point-and-click-development can also have its disadvantages.

As in many cases with performance, it may work for you, but it may also shoot you in the foot.

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

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

Thanks Johan. For me it just looks wrong/bad. I mean if in some situations it provides better performance and enough so to justify it then yeah but it just looks confusing to me.

Kindest Regards,

Just say No to Facebook!

YSLGuru SSC-Insane Points: 21669 More actions · Answer 8

I could see that, the Spock brow

Kindest Regards,

Just say No to Facebook!

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

I never really nest joins like that but if you do you can only access fields from the join on the next level above.

Create some tables:

DROP TABLE IF EXISTS #table1;
DROP TABLE IF EXISTS #table2;
DROP TABLE IF EXISTS #table3;

CREATE TABLE #table1(id int, description varchar(20));
CREATE TABLE #table2(id int, t1id int, description varchar(20));
CREATE TABLE #table3(id int, t1id int, t2id int, description varchar(20));

This query will compile successfully:

SELECT *
  FROM #table1 t1
  LEFT JOIN #table2 t2
       LEFT JOIN #table3 t3
              ON t3.t2Id = t2.Id
         ON t2.t1Id = t1.Id
;

This query is trying to access #table1 from #table3

SELECT *
  FROM #table1 t1
  LEFT JOIN #table2 t2
       LEFT JOIN #table3 t3
              ON t3.t1Id = t1.Id
         ON t2.t1Id = t1.Id
;

If fails with error:

Msg 4104, Level 16, State 1, Line 5

The multi-part identifier "t1.Id" could not be bound.