patternModerate
Peculiar Oracle outer join syntax case
Viewed 0 times
casejoinsyntaxpeculiaroracleouter
Problem
I have seen the following in a query that was supposed to be ported from Oracle outer join syntax to SQL standard outer join syntax:
Now translating the outer join syntax is normally quite a mechanical process, but that last line had me baffled. What does it mean? What effect does it have?
SELECT ...
FROM A, B, C, D, E
WHERE A.A_ID = B.A_ID
AND B.B_ID = C.A_ID(+)
AND B.B_KEY = C.B_KEY(+)
AND C.C_ID = D.C_ID(+)
AND B.A_ID = E.A_ID(+)
AND B.B_KEY = E.B_KEY(+)
AND 'CONSTANT' = C.X_ID(+)Now translating the outer join syntax is normally quite a mechanical process, but that last line had me baffled. What does it mean? What effect does it have?
Solution
I tried to perform the mechanical process. I hope I remember it right.
This leads to:
In short I think Leigh Riffel's answer is correct.
Note
in old days the rule to memorize was:
oracle where A.a = B.b (+)
becomes A.a *= B.b in SQL-Server old syntax the plus goes to the opposite side and becomes a star,
which means A left join B on A.a = B.b
This leads to:
SELECT ...
FROM A
join B on A.A_ID = B.A_ID
left join C on B.B_ID = C.A_ID and B.B_KEY = C.B_KEY and 'CONSTANT' = C.X_ID
left join D on C.C_ID = D.C_ID
left join E on B.A_ID = E.A_ID and B.B_KEY = E.B_KEYIn short I think Leigh Riffel's answer is correct.
Note
in old days the rule to memorize was:
oracle where A.a = B.b (+)
becomes A.a *= B.b in SQL-Server old syntax the plus goes to the opposite side and becomes a star,
which means A left join B on A.a = B.b
Code Snippets
SELECT ...
FROM A
join B on A.A_ID = B.A_ID
left join C on B.B_ID = C.A_ID and B.B_KEY = C.B_KEY and 'CONSTANT' = C.X_ID
left join D on C.C_ID = D.C_ID
left join E on B.A_ID = E.A_ID and B.B_KEY = E.B_KEYContext
StackExchange Database Administrators Q#412, answer score: 11
Revisions (0)
No revisions yet.