What does UNION do? What is the difference between UNION and UNION ALL?

Question

sql

Answer 1

UNION combines multiple datasets into a single dataset, and removes any existing duplicates.
UNION ALL combines multiple datasets into one dataset, but does not remove duplicate rows.

Answer 2

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 3

That is also explained in a lesson below:
https://www.sololearn.com/learn/SQL/1867/

Answer 4

Union removes duplicate records but union all does not.

Answer 5

UNION ALL is faster than UNION, as it does not perform the duplicate removal operation over the data set.
Union removes existing any existing duplicate while Union All does not remove duplicate.

Answer 6

union is merge result sets of two or more SELECT statements，union all is allow duplicate values