Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

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

share|improve this answer
1  
Interesting and helpful formula . Thank u :) –  vettipayyan Jan 8 '11 at 17:50
    
Excellent conversion. –  Leigh Riffel Jan 10 '11 at 14:44
add comment

The line requires c.X_ID to be equal to the constant value or for there to be no record from the C table. Of course since it is left joined it won't limit the records from the A table, only limit the records from the C table that get joined. Here is a demonstration:

Setup:

CREATE TABLE T1 as (select rownum+1 t1_id from dual connect by rownum <= 4);
CREATE TABLE T2 as (
   select rownum t1_id, DECODE(rownum,2,'CONSTANT',3,'NoMatch') CompareField 
   from dual connect by rownum <= 3
);
SELECT * FROM T1;
SELECT * FROM T2;

Results:

SELECT T1.t1_id, T2.t1_id, T2.CompareField
FROM T1, T2
WHERE T1.t1_id = T2.t1_id(+)
AND 'CONSTANT' = T2.CompareField(+)
ORDER BY 1;

Or:

SELECT T1.t1_id, T2.t1_id, T2.CompareField
FROM T1 LEFT JOIN T2 ON T1.t1_id = T2.t1_id 
AND 'CONSTANT' = T2.CompareField
ORDER BY 1;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.