The Difference Between UNION and UNION ALL

Both UNION and UNION ALL concatenate the result sets of two separate SQLs. They differ in the way they handle duplicates.

UNION performs a DISTINCT on the result set, eliminating any duplicate rows.

UNION ALL does not remove duplicates, and is therefore faster than UNION.

UNION vs UNION ALL Examples

Consider two tables that store colors, one has shades close to red and the other shades close to green. They overlap on the values ‘yellow’ and ‘tan’.

SQL> select *
  2  from colors1;

NAME
------------
red
orange
brown
tangerine
yellow
tan

6 rows selected.

SQL> select *
  2  from colors2;

NAME
------------
green
olive
tan
emerald
yellow

Result with UNION contains distinct rows:

SQL> select *
  2  from colors1
  3  union
  4  select *
  5  from colors2;

NAME
------------
brown
emerald
green
olive
orange
red
tan
tangerine
yellow

9 rows selected.

Result with UNION ALL contains duplicates:

SQL> select *
  2  from colors1
  3  union all
  4  select *
  5  from colors2;

NAME
------------
red
orange
brown
tangerine
yellow
tan
green
olive
tan
emerald
yellow

11 rows selected.

Which should you use – UNION or UNION ALL?

Use UNION only if you absolutely must get rid of duplicates in the result. Otherwise, use UNION ALL which works faster.