Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

Complex joins Expand / Collapse
Author
Message
Posted Tuesday, December 6, 2011 9:12 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Friday, August 29, 2014 1:51 PM
Points: 21,644, Visits: 15,317
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1217185
Posted Tuesday, December 6, 2011 9:22 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, August 28, 2014 4:00 AM
Points: 21,397, Visits: 9,612
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.
Post #1217195
Posted Tuesday, December 6, 2011 9:37 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, August 29, 2014 10:23 AM
Points: 3,299, Visits: 1,978
Interesting question. Thanks Hugo!
Post #1217211
Posted Tuesday, December 6, 2011 9:46 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, August 22, 2014 8:50 AM
Points: 4,425, Visits: 3,417
Wow - one of the best questions is a long time... Thank you, Hugo!
Post #1217221
Posted Tuesday, December 6, 2011 10:01 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 8:48 AM
Points: 8,739, Visits: 9,287
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
Post #1217239
Posted Tuesday, December 6, 2011 10:46 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 8:48 AM
Points: 8,739, Visits: 9,287
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
Post #1217295
Posted Tuesday, December 6, 2011 1:05 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:24 AM
Points: 5,977, Visits: 8,239
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
Post #1217386
Posted Tuesday, December 6, 2011 2:23 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:53 AM
Points: 1,176, Visits: 778
tks Hugo for another great question. cheers
Post #1217435
Posted Tuesday, December 6, 2011 6:48 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:53 PM
Points: 1,521, Visits: 3,039
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.
Post #1217507
Posted Wednesday, December 7, 2011 1:19 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, August 29, 2014 4:30 AM
Points: 5,345, Visits: 1,388
We should have more questions like this one....


Post #1217612
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse