|
|
Posted by dportas on 05/20/06 13:47
ehchn1 wrote:
> Hi,
>
> Just curious. Would you use ANSI style table joining or the 'old
> fashion' table joining; especially if performance is the main concern?
>
> What I meant is illustrated below:
>
> ANSI Style
> select * from a join b on a.id = b.id
>
> Old Style
> select * from a, b where a.id = b.id
>
> I noticed that in some SQL, the ANSI is much faster but sometimes, the
> old style looks much better.
>
> It's ridiculous to try out both styles to see which is better whenever
> we want to write an SQL statement.
>
> Please comment.
>
> Thanks in advance.
I'm interested if you have an example where two otherwise identical
inner joins will yield different execution plans with a resulting
difference in performance. Usually they give equivalent plans whether
you specify the join as INNER JOIN or in the WHERE clause. This is
definitely not true for outer joins however. Always use the OUTER JOIN
syntax instead of the *= notation.
I am aware of two very unusual circumstances where you can get
different results from the INNER JOIN syntax. One is a bug in SQL
Server 2000, the other is with the GROUP BY ALL feature (rarely used in
my experience).
It is incorrect to describe your two queries as ANSI versus "Old
Style". Both are compliant with the ANSI standards SQL92, SQL99 AND
SQL2003. Whether and when to specify join conditions using the INNER
JOIN syntax or not is largely a matter of style and clarity. The
convention I normally use is that if the criteria used for the join is
a foreign key between the two tables then I specify it using in the ON
clause, which therefore requires an INNER JOIN. Otherwise I specfy it
in the WHERE clause, which may mean I can leave out the INNER JOIN. I
don't always follow my own rules though :-)
If an OUTER JOIN is involved as well then it's no longer just a matter
of style. Specifying the same criteria in the WHERE clause versus the
ON clause makes a difference to the meaning of the query if it
references the outer part of an outer join. Maybe that explains what
you are referring to as a performance difference. Can you give a fuller
example? Make sure you specify your SQL Server version and service
pack.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Navigation:
[Reply to this message]
|