October 8, 2018 at 11:48 pm
Hi guys,
Can anybody tell me the difference between the two below examples and why I would use one over the other?
Example 1
LEFT OUTER JOIN Company.schema.GeneralParameter GPExcess
ON PCI.ItemValue = GPExcess.ParameterId
AND GPExcess.ParameterName = 'MedicaExcessAmount'
Example 2
LEFT OUTER JOIN (SELECT * FROM Company.schema.GeneralParameter
WHERE ParameterName = 'MedicaExcessAmount'
)GPExcess
ON PCI.ItemValue = GPExcess.ParameterId
Both examples work, just wondering what the differences are.
Thanks
October 9, 2018 at 3:50 am
Check execution plans of both queries.
See if you can spot any difference.
_____________
Code for TallyGenerator
October 9, 2018 at 3:53 am
Well, the second one is using a derived table where clearly none is needed. The other is just a JOIN. Effectively, any JOIN is actually a SELECT * under the covers, so you can use a syntax such as this and you'll still arrive at the same place. The question is, why? It's added muck in a query with no benefits. It's certainly possible that having lots of this pattern may even cause problems for the optimizer as it attempts to unpack a non-standard syntax. There might be reasons to do a derived table like this (say, for example, in order to do some aggregation in the sub-select and then JOIN that to the other table(s)), but to just do it because you can... I sure wouldn't recommend it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 9, 2018 at 7:50 am
The second one is harder to read and takes more typing, and that's about all.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 10, 2018 at 8:06 am
I tend to prefer keeping join criteria separate from filtering criteria so would generally go with a third option
LEFT OUTER JOIN Company.schema.GeneralParameter GPExcess
ON PCI.ItemValue = GPExcess.ParameterId
WHERE GPExcess.ParameterName = 'MedicaExcessAmount'
Generally I find it easier to read.
As long as the execution plans are the same then which you choose will be down to personal preference and/or company standards.
October 10, 2018 at 8:32 am
crmitchell - Wednesday, October 10, 2018 8:06 AMI tend to prefer keeping join criteria separate from filtering criteria so would generally go with a third optionLEFT OUTER JOIN Company.schema.GeneralParameter GPExcess
ON PCI.ItemValue = GPExcess.ParameterId
WHERE GPExcess.ParameterName = 'MedicaExcessAmount'Generally I find it easier to read.
As long as the execution plans are the same then which you choose will be down to personal preference and/or company standards.
It can, however, make quite the difference as to what is returned for outer joins.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 10, 2018 at 8:36 am
crmitchell - Wednesday, October 10, 2018 8:06 AMI tend to prefer keeping join criteria separate from filtering criteria so would generally go with a third optionLEFT OUTER JOIN Company.schema.GeneralParameter GPExcess
ON PCI.ItemValue = GPExcess.ParameterId
WHERE GPExcess.ParameterName = 'MedicaExcessAmount'Generally I find it easier to read.
As long as the execution plans are the same then which you choose will be down to personal preference and/or company standards.
As written, this is an inner join - there's nothing in the code to allow null values of GPExcess.ParameterName. Adjusting this syntax to permit rows on the left with no matching rows on the right makes the code clunky, harder to skim read.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 10, 2018 at 8:46 am
Yes your right the GPExpess is on the RHS so as Jeff pointed out that would effectively turn it into an INNER JOIN.
That's what I get for not trying it out before posting I guess.
October 10, 2018 at 1:06 pm
crmitchell - Wednesday, October 10, 2018 8:06 AMI tend to prefer keeping join criteria separate from filtering criteria so would generally go with a third optionLEFT OUTER JOIN Company.schema.GeneralParameter GPExcess
ON PCI.ItemValue = GPExcess.ParameterId
WHERE GPExcess.ParameterName = 'MedicaExcessAmount'Generally I find it easier to read.
As long as the execution plans are the same then which you choose will be down to personal preference and/or company standards.
However, since this is an outer join, you've changed the meaning of the query and the results returned. And moving the filter to the WHERE and adding 'OR GPExcess.ParameterName IS NULL' does not have the same behaviour either.
Table1 t1 LEFT OUTER JOIN Table2 t2 on t1.ID = t2.ID and T2.colour = 'red'
returns all rows from T1 and, if there's a matching row in t2 that is red, returns that as well
Table1 t1 LEFT OUTER JOIN Table2 t2 on t1.ID = t2.ID
WHERE T2.colour = 'red' OR T2.colour IS NULL
returns all rows from T1 that either had a colour of red or no colour at all.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply