Solved

SQL Advanced INNER JOIN

Posted on 2009-05-18
5
563 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Copy Database Wizard 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.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

749 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