How to perform FULL OUTER JOIN in ORACLE using '+' operator?

Question

Instead of using keywords like FULL OUTER JOIN or FULL JOIN, how can I perform full outer join using 'where' clause with the help of '+' operator?!

Answer 1

You can't (at least directly). Oracle only supports a full outer join using SQL:1999 syntax.

You can fake it by unioning two outer joins:

select a.field1, b.field2
from table_a a, table_b b
where a.id = b.id(+)
union all 
select a.field1, b.field2
from table_a a, table b b
where a.id(+) = b.id
      and a.id is null

It's a lot more readable using the SQL:1999 syntax:

select a.field1, b.field2
from table_a a full outer join table_b b
on a.id = b.id