Monday, April 02, 2012

UNION v UNION ALL

This was tested on an Oracle 11 database. A UNION ALL will merge 2 result sets. A UNION does the same then removes duplicate values:

SQL> create table union_test1 (one_col number)
  2  /
 
Table created.
 
SQL> insert into union_test1 values (1)
  2  /
 
1 row created.
 
SQL> insert into union_test1 values (2)
  2  /
 
1 row created.
 
SQL> insert into union_test1
  2  select * from union_test1
  3  /
 
2 rows created.
 
SQL> select * from union_test1
  2  /
 
   ONE_COL
----------
         1
         2
         1
         2
 
SQL> create table union_test2 (one_col number)
  2  /
 
Table created.
 
SQL> insert into union_test2 values (2)
  2  /
 
1 row created.
 
SQL> insert into union_test2 values (3)
  2  /
 
1 row created.
 
SQL> insert into union_test2
  2  select * from union_test2
  3  /
 
2 rows created.
 
SQL> select * from union_test2
  2  /
 
   ONE_COL
----------
         2
         3
         2
         3
 
SQL> select * from union_test1
  2  union all
  3  select * from union_test2
  4  /
 
   ONE_COL
----------
         1
         2
         1
         2
         2
         3
         2
         3
 
8 rows selected.
 
SQL> select * from union_test1
  2  union
  3  select * from union_test2
  4  /
 
   ONE_COL
----------
         1
         2
         3
 
SQL>

No comments: