The explanation is really simple:

With the old syntax it's impossible to determine what part of the where statement is used to join the tables and which part is used to filter the records, so the database server has to guess.

With the proper syntax the part of the join which is used to join the table is after the ON and the part to filter the records is after the WHERE.

Hope it makes sense,

Auke

So it actually reads the old statement as

SELECT
 S.IIATransactionId,
 substring(rats_filename, 1+patindex('%{________-____-____-____-____________}%',  rats_filename), 36) AS OracleTransactionId
FROM
 iiafeedtransaction S
LEFT OUTER JOIN
 ratsiiafeedtransaction o
ON
 S.IIATransactionId = substring(rats_filename,   1+patindex('%{________-____-____-____-____________}%', rats_filename), 36)
and
 o.Rats_filename IS  NULL
ORDER BY
 S.IIATransactionId

Actually, your logic is incorrect.

What are you basing your where statement on? It is where rats_filename is null.

Look at your ratsiiafeedtransaction table again.. there are no null fields in this table.

so something like the below (and this is quick and nasty) works:

 

select a, oracletransactionid

from (

SELECT S.IIATransactionId as a, substring(o.rats_filename,1+patindex('%{________-____-____-____-____________}%',rats_filename),36) AS OracleTransactionId

FROM iiafeedtransaction S, ratsiiafeedtransaction o

WHERE S.IIATransactionId *=

substring(o.rats_filename,1+patindex('%{________-____-____-____-____________}%',o.rats_filename),36)

) data

where oracletransactionid is null

order by a

 

FYI: SQL 2005 does not allow this style of code by default, you have to override the database to do it. And it is claimed that future version wont support it at all.  even in backward compat. mode.

 

Here's the official info from BOL:

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.

/Kenneth

Sorry about that. This article wrote itself since I just copied & pasted the code. Usually I'd write the code for an article seperately. Again, sorry for the confusion.

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 V
INNER JOIN x
ON x.Id = V.Id
INNER JOIN y
ON x.Id = y.Id
AND y.Version = (SELECT MAX(Version)
FROM Y AS y2
WHERE y2.VersionId <= v.VersionId
AND 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.