The benefit of doing these type of joins in the FROM clause rather than the WHERE clause is the same as it is for an equijoin. For inner joins, the two different syntaxes are equivelent. For outer joins, performing the joins in the WHERE clause will cause non-matched rows to be excluded. This is because any comparison to NULL (i.e. 5 = NULL, NULL = NULL, NULL BETWEEN 5 AND 20) returns false. Furthermore, Microsoft recommends that all joins be done in the FROM clause and warns that support for WHERE clause joins may be dropped in future versions of SQL Server.
Chris Cubley
Chris Cubley
Chris Cubley
www.queryplan.com