August 6, 2008 at 8:15 am
Hello:
It appears that these two queries produce the same exact result. Does that make sense? I thought the first one would join A and B, then proceed to join B and C. Where as the second one, it would join B and C, then A and B. Thoughts?
SELECT top 1000 *
FROM tblA
INNER JOIN tblB ON tblA.equip_id = tblB.equip_id
INNER JOIN TblC ON tblB.equip_id = TblC.equip_id
SELECT top 1000 *
FROM tblA
INNER JOIN tblB
INNER JOIN TblC
ON tblB.equip_id = TblC.equip_id
ON tblA.equip_id = tblB.equip_id
August 6, 2008 at 9:11 am
Technically, they both tell SQL Server to do the same thing.
The thing to keep in mind is that, most of the time, you aren't telling SQL Server how to do something, you're just telling it what to do, and it figures out how on its own.
Essentially, both queries are the same.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 6, 2008 at 9:28 am
"INNER JOIN" is an Abelian operator, meaning that like addition and multiplication, you get the same result no matter what order they are done.
OUTER Joins are NOT, however, so order and association matter very much to them.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 6, 2008 at 11:06 am
Everything the other guys said is 100% accurate, but one other note, you may want to take into account.... With really simple queries like the ones you showed, the optimizer doesn't have to do much work to arrive at an optimal plan, so placement of the code within the query may not matter. But when the queries get really complex, you may see performance differences between the two approaches because the optimizer only has so long to try various combinations of plans, it might not choose the optimal plan if all the join criteria is in the WHERE clause. It's a better practice, in terms of documentation and readability, to keep the join condition in the ON clause, but in certain instances, it's also more performant.
"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
August 7, 2008 at 12:05 pm
I see what you guys are saying. Thanks. I went ahead and did the same test on a LEFT OUTER Join. Similar results - I got the same results back regardless of which way the query was written. Good point about performance. I'll have to remember to check both scenarios as my app. grows.
SELECT top 6 p.Name, pr.ProductReviewID, pp.ProductPhotoID
FROM Production.Product p
LEFT OUTER JOIN Production.ProductReview pr ON p.ProductID = pr.ProductID
LEFT OUTER JOIN Production.ProductProductPhoto pp ON pr.ProductID = pp.ProductID
order by pr.productreviewid desc
SELECT top 6 p.Name, pr.ProductReviewID, pp.ProductPhotoID
FROM Production.Product p
LEFT OUTER JOIN Production.ProductReview pr
LEFT OUTER JOIN Production.ProductProductPhoto pp
ON pr.ProductID = pp.ProductID
ON p.ProductID = pr.ProductID
order by pr.productreviewid desc
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply