Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Complex joins


Complex joins

Author
Message
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21075 Visits: 18259
Thanks Hugo. Nested joins is a topic I would like to see more.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20931 Visits: 9671
SQL Kiwi (12/6/2011)
[quote]Hugo Kornelis (12/6/2011)
Something about the ability to select blocks of code to run easily I think is one main argument in favour.



+1000

I work mainly with 1000+ lines procs for reporting. CTEs just don't do what I need. I often need to nest 2-3 levels and I need to "inner" select as many as I need as to not go insane.
Ken Wymore
Ken Wymore
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4422 Visits: 2342
Interesting question. Thanks Hugo!
Revenant
Revenant
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5791 Visits: 4718
Wow - one of the best questions is a long time... Thank you, Hugo!
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10714 Visits: 12017
Hugo Kornelis (12/6/2011)
[quote]I stiill stand by what I intended to write - that the only way to rewrite the query to a form that doesn't use nested joins involves reordering the table order and changing the left outer join to the far less cocmmon and harder to grasp right outer join form.

Just one question on that: if I write it using a CTE which contains a join, does that count as a nested join? If not, you can't stand by it. If it does (as it probably should) you can. (edit: of course reordering and changing the left join to a right join doesn't eliminate the nested join anyway, since the nesting is implicit in the left to right semantics of SQL - we would all be very disappointed if the system managed to avoid nesting the joins in a case like this! The whole point of the left to right semantics is to specify the nesting structure in cases where there are no other syntactic indications of what the nesting should be).

I agree that right joins are far less common than left joins, but I don't find them the least bit harder to grasp; they are conceptually exactly the same (and in any decent algebraic or functional notation for relational calculus they don't present any problem at all). I've never understood why I see more left joins than right joins when loooking at other people's code - and I very rarely write either (I write full joins comparatively often - they are usful for generating big sets when size not content counts).

Tom

TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10714 Visits: 12017
A good question - the sort of thing I would like to see more of because it's about understanding the system and its language rather than about remembering the reference book. But unfotunately it's even more about ability to read and understand code which is obfuscated by really terrible formatting than about understanding nested joins. I was glad to see in your reply to an earlier comment that the appalling format was deliberate, not something that you thought was good format (and hope that applies to the query that was the correct answer too, despite its having only nesting implied by L to R semantics; I would still insist on using indentation to indicate the structure including nesting of the pair of joins in that query, since I don't see it as structurally an different from the original query).

It's another one where I'm surprised by the failure rate - 78% wrong answers is amazing. Does that mean that 78% of people here never write or read complex queries?

My first approach to this was to format all five queries sensibly, but as I don't possess a decent automatic formatter I decided to do some elimination first so that I'd only have to format some - and what with a query that would have no nulls introduced by the outer join, another that would eliminate all rows that missed on what was supposed to be a potentially null-introducing outer join condition, and another that could allow one null (or none, or two) to be introduced by the outer join while the original query could only introduce none or two I quickly discovered that I didn't have to do any formatting to get the answer becaue I'd eliminated all but one of the options. I didn't check the other option thoroughly, just checked that moving one particular condition to the inner join to the outer join would have no effect, and that was it. So not too much effort - but I still think any code formatted like that should earn a written warning to the writer, and a repeat performance a dismissal, even though I could quite quickly get the answer without sorting out the format in this case.

Tom

Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8323 Visits: 11579
L' Eomot Inversé (12/6/2011)
Just one question on that: if I write it using a CTE which contains a join, does that count as a nested join?


I don't think there's a formal definition of nested joins. I call a join nested when the query goes JOIN JOIN ON ON instead of the more common JOIN ON JOIN ON.


I agree that right joins are far less common than left joins, but I don't find them the least bit harder to grasp; they are conceptually exactly the same (and in any decent algebraic or functional notation for relational calculus they don't present any problem at all). I've never understood why I see more left joins than right joins when loooking at other people's code - and I very rarely write either


I think it's about how most people think. You read left-to-right. The left join takes the "first" (left) source, then joins the "second" (right) source, but doesn't throw out unmatched rows from the first. For a right join, you either use the same mental paradigm, but then you have to regard the right source as the "first" (which feels unnatural for people who are used to read left-to-right and top-to-bottom); or you can still work left to right, but then you have to change the mental paradigm: takes "first" (left) source; join to "second" (right) source; then add rows from "second" (right) source that were not used to join to any of the rows from the "first" (left) source.

(I write full joins comparatively often - they are usful for generating big sets when size not content counts).

Aren't you confusing full joins with cross joins? For generating big sets, I always use cross joins.

I was glad to see in your reply to an earlier comment that the appalling format was deliberate, not something that you thought was good format (and hope that applies to the query that was the correct answer too, despite its having only nesting implied by L to R semantics; I would still insist on using indentation to indicate the structure including nesting of the pair of joins in that query, since I don't see it as structurally an different from the original query).

I'm afraid I have to disappoint you. The answer options were all formatted exactly as I always format my queries (except for the SELECT clause, that would normally take three lines). And the only thing I'd change in the original query is to add parentheses and indent the nested join, and make the same change to the SELECT clause,
I think that almost everyone has their own formatting style. And most styles are good, as long as they are followed consequently. I regret that you don't like my style, but I will not change my formatting style for future questions because of that. (Though I would change it to whatever you like if I were to work for you - as I said, styles have to be followwed consequently, so when working for a client, I always try to follow house style).


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
OzYbOi d(-_-)b
OzYbOi d(-_-)b
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1300 Visits: 778
tks Hugo for another great question. cheers
john.arnott
john.arnott
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1466 Visits: 3059
My tired brain is berating my inner self for waiting till the end of the day to look at the QOD. But I'm glad I did.

I was able to choose the correct answer by looking for whether the outer (left or right) joins would allow "extra" rows to be selected. Since the original query was dependent (via the inner join) on the presence of Ordersfor the Customers to be available for possible use in the left outer join to Employees, I looked for which choice supported that relationship. #4 has it right up front with the inner join from Orders to Customers, so it was a short hop from that recognition to picking it as the answer.

I'm just glad at this point that it was a multiple-choice question.
Anipaul
Anipaul
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6275 Visits: 1407
We should have more questions like this one....



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search