What is the difference between UNION and UNION ALL?
sql, union, union-all
Solution
`UNION` removes duplicate rows (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).
To identify duplicates, rows must be comparable types as well as compatible types. This will depend on the SQL system. For example the system may truncate all long text columns to make short text columns for comparison (MS Jet), or may refuse to compare binary columns (ORACLE)
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)
Problem
What is the difference between `UNION` and `UNION ALL`?