JOIN SYNTAX - DIfference between SQL-92 and Legacy Syntax

[karthik555]

SQL Server Documentation says,
"SQL Server supports both the SQL-92 outer join syntax and a legacy syntax for specifying outer joins based on using the *= and =* operators in the WHERE clause. The SQL-92 syntax is recommended because it is not subject to the ambiguity that sometimes results from the legacy Transact-SQL outer joins"

1. Is there any performance gain in using one over the second? Why?
2. What are the pros and cons?

Thanks in advance.

Karthik

 

[SQLSister]

Never under any circumstances use =* or *=. AS BOL says you do not get consistent results. What this means is that it will sometimes interpt this a as a cross join which is a performance hog as well as not generally giving you the results you want.

Questions about posting. See faq183-874