SQL convert from Oracle to ANSI JOIN

Question

How to convert from old Oracle join type to ANSI joins and why?

SELECT *
FROM a, b, c
WHERE b.id (+) = a.id1
AND b.xxx = c.yyy
AND c.id (+) = a.id2

--Should be this 1?

select * from
A
left outer join B on B.ID = A.ID1
left outer join C on C.ID = A.ID2 AND B.xxx = C.yyy

--or this 2?

select * from
A
left outer join C on C.ID = A.ID2
left outer join B on B.ID = A.ID1 AND B.xxx = C.yyy

Answer 1

According to the Oracle documentation:

If the WHERE clause contains a condition that compares a column from table B with a constant, then the (+) operator must be applied to the column so that Oracle returns the rows from table A for which it has generated nulls for this column. Otherwise Oracle returns only the results of a simple join.

So there is an inner join between b and c. And because of the overall conditions, this is going to turn all the joins into INNER JOIN (there needs to be valid values in b and c for that condition to work.

I think the equivalent logic is:

SELECT *
FROM a JOIN
     b
     ON b.id = a.id1 JOIN
     c
     ON c.id = a.id2 AND b.xxx = c.yyyy;

That is, the simple equality eliminates turns the outer joins into inner joins.

Of course, you can test this.