When doing outer join on multiple tables or two tables on multiple conditions in Oracle, if you are using (+) operator, you must use it in all of these conditions. If you do not, then Oracle Database will return only the rows resulting from a simple join, but without a warning or error to advise you that you do not have the results of an outer join.

For example, to return all rows from T2 when join two tables T1 and T2 (right outer join), and for any rows in T2 that have no matching rows in T1, Oracle returns NULL for any select list expressions containing columns of T1. The query would be like the following with (+) operator:

SELECT column_list 
FROM T1, T2 
ON T1.c1(+) = T2.c1 AND T1.c2(+) = T2.c2 AND T1.c3(+) = T2.c3;

That seems to be obvious and easy to remember. But what about when join multiple tables? Say if we would like to join one more table T0. T0 and T1 are connected with a foreign key. So you would like to return all rows from T2, but for rows in T2 have no matching rows in T1, then T0, you want to see columns from T1 and T0 in select marked as NULL. In this case, you need to put (+) on T0 as well.

SELECT column_list 
FROM T0, T1, T2 
ON T0.c0(+) = T1.c0 and T1.c1(+) = T2.c1;

Without (+) on T0.c0, you will just have all matching rows from T2 & T1, then T0 as simple join among them.

What about using “right outer join” keywords? You need to put them for all tables involved.

SELECT column_list 
FROM T0 right outer join T1 on T0.c0 = T1.c0 
right outer join T2 ON T1.c1 = T2.c1;

If you think no need to do “right outer join” between T0 & T1 with the following query:

SELECT column_list 
FROM T0 join T1 on T0.c0 = T1.c0 
right outer join T2 ON T1.c1 = T2.c1;

You will get all rows from T2, but for columns from T1 and T0 in select, they will not be marked as NULL, but just replicate of values from T2.

So when doing outer join two tables, but wanting extra columns from more tables which have relations only to one of tables, remember to put outer join on all tables, not just that two tables.

• Oracle