Table of Contents

About

A “Full Outer Join” is interpreted as :

  • show me all data from the left table and all the data from the the right table regardless of any matches.
  • or give me every row from both tables, regardless of whether or not it has a match in the other table

It does then :

  1. an outer join of the two tables
  2. an anti join of the two tables
  3. then an UNION ALL of the two result data set

A “Full Outer Join”:

  • Causes all records from each data source to be processed, even if they have properties that are not shared
  • Causes the data of shared records to be merged into a single record
  • Ensures that no records get lost or dropped

Fulloutertableatableb

Example

Graphic

Full Outer Join Result Discard

In Ansi

select table1.id "ID_table1", table2.id "ID_table2"
        from table1 FULL OUTER JOIN table2
       on  table1.id = table2.id;

In Oracle SQL

The syntax below is not correct: 

select table1.id "ID_table1", table2.id "ID_table2"
from table1, table2
where table1.id (+)= table2.id (+);

You will get this message: 

ORA-01468: a predicate may reference only one outer-joined table
01468. 00000 -  "a predicate may reference only one outer-joined table"
*Cause:    
*Action:
Error at Line: 55 Column: 19

On workaround is to make two outer join on each table and to join the data set with an union such as: 

SELECT
  table1.id "ID_table1",
  table2.id "ID_table2"
FROM
  table1,
  table2
WHERE
  table1.id = table2.id (+)
UNION
SELECT
  table1.id "ID_table1",
  table2.id "ID_table2"
FROM
  Table1,
  Table2
WHERE
  table1.id (+) = table1.id

The Result

The source table:

table1
(inner set) 		table2
(outer set)
Column ID Column ID Column ID_2
A A
B B A
D C C

The Result: 

ID_table1  ID_table2
---------- ----------
A          A
B          B
D
           C

Documentation / Reference