HiveBrain v1.2.0
Get Started
← Back to all entries
patternModerate

Peculiar Oracle outer join syntax case

Submitted by: @import:stackexchange-dba··
0
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:

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:

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_KEY


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

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_KEY

Context

StackExchange Database Administrators Q#412, answer score: 11

Revisions (0)

No revisions yet.