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/Data Platform MVP (2006-2016)
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis