SQL Server incorrectly translates Left Outer Join

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL Server incorrectly translates Left Outer Join

Author

Topic

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2009-05-20 : 11:23:05

We have the daunting task of translating the old-fashioned Left-Outer Join (*=) syntax. As we are going from Compatibility mode 8.0 to 9.0.

We thought clicking on a query to 'Design Query in editor' was going to do the job for us but we ran into a problem:

Original query:

SELECT
*
from
AccountExtraField a, IncidentExtraField i
Where
a.AccountID = 'zzz'
and i.IncidentID = 24740
and a.BordereauFieldCode *= i.BordereauFieldCode

SQL Server incorrectly Translates to:

SELECT
*
FROM
AccountExtraField AS a LEFT OUTER JOIN
IncidentExtraField AS i ON a.BordereauFieldCode = i.BordereauFieldCode
WHERE
(a.AccountID = 'zzz') AND (i.IncidentID = 24740)

Should be:

SELECT
*
FROM
AccountExtraField AS a LEFT OUTER JOIN
IncidentExtraField AS i ON a.BordereauFieldCode = i.BordereauFieldCode AND (i.IncidentID = 24740)
WHERE
(a.AccountID = 'zzz')

Anyone know why SQL Server translates this incorrectly? Anyone know of a tool that can translate to 'Join' syntax correctly?

jholovacs
Posting Yak Master

163 Posts

Posted - 2009-05-20 : 12:30:15

Part of the problem with the old syntax was that it could be ambiguous under some circumstances. Technically, the query designed in the interface is a legitimate syntax, and could even be theoretically correct on some RDMBS's given the original query, although it is essentially forcing an inner join and will almost certainly generate a different result set.

I think you said it right when you called it a "daunting task".

SELECT TOP 1
w.[name]
FROM
dbo.women w
INNER JOIN
dbo.inlaws i
ON
i.inlaw_id = w.parent_id
WHERE
i.net_worth > 10000000
AND
i.status IN ('dead', 'dying')
AND
w.husband_id IS NULL
ORDER BY
w.hotness_factor DESC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources