Link to home
Start Free TrialLog in
Avatar of Jim Horn
Jim HornFlag for United States of America

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Jim Horn

ASKER

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?
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?
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.
>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'.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks guys.  -Jim
Avatar of neilxt
neilxt

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.