Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Advanced INNER JOIN

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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

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…
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…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

730 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