User Tools

Site Tools


oracle:meaningoffulljoin

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision
Previous revision
oracle:meaningoffulljoin [2016/04/27 09:48]
rlunaro created
oracle:meaningoffulljoin [2016/04/27 10:23] (current)
rlunaro
Line 5: Line 5:
 Let's say we have two tables:  Let's say we have two tables: 
  
-<code>+<code sql>
 create table test1 create table test1
 (field  varchar2(100)); (field  varchar2(100));
Line 16: Line 16:
 insert into test1 values ('b'); insert into test1 values ('b');
 insert into test1 values ('c'); 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> </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|
 +
  
oracle/meaningoffulljoin.1461743305.txt.gz ยท Last modified: 2016/04/27 09:48 by rlunaro