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 6:17 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, August 21, 2014 2:36 PM
Points: 1,812, Visits: 899
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!)
Post #1217003
Posted Tuesday, December 6, 2011 6:17 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Saturday, August 30, 2014 4:20 PM
Points: 11,194, Visits: 11,142
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1217004
Posted Tuesday, December 6, 2011 6:19 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:19 AM
Points: 1,385, Visits: 1,243
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.
Post #1217009
Posted Tuesday, December 6, 2011 6:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:28 PM
Points: 5,984, Visits: 8,242
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
Post #1217012
Posted Tuesday, December 6, 2011 6:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Saturday, August 30, 2014 4:20 PM
Points: 11,194, Visits: 11,142
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.

Yes you are mean!

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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1217017
Posted Tuesday, December 6, 2011 6:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:28 PM
Points: 5,984, Visits: 8,242
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
Post #1217026
Posted Tuesday, December 6, 2011 7:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Saturday, August 30, 2014 4:20 PM
Points: 11,194, Visits: 11,142
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1217053
Posted Tuesday, December 6, 2011 7:25 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, August 18, 2014 7:25 AM
Points: 989, Visits: 1,823
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.
Post #1217070
Posted Tuesday, December 6, 2011 7:30 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:18 AM
Points: 1,365, Visits: 1,314
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
Post #1217072
Posted Tuesday, December 6, 2011 8:49 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:07 AM
Points: 1,541, Visits: 8,220
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.


BrainDonor
Linkedin
Blog Site
Post #1217159
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse