SQL - Full Outer Join

> (Data|State) Management and Processing > (Data Type|Data Structure) > (Relation|Table) - Tabular data > Structured Query Language (SQL)

1 - 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

Advertising

3 - Example

3.1 - Graphic

3.2 - In Ansi

[email protected]>SELECT table1.id "ID_table1", table2.id "ID_table2"
  2        FROM table1 FULL OUTER JOIN table2
  3       ON  table1.id = table2.id;

3.3 - 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 

3.4 - 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
Advertising

4 - Documentation / Reference