• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4660
  • Last Modified:

Joining tables in T-SQL: INNER JOINs vs. WHERE clauses

At a new employer, I'm finding a whole lot of T-SQL (1st) where tables are joined by piling them in the FROM clause, and specifying the relationships in the WHERE clause, versus (2nd) the way I was always taught to do this, by FROM one table INNER JOIN ... the rest of the tables.

Other than apparently speed, are there any issues with leaving SQL statments in the 1st manner?

My background is Access, so I'm not familiar with this, or with query optimization.

TIA
Jim

-- (1st) 11871 rows in 1 second
SELECT policy_no, sf_affinity_desc
FROM ufn_selling_agent('1-1-2008', null, null) sa, certificate c, policy p, sales_force sf, sales_force_affinity sfa
WHERE sa.cert_id = c.cert_id and
c.series_id = p.series_id and
c.cert_no = p.cert_no and
sa.sales_force_id = sf.sales_force_id and
sf.sf_affinity_cd = sfa.sf_affinity_cd

-- (2nd) Modified by me based on what I'm used to, 11871 rows in 0.? seconds
SELECT policy_no, sf_affinity_desc
INTO glfeed_selling_agents
FROM ufn_selling_agent('1-1-2008', null, null) sa
INNER JOIN certificate c ON sa.cert_id = c.cert_id
INNER JOIN policy p ON c.series_id = p.series_id and c.cert_no = p.cert_no
INNER JOIN sales_force sf ON sa.sales_force_id = sf.sales_force_id
INNER JOIN sales_force_affinity sfa ON sf.sf_affinity_cd = sfa.sf_affinity_cd
0
Jim Horn
Asked:
Jim Horn
3 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
JOIN will be
1) eventually better in performance
2) better to read (at least for all people that use SQL a bit really)
3) better to modify the conditions
4) better to change when changing inner to/from outer joins
5) better to see when conditions are missing
0
 
digital_thoughtsCommented:
Really, there's not much difference, both are INNER JOINs, where JOIN statements really come in to play is for LEFT, RIGHT, with and without OUTER. If you want, compare the execution plans to see if there's truly any difference.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
I ran both with 'Show Execution Plan' checked, and looking at the results it seems to only give percentages, instead of time of execution.  

Since both add up to 100%, that doesn't seem to be the best way to compare.

How would you guys do it?
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Faiga DiegelSr Database EngineerCommented:
You would have 2 set of percentage that add ups to 100%. What is the percentage of the first one? compared to the percentage of the 2nd query?
0
 
Faiga DiegelSr Database EngineerCommented:
And another way to compare, run a profiler. Look into the I/O, CPU and duration. Run the 1st query then run the 2nd query. for me, it's one of the best way to compare queries on how they are doing with the hard disk read/write and CPU consumption.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
>What is the percentage of the first one? compared to the percentage of the 2nd query?
(not sure if this is what you're getting at, but here 'goes...)
When they are both run together, 50.00% - 50.00% = 100%.
When they are both run separately, they both add up to 100%.

>And another way to compare, run a profiler.
SQL Profile, when connecting to server displays errmsg 'In order to run a trace against SQL Server you have to be a member of sysadmin fixed server role'.
0
 
Faiga DiegelSr Database EngineerCommented:
Oh. That means you dont have privilege to run profiling. Are you using 2005? If yes, ask your administrator to set permission to your account. Here's the grant permission script;

GRANT ALTER TRACE TO [UserAccount]

You should be able to run profiler with that granted permission.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
Thanks guys.  -Jim
0
 
neilxtCommented:
In early versions of SQL, especially in DB2 on the mainframe, there were no "JOIN" constructs and it HAD to be done in the WHERE clause.

I bet this has nothing to do with speed and is just someone who hasn't cottoned on to Joins yet or wanted to remain compatible with DB2.
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now