ANSI JOIN vs. OUTER JOIN

Q
Can I improve performance by using the ANSI-style joins instead of the old-style joins?

A
You will not get any performance gain by switching to the ANSI-style JOIN syntax. However, when you are dealing with outer joins, the old-style JOIN (=* or *=) is not the exact equivalent of the ANSI-style JOIN.

We strongly suggest that you move to the ANSI-style JOIN syntax. Although old habits are hard to break (we know, we've been there), we've found that after you've become comfortable with the ANSI-style JOIN, you'll quickly prefer using it to code queries. Using the ANSI-JOIN syntax gives you an important advantage: Because the join logic is cleanly separated from the filtering criteria, you can understand the query logic more quickly.

One thing to be aware of is that you should not expect to be able to mechanically convert an old-style OUTER JOIN to an ANSI-style JOIN and receive the same output. You might not replicate the results, because the SQL Server old-style JOIN executes the filtering conditions before executing the joins, whereas the ANSI-style JOIN reverses this procedure (join logic precedes filtering).

Perhaps the most compelling argument for switching to the ANSI-style JOIN is that Microsoft has explicitly stated that SQL Server will not support the old-style OUTER JOIN syntax indefinitely. Another important consideration is that the ANSI-style JOIN supports query constructions that the old-style JOIN syntax does not support.

Related Articles by Categories