December 7, 2006 at 10:35 pm
Can anyone offer opinion on the relative merits of using an INNER JOIN in select queries rather than implying the join in
the WHERE clause. Is there a performance gain in one or the other (or any other benefits either way), or is it just down to preference?
There are four condition i have been given to you Which run fast and Why ????
1.
SELECT table1.foo, table2.bar
FROM table1, table2
WHERE table1.pkid = table2.fkid
2.
SELECT table1.foo, table2.bar
FROM table1
INNER JOIN table2
WHERE table1.pkid = table2.fkid
3.
SELECT table1.foo, table2.bar
FROM table1
INNER JOIN table2
WHERE table1.pkid = table2.fkid
4.
SELECT table1.foo, table2.bar
FROM table1
INNER JOIN table2
ON table1.pkid = table2.fkid
Your opinion is presious for me ......
Expert Suggestion will be heartly appreciated by me ......
December 8, 2006 at 3:29 am
put it through profiler with varying sizes of data - that way you get to understand and learn the impact rather than just getting someone else to do your thinking. Performance tuning , the learning of, has to be "hands on" that way you get a far better understanding and won't blindly follow someone else's suggestion which may or may not be correct for all scenarios.
As a hint you also need to examine the query plan, I've rewritten queries only to find the optimiser "rewrites" its plan back to what I just rewrote !!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
December 8, 2006 at 7:40 am
The the first and the last queries are valid. The other 2 do not have valid syntax ( you can not use inner join without ON clause)
The first one is equavalent to inner join. So it has the same execution plan with the last one. In terms of performance, they are the same.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply