User Tools

Site Tools


oracle:meaningoffulljoin

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:

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
FIELDFIELD
d4d4
e5e5
e5e5
f6f6
f6f6
bnull
cnull
anull
null1
null3
null2
oracle/meaningoffulljoin.txt · Last modified: 2016/04/27 10:23 by rlunaro