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)
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
Employee table data:
Customer table data:
UNION Example (It removes all duplicate records):
UNION ALL Example (It just concatenate records, not eliminate duplicates, so it is faster than UNION):