What is the difference between UNION and UNION ALL

Question

What is the difference between UNION and UNION ALL?

Answer 1

UNION removes duplicate records (where all columns in the results are the same), UNION ALL does not.

There is a performance hit when using UNION instead of UNION ALL, since the database server must do additional work to remove the duplicate rows, but usually you do not want the duplicates (especially when developing reports).

UNION Example:

SELECT 'foo' AS bar UNION SELECT 'foo' AS bar

Result:

+-----+
| bar |
+-----+
| foo |
+-----+
1 row in set (0.00 sec)

UNION ALL example:

SELECT 'foo' AS bar UNION ALL SELECT 'foo' AS bar

Result:

+-----+
| bar |
+-----+
| foo |
| foo |
+-----+
2 rows in set (0.00 sec)

Answer 2

In ORACLE: UNION does not support BLOB (or CLOB) column types, UNION ALL does.

Answer 3

UNION removes duplicates, whereas UNION ALL does not.

In order to remove duplicates the result set must be sorted, and this may have an impact on the performance of the UNION, depending on the volume of data being sorted, and the settings of various RDBMS parameters ( For Oracle PGA_AGGREGATE_TARGET with WORKAREA_SIZE_POLICY=AUTO or SORT_AREA_SIZE and SOR_AREA_RETAINED_SIZE if WORKAREA_SIZE_POLICY=MANUAL ).

Basically, the sort is faster if it can be carried out in memory, but the same caveat about the volume of data applies.

Of course, if you need data returned without duplicates then you must use UNION, depending on the source of your data.

I would have commented on the first post to qualify the "is much less performant" comment, but have insufficient reputation (points) to do so.

Answer 4

Both UNION and UNION ALL concatenate the result 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 it therefore faster than UNION.

Note: While using this commands all selected columns need to be of the same data type.

Example: If we have two tables, 1) Employee and 2) Customer

1. Employee table data:

2. Customer table data:

3. UNION Example (It removes all duplicate records):

4. UNION ALL Example (It just concatenate records, not eliminate duplicates, so it is faster than UNION):

Answer 5

Important! Difference between Oracle and Mysql: Let's say that t1 t2 don't have duplicate rows between them but they have duplicate rows individual. Example: t1 has sales from 2017 and t2 from 2018

SELECT T1.YEAR, T1.PRODUCT FROM T1

UNION ALL

SELECT T2.YEAR, T2.PRODUCT FROM T2

In ORACLE UNION ALL fetches all rows from both tables. The same will occur in MySQL.

However:

SELECT T1.YEAR, T1.PRODUCT FROM T1

UNION

SELECT T2.YEAR, T2.PRODUCT FROM T2

In ORACLE, UNION fetches all rows from both tables because there are no duplicate values between t1 and t2. On the other hand in MySQL the resultset will have fewer rows because there will be duplicate rows within table t1 and also within table t2!

Answer 6

Just to add my two cents to the discussion here: one could understand the UNION operator as a pure, SET-oriented UNION - e.g. set A={2,4,6,8}, set B={1,2,3,4}, A UNION B = {1,2,3,4,6,8}

When dealing with sets, you would not want numbers 2 and 4 appearing twice, as an element either is or is not in a set.

In the world of SQL, though, you might want to see all the elements from the two sets together in one "bag" {2,4,6,8,1,2,3,4}. And for this purpose T-SQL offers the operator UNION ALL.