Difference between two join method

Question

Post reply

Difference between two join method

NikosV

SSCrazy

Points: 2111
More actions
October 8, 2018 at 11:48 pm

#373978

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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

Check execution plans of both queries.

See if you can spot any difference.

_____________
Code for TallyGenerator

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

crmitchell SSCarpal Tunnel Points: 4557 More actions · Answer 4

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.

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 5

crmitchell - Wednesday, October 10, 2018 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.

It can, however, make quite the difference as to what is returned for outer joins.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

crmitchell - Wednesday, October 10, 2018 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.

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.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

crmitchell SSCarpal Tunnel Points: 4557 More actions · Answer 7

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

crmitchell - Wednesday, October 10, 2018 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.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass