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:

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');

Let's execute a full join:

SELECT * 
  FROM test1 FULL JOIN test2 
  ON test1.field = test2.field;

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