I am not asking difference between them, my question is when we need to use "Union All" ?
+6
A:
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.
Andrew Barber
2010-10-11 06:59:19
+1 for specifying a possible scenario of UNION ALL
Christian
2010-10-11 07:02:52
+1 for mentioning the performance - this is almost critical, one needs to understand the implications of UNION vs UNION ALL for bigger tables, where UNION will perform orders of magnitude slower then UNION ALL for large resultsets (since removing duplicates in most cases mean building temporary index; for smaller resultsets this will not be so expensive, the size of the resultset is the number of rows *returned*)
Unreason
2010-10-11 07:10:11
+1
A:
When we do not need to exclude duplicated rows from recordset
Michael Pakhantsov
2010-10-11 06:59:25
+6
A:
- 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
gbn
2010-10-11 06:59:32
If you were doing lots of JOINs and had redundant rows, would you use GROUP BY instead of UNION? And what's the difference between UNION and DISTINCT?
meder
2010-10-11 07:05:58
@meder: GROUP BY to remove duplicates is abuse: it's for aggregates. DISTINCT is per SELECT. That is, I could have SELECT DISTINCT.. UNION ALL SELECT ... if needed. This means de-duplicate 1st clause but take all rows on UNION.
gbn
2010-10-11 07:25:58
+2
A:
When you need to use them depends on your requirements. The difference between UNION and UNION ALL is that UNION ALL doesn't remove duplicate rows.
mastoj
2010-10-11 07:00:24
+2
A:
Union all is used to join multiple data set into a single data set but it does not remove duplicates
Pramodh
2010-10-11 07:01:41
+1
A:
You use UNION ALL to concatenate (append) two result sets into a single result set when they have the same columns (number and type).
Mark Byers
2010-10-11 07:02:59
+2
A:
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.
codaddict
2010-10-11 07:06:14
A:
Why Union or Union all ?
Ans: If you are looking some data from two or more different tables (I mean, by relationally) then you can use it.
Vikas
2010-10-11 07:06:55
+1
A:
Because the result will be a table, and every table should have a key, and UNION ensures unique rows, you should in theory always use UNION. However, because UNION ALL does not ensure unique rows, UNION ALL can perform better and therefore may be preferred in practise if you can be sure that both tables already contain unique rows.
The same logic applies to the choice between SELECT DISTINCT and SELECT ALL, BTW.
onedaywhen
2010-10-11 07:46:42