December 6, 2011 at 9:12 am
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[/url]
Learn Extended Events
December 6, 2011 at 9:22 am
SQL Kiwi (12/6/2011)
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.
December 6, 2011 at 9:37 am
Interesting question. Thanks Hugo!
December 6, 2011 at 9:46 am
Wow - one of the best questions is a long time... Thank you, Hugo!
December 6, 2011 at 10:01 am
Hugo Kornelis (12/6/2011)
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
December 6, 2011 at 10:46 am
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
December 6, 2011 at 1:05 pm
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).
December 6, 2011 at 2:23 pm
tks Hugo for another great question. cheers
December 6, 2011 at 6:48 pm
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.
December 7, 2011 at 1:19 am
We should have more questions like this one....
December 7, 2011 at 1:50 am
Wow, another excellent question Hugo. Thanks!
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 7, 2011 at 3:30 pm
L' Eomot Inversé (12/6/2011)
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?
I don't find it all that surprising, Tom. It seems like most of the stuff I'm fixing every day is because once it gets beyond a simple query, a c.u.r.s.o.r is used. Personally, I'm pleased that it's this high.
Hugo - excellent question. I'm thoroughly enjoying these questions you've been posting.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
December 7, 2011 at 10:38 pm
I got it wrong.till now id id not worked on nested joins.thanks i leanred one new point today
Malleswarareddy
I.T.Analyst
MCITP(70-451)
February 8, 2012 at 10:04 am
It took around 2 hours for me to solve it and finally got it right 🙂
Ofcourse, learnt something new
Viewing 14 posts - 31 through 43 (of 43 total)
You must be logged in to reply to this topic. Login to reply