====== How does full join work??? ======

Despite I am seasoned sql developer, sometimes I have doubts on how exactly full join work; so I decided to implement this simple test to see this working. 

Let's say we have two tables: 

<code sql>
create table test1
(field  varchar2(100));

create table test2
(field varchar2(100));

-- unique values for table test1 
insert into test1 values ('a');
insert into test1 values ('b');
insert into test1 values ('c');

-- unique values for table test2 
insert into test2 values ('1');
insert into test2 values ('2');
insert into test2 values ('3');

-- common values for tables test1 and test2 
-- with only one record
insert into test1 values ('d4');
insert into test2 values ('d4');

-- common values with duplicate value in test1
insert into test1 values ('e5');
insert into test1 values ('e5');
insert into test2 values ('e5');

-- common values with duplicate value in test2 
insert into test1 values ('f6');
insert into test2 values ('f6');
insert into test2 values ('f6');

</code>

Let's execute a full join: 

<code sql>
select * 
  from test1 full join test2 
  on test1.field = test2.field; 

</code>

The result is: 

  * Values that are only in one of the tables, appear with the values of the other table as null
  * Common values appear, and if it is neccessary, appear duplicated 

|FIELD|FIELD|
|d4|d4|
|e5|e5|
|e5|e5|
|f6|f6|
|f6|f6|
|b|null|
|c|null|
|a|null|
|null|1|
|null|3|
|null|2|