What is the use of "Union all"?

You would use UNION ALL when you really do need the multiple 'copies' of rows that would otherwise be removed when using UNION. It can also be faster on the query end, since the DB engine does not need to determine what are duplicates between the result sets.

• UNION will remove duplicates
• UNION ALL does not remove duplicates

Example

SELECT 1 AS foo
UNION
SELECT 1 AS foo

= one row

SELECT 1 AS foo
UNION ALL
SELECT 1 AS foo

= two rows

An example will make it clear:

mysql> select * from tmp1;
+------+
| a    |
+------+
| foo1 |
| foo2 |
+------+
2 rows in set (0.00 sec)

mysql> select * from tmp2;
+------+
| a    |
+------+
| foo2 |
| foo3 |
| foo4 |
+------+
3 rows in set (0.00 sec)

mysql> select * from tmp1 union select * from tmp2;
+------+
| a    |
+------+
| foo1 |
| foo2 |   # DUPLICATES REMOVED.
| foo3 |
| foo4 |
+------+
4 rows in set (0.00 sec)

mysql> select * from tmp1 union all select * from tmp2;
+------+
| a    |
+------+
| foo1 |
| foo2 |
| foo2 |    # DUPLICATES NOT REMOVED.
| foo3 |
| foo4 |
+------+
5 rows in set (0.00 sec)

Coming to the question of When to use UNION ALL?

If you don't care that result set has duplicate rows or if you know there won't be any duplicates then use UNION ALL instead of UNION.