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>