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 :
- an outer join of the two tables
- an anti join of the two tables
- 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
Articles Related
Example
Graphic
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