December 22, 2008 at 1:51 pm
I am working on someone else's code and came across a right outer join I am not familiar with. Basically it states
SELECT *
FROM sys.tables t1
RIGHT OUTER JOIN sys.tables t2
INNER JOIN sys.tables t3 ON t3.[object_id] = t2.[object_id]
ON t1.[object_id] = t3.[object_id]
I'm guessing it's basically the same as
SELECT *
FROM sys.tables t1
RIGHT OUTER JOIN (
SELECT t3.[object_id]
FROM sys.tables t2
INNER JOIN sys.tables t3 ON t3.[object_id] = t2.[object_id]
) derived
ON t1.[object_id] = derived.[object_id]
The actual statement is 206 lines and can't create the schema here but the tables are all different and basically are foreign key links to children tables so it's more like a right outer join on data from a child table. I've just never really seen the syntax and wanted some insight.
December 22, 2008 at 1:55 pm
Yup that's the gist of it, basically they are doing a right outer join to a derived table/view of the 2nd two tables. I typically find it's easier and gets me the same result set to rewrite it so it's clearer. I'm sure there are times when due to the filtering/selecting order of joins and where where this will make some difference, but Every time I test it against my data it comes up the same so I go with the more readable of the two.
YMMV of course.
-Luke.
December 22, 2008 at 2:11 pm
I agree with Luke in that I'd rewrite this to be more readable.
The order of joins shouldn't matter, and that's what has been the advice, but you never know. However until it definitely makes a difference, I'd rewrite things to be very clear. You never know who comes behind you to examine this code.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 22, 2008 at 6:20 pm
I agree with the rewrites. The simpler and clearer you can make the query, not only will it be easier for you, and those that come after you, to maintain, but simpler queries are more likely to come through the optimizer performing better. Eliminating unecessary complexity can, especially in queries hundreds of lines long, lead to better performance.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
December 23, 2008 at 5:39 am
Thank you all and as you all stated, I'm the next in line to maintaining this :w00t: so I will clean it up and see if there are any performance issues. Thanks again.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply