Transact-SQL Joins

In earlier versions of Microsoft® SQL Server™ 2000, left and right outer join conditions were specified in the WHERE clause using the *= and =* operators. In some cases, this syntax results in an ambiguous query that can be interpreted in more than one way. SQL-92 compliant outer joins are specified in the FROM clause and do not result in this ambiguity. Because the SQL-92 syntax is more precise, detailed information about using the old Transact-SQL outer join syntax in the WHERE clause is not included with this release. The syntax may not be supported in a future version of SQL Server. Any statements using the Transact-SQL outer joins should be changed to use the SQL-92 syntax.

The SQL-92 standard does support the specification of inner joins in either the FROM or WHERE clause. Inner joins specified in the WHERE clause do not have the same problems with ambiguity as the Transact-SQL outer join syntax.

As said above, this is an old 'caveat' with the legacy outer join syntax compared to ANSI joining, that has been around since day one ANSI joins was available.

The primary difference lies in how the query is resolved.The legacy syntax has only one WHERE clause to put both join criteria and filtering criteria, where with ANSI syntax, you have a distinct section in the ON clause for the actual join, and the filtering goes into the WHERE clause.

This difference makes such questions as 'what rows exists in table A but not in table B' simply impossible to write correctly with the old legacy *= syntax, since what we really ask for in these cases are nulls that are a result of the join itself.There are many reasons to switch habits from legacy join syntax to ANSI style, these kinds of queries is one of them.

Couldn't agree more. I personally haven't written queries using the old syntax for something like 10 years. I had just assumed during all that time, that those people, either obstinant or ignorant, writing in the old syntax were getting good data from their queries. This doesn't even address the fact that with the current syntax we can do things that could never have been done in the old:SELECT ....FROM Version VINNER JOIN xON x.Id = V.IdINNER JOIN yON x.Id = y.IdAND y.Version = (SELECT MAX(Version)FROM Y AS y2WHERE y2.VersionId <= v.VersionIdAND y2.Id = y.Id)

This little dive through the old methods was an eye opener that I thought I'd share just in case others were as uninformed. There really are still a lot of people who are using the old approach.

Because of the left join, you would get values that are null, despite the fact that there were no null values in the table. It's not dissimilar to the query you wrote to determine what's in one table, but not the other. When you join with the proper syntax it works just fine. That was the surprise.