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
LVL 67
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.