ORA-01417 - Two outer joins error. New join syntax?

Question

I never got my head around the "new" SQL join syntax, and therefore use the "old" join system, with the (+). I know it's about time I learned it - however I just find the old syntax a lot more intuitive, especially when working with multiple tables with multiple joins.

However I now have an operation which requires two outer joins on the same table. My code is:

SELECT
    C.ID,
    R.VALUE,
    R.LOG_ID,
    LOG.ACTION
FROM
    C,
    R,
    LOG
WHERE
    C.DELETED IS NULL
    AND R.DELETED IS NULL
    -- Two joins below
    AND R.C_ID(+) = C.ID
    AND R.LOG_ID(+) = LOG.ID

However this results in an error:

ORA-01417 - A table may be outer joined to at most one table.

Searching for this error I find that the solution is to use the new syntax For example this answer on SO: Outer join between three tables causing Oracle ORA-01417 error

So I am aware that some may consider this question a duplicate as it technically already has an answer. However the "old" syntax posed in that question does not contain exactly the same number of tables and joins as I have here, and try as I might, I'm not sure how I would factor this in to my own code.

Is anyone able to assist? Thanks.

Answer 1

I think you want:

SELECT C.ID, R.VALUE, R.LOG_ID, LOG.ACTION
FROM C LEFT JOIN
     R 
     ON R.C_ID = C.ID LEFT JOIN
     LOG
     ON R.LOG_ID = LOG.I
WHERE C.DELETED IS NULL AND
      R.DELETED IS NULL;

The "new" (it is 25 years old) outer join syntax is actually very easy to follow, particularly for a simple example with just LEFT JOIN.

The idea is you want to keep all rows from one table (perhaps subject to filters in the WHERE clause). That is the first table. Then you use a chain of LEFT JOIN to bring in other tables.

All rows from the first table are in the result set. If there are matching rows in the other tables, then columns from those tables come from matching rows. If there are no matches, then the row from the first table is kept.