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


Complex joins


Complex joins

Author
Message
Rich Weissler
Rich Weissler
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2013 Visits: 954
Cadavre (12/6/2011)
I assumed that understanding the logic of join operators is integral to the majority of the visitors to this site.

Not, I believe, a safe assumption. In my daily routine as an "Accidental DBA", I write relatively few queries, and very, very few complex queries with joins. I visit the site to learn, so that I have a chance of writing the queries quickly and efficiently when needs arise. (Of course, something is apparently rubbing off, 'cause I apparently got today's question right AND learned something!)
Paul White
Paul White
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: 10332 Visits: 11350
michael.kaufmann (12/6/2011)
One minor flaw I'd attribute to speedily writing this query--the above one will only yield the same result as the original one if only employees are the customers and the IDs as customer and employee are equal.

Thanks Michael - I did indeed write the syntax quickly, just from the execution plan, before Hugo so helpfully posted table definitions and sample data. That'll teach me, I guess.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Tao Klerks
Tao Klerks
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1393 Visits: 1249
Thanks Hugo,

Great question, and between your form and Paul's I now have 2 more cases that my T-SQL formatter doesn't handle correctly; at least it's not mangling the SQL, but the resulting indentation makes little sense (and the parse tree is just wrong).

Any objection to my adding it to my test suite (which is also the demo list on poorsql.com) once I've fixed it?

http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
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: 8275 Visits: 11536
Tao Klerks (12/6/2011)
Any objection to my adding it to my test suite (which is also the demo list on poorsql.com) once I've fixed it?

No problem for me!

Sorry for breaking your formatter - and good luck getting it fixed. ;-)


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Paul White
Paul White
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: 10332 Visits: 11350
Hugo Kornelis (12/6/2011)
The few timies where I did choose to use nested joins in actual code, I have always used parentheses and indentation for easier reading. In fact, my first draft of this question did use exactly that. I just took it out before submitting the question, because I am mean.Whistling

Yes you are mean! Laugh

Actually, I think I rather misunderstood the point of the question. I thought you were intending to illustrate SQL/relational equivalences or something like that, but I now see you were aiming at the 'nested join' idea. I'll have to think about that a bit more to see if I understand the point completely.

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.

Hmm - but doesn't the CTE/derived table example break that assertion? Or are they still 'nested joins' somehow. I think that's the point I'm struggling with - what precisely 'nested joins' means.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
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: 8275 Visits: 11536
SQL Kiwi (12/6/2011)
Hmm - but doesn't the CTE/derived table example break that assertion?

It does. I only realised the CTE possibility later, after I already posted that earlier post.
(For some reason I can't really explain, I am not very fond of the derived table version - I've used derived tables when needed, but always found the syntax to be lacking readability).


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Paul White
Paul White
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: 10332 Visits: 11350
Hugo Kornelis (12/6/2011)
(For some reason I can't really explain, I am not very fond of the derived table version - I've used derived tables when needed, but always found the syntax to be lacking readability).

I'm somewhere in the middle on this one. I'd like to get you and Adam Machanic together in a room on this point though - he's a fierce advocate of the benefits of derived tables over CTEs. Something about the ability to select blocks of code to run easily I think is one main argument in favour.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
jeff.mason
jeff.mason
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1153 Visits: 2125
This is one of the best QOTDs I have ever seen here. I had to really think this through to get it right. I ALMOST just checked #1 out of first impulse but figured it couldn't be that easy, especially once I saw the RIGHT JOIN answers. Wondering what would cause a DBA to make it that tough, I read the first query very carefully and realized that that the inner join would happen first and wouldn't happen the same on the query #1. So I ruled that out and #2 was easy to rule out. #3 screwed up the WHERE clause, so it had to be #4. A lot of work for first thing in the morning, but a good set of work as in my experience developers use nested joins not intentionally but by accident, as they are not up to speed on ANSI joins and are used to doing joins in the WHERE clause. And when they move to ANSI they sometimes do things like this. So very practical for real life troubleshooting.
honza.mf
honza.mf
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: 1745 Visits: 1323
Hugo Kornelis (12/6/2011)
honza.mf (12/6/2011)
Probably I will use right joins and nested joins only in case of self-defense. I prefer the readability and I think from left to right.

Absolutely true. This question was intended to illustrate how a real-life problem sometimes forces you to choose the lesser of two evils. Both nested joins and right outer joins reduce readability and ease of understanding of a query - so pick your poison. I have been in this position a few times, and whatever I chose, I always felt it was not the optimal choice.

Yes, if I need to choose a poison, I use some antidote. Here it can be anything that makes query more readable: right join with conditions near the table, parentheses around the nested join, CTE, or subquery. Some are better for hand made queries, some for generated code, it depends.



See, understand, learn, try, use efficient
© Dr.Plch
BrainDonor
BrainDonor
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2012 Visits: 11167
Fascinating question Hugo, thank you.

I had to re-read 'Formatting Your FROM Clause' in the first volume of 'MVP Deep Dives' to answer it. Knowing where to find the answers is half of the battle.

Steve Hall
Linkedin
Blog Site
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