Solved

SQL Advanced INNER JOIN

Posted on 2009-05-18
5
562 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 143

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

840 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