Difference between union and union all

 
SQL>
SQL> create table x (c1 number);

Table created.

SQL>
SQL> create table y (c1 number);

Table created.

SQL>
SQL> Insert into x values (1);

1 row created.

SQL> Insert into x values (2);

1 row created.

SQL> Insert into x values (3);

1 row created.

SQL> Insert into x values (4);

1 row created.

SQL> Insert into x values (5);

1 row created.

SQL> Insert into x values (6);

1 row created.

SQL>
SQL>
SQL> Insert into y values (5);

1 row created.

SQL> Insert into y values (6);

1 row created.

SQL> Insert into y values (7);

1 row created.

SQL>
SQL> SELECT * FROM x
  2  UNION
  3  SELECT * FROM y ;
        C1
----------
         1
         2
         3
         4
         5
         6
         7

7 rows selected.

SQL>
SQL> SELECT * FROM x
  2  UNION ALL
  3  SELECT * FROM y ;
        C1
----------
         1
         2
         3
         4
         5
         6
         5
         6
         7

9 rows selected.

SQL>
SQL> drop table x;

Table dropped.

SQL> drop table y;

Table dropped.

SQL> --

Related examples in the same category

1.	Union all result set
2.	union all with like
3.	UNION ALL: same with UNION statement, except the duplicate rows are not filtered
4.	A UNION ALL query for a single column

Difference between union and union all : Union all « Result Set « Oracle PL / SQL

Related examples in the same category