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:
FIELD | FIELD |
d4 | d4 |
e5 | e5 |
e5 | e5 |
f6 | f6 |
f6 | f6 |
b | null |
c | null |
a | null |
null | 1 |
null | 3 |
null | 2 |