ANSI JOIN vs. Non-ANSI JOIN in T-SQL: What are the Performance Differences?

Performance difference between ANSI JOIN and non-ANSI JOIN in T-SQL

In T-SQL stored procedures, non-ANSI JOIN syntax has always been common:

<code class="language-sql">SELECT A.A, B.B, C.C
FROM aaa AS A, bbb AS B, ccc AS C
WHERE
    A.B = B.ID
AND B.C = C.ID
AND C.ID = @param</code>

There is speculation that replacing this with ANSI-92 JOIN syntax could improve performance:

<code class="language-sql">SELECT A.A, B.B, C.C
FROM aaa AS A
JOIN bbb AS B
   ON A.B = B.ID
JOIN ccc AS C
   ON B.C = C.ID
   AND C.ID = @param</code>

Are they the same?

In this particular example, the execution of both queries is identical. However, ANSI-92 syntax has several advantages:

• Clarity: The JOIN clause separates logic into relations and filters, enhancing readability.
• Avoid ambiguity: In some cases, non-ANSI outer joins can lead to ambiguous results or deadlocks. ANSI-92 syntax eliminates this situation.
• Standards Compliance: ANSI-92 is the current industry standard and has been adopted by most developers and query tools.
• Avoiding unexpected Cartesian products: The JOIN clause helps prevent unexpected Cartesian products.

Arguments with non-ANSI syntax

Some argue that non-ANSI syntax helps conceptualize SQL as a Cartesian product followed by filtering. While this technique can aid understanding, the preferred ANSI-92 syntax is recommended because it is generally clearer and standards-compliant. The only exception to this rule is when using natural joins, which should be avoided entirely due to their inherent risks.

The above is the detailed content of ANSI JOIN vs. Non-ANSI JOIN in T-SQL: What are the Performance Differences?. For more information, please follow other related articles on the PHP Chinese website!