SQL - Full Outer Join

Data System Architecture

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





Discover More
Bobj Outer Join Properties
BOBJ - Outer Join

An outer join is a join that links two tables, one of which has rows that do not match those in the common column of the other table. More ... You define an outer join by specifying which table is...
Data System Architecture
Dimensional Data Operation - Data Densification (sparse to dense data) - Preservation of the dimensions

You may want to view the data in dense form, with rows for all combination of dimension values displayed even when no fact data exist for them. The densification is also : known as thepreservation...
Obiee Fact Cross Join
OBIEE - Densification with the fact-based fragmentation capabilities

You may want to view the data in dense form, with rows for all combination of dimension values displayed even when no fact data exist for them. The preservation of the dimensions is is also well known...
Obiee Logical Join Bmm
OBIEE - Full outer Join

To perform a , you have two ways : in the repository by using the fact vertical partitioning capabilities or with the Obiee logical sql To know what sort of join is a full outer join, follow this...
Data System Architecture
SQL - Joins (Home)

in SQL. A join is a SQL clause statement which define the set operation such as: intersection, union, that the database must perform between two or more relation (table, view, query, ...). The...
Data System Architecture
SQL - Outer Join

An outer join extends the result of a equi join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other...



Share this page:
Follow us:
Task Runner