is there a way to do multiple left outer joins in oracle?

Question

Why wont this work in oracle?

is there a way to make this work?

FROM table1 a,
     table2 b,
     table3 c
WHERE a.some_id = '10'
AND a.other_id (+)= b.other_id
AND a.other_id (+)= c.other_id

I want table1 to be left outer joined on multiple tables...

If I try to change it to ANSI join I get compilation errors. I did the following:

FROM table2 b, table3 c
LEFT JOIN table1 a ON a.other_id = b.other_id and a.other_id = c.other_id

Answer 1

use ansi joins. They are way clearer IMO. BUt for some reason they don't work with materialized views...

Answer 2

In oracle you cannot outer join the same table to more than one other table. You can create views that have joins in them, then join to that view. As side note, you also cannot outer join to an sub select, so that is not an option here either.

Answer 3

OK, looking at the examples from the Oracle docs, my recollection of the syntax was correct, so I'm turning my comment into an answer. Assuming that your goal is a left outer join where A is the base table, and you join matching rows from B and C, rewrite your query as follows (note that I'm just changing the prefixes; I like to have the source rowset on the right).

FROM table1 a,
     table2 b,
     table3 c
WHERE a.some_id = '10'
AND b.other_id (+)= a.other_id
AND c.other_id (+)= a.other_id

If that's not what you're trying to do, then the query is borked: you're doing a cartesian join of B and C, and then attempting an outer join from that partial result to A, with an additional predicate on A. Which doesn't make a lot of sense.

Answer 4

You can do something like this.

FROM table1 a,     table2 b,     table3 c
WHERE a.some_id = '10'
AND a.other_id = b.other_id(+)
AND a.other_id = c.other_id(+)

Answer 5

I wanted to address separately this part of your question:

If I try to change it to ANSI join I get compilation errors. I did the following:

FROM table2 b, table3 c
LEFT JOIN table1 a ON a.other_id = b.other_id and a.other_id = c.other_id

In an ANSI join, at least in Oracle, you are operating on exactly two row sources. The LEFT JOIN operator in your example has table3 and table1 as its operands; so you cannot reference "b.otherid" in the ON clause. You need a new join operator for each additional table.

I believe what you are trying to do is outer join table 2 and table 3 to table 1. So what you should be doing is this:

FROM table1 a LEFT JOIN table2 b ON b.other_id = a.other_id
              LEFT JOIN table3 c ON c.other_id = a.other_id

or Henry Gao's query if you want to use Oracle-specific syntax.

Answer 6

You could off course try the following (Table b and c being the BASE) FROM (SELECT other_id FROM table2 UNION SELECT other_id FROM table3) b LEFT JOIN table1 a b.other_id = a.other_id

But then again I am an Oracle Nono