Differences

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

--- oracle:meaningoffulljoin [2016/04/27 07:48] – created rlunaro
+++ oracle:meaningoffulljoin [2022/12/02 21:02] (current) – external edit 127.0.0.1
@@ Line 5: / Line 5: @@
 Let's say we have two tables:
-<code>
+<code sql>
 create table test1
 (field  varchar2(100));
@@ Line 16: / Line 16: @@
 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|