The Difference Between UNION and UNION ALL

Both UNION and UNION ALL concatenate the result sets of two different 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’.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

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.