SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Complex joins


Complex joins

Author
Message
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68999 Visits: 18570
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 Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70161 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
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6677 Visits: 2399
Interesting question. Thanks Hugo!
Revenant
Revenant
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12797 Visits: 5010
Wow - one of the best questions is a long time... Thank you, Hugo!
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26559 Visits: 12506
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

Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26559 Visits: 12506
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
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19229 Visits: 12426
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
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1718 Visits: 778
tks Hugo for another great question. cheers
john.arnott
john.arnott
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3704 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
SSCrazy Eights
SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)

Group: General Forum Members
Points: 9933 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