Solved

SQL Advanced INNER JOIN

Posted on 2009-05-18
5
561 Views
Last Modified: 2012-05-07
Can anyone explain how T-SQL interprets the following queries based on their respective joins? Does SQL ignore where the ON part of the JOIN is located? They seem to return the same rows and values...but I have never seen the second style join EVER used -- I've search google for hours now, with no explanation.

SELECT *
FROM BillingItem As B
   INNER JOIN Charge As C
      on B.ChargeID = C.IDCharge
   INNER JOIN Patient As P
      On P.IDPatient = C.PatientID

SELECT *
FROM BillingItem As B
   INNER JOIN Charge As C  
   INNER JOIN Patient As P
   On P.IDPatient = C.PatientID       
   On B.ChargeID = C.IDCharge


Thanks -

Mike
0
Comment
Question by:dsiproductteam
5 Comments
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 150 total points
ID: 24417185
>Can anyone explain how T-SQL interprets the following queries
run the queries with the Query -> include actual explain plan set, and you will get the full explain plan in an additional tab.
speaking of the sql management studio ...



apart from that, I have read recently about the second style... which is, imho, just an obscure form that actually does the same .
so, my suggestion is: forget it ...
0
 

Author Comment

by:dsiproductteam
ID: 24417212
The execution plans are the same in SQL 2005, but appear slightly different in SQL 2000. SQL 2000 includes information about Hash Match/Join, which I am totally unfamiliar with.

I am downloading some live client data right now to compare the difference in the behavior in SQL 2000 and 2005 just to make sure. I'll post that information when I have it.

Thanks..
0
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 150 total points
ID: 24417293
both statements are the same....the 2nd is just uglier than the first.
0
 
LVL 9

Accepted Solution

by:
tculler earned 200 total points
ID: 24422135
I'd agree with chapmandew. The way the SQL "Compiler", if you will, works is by taking multiple passes at your statement. It looks like they designers of this particular RDBMS allowed a different syntax with multiple inner joins. Because of the multiple passes, either query will result in the "compiler" disassembling the query down to the same thing.
0
 

Author Closing Comment

by:dsiproductteam
ID: 31582808
Thanks for your help. Through my research, I've come to the same conclusion.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question