Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Advanced INNER JOIN

Posted on 2009-05-18
5
Medium Priority
?
574 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 450 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 450 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 600 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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Integration Management Part 2
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

916 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