Jim Horn
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-200 8', 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-200 8', 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
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-200
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-200
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
>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'.
(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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks guys. -Jim
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.
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.
ASKER
Since both add up to 100%, that doesn't seem to be the best way to compare.
How would you guys do it?