I am not asking difference between them, my question is when we need to use "Union All" ?
A union is used for extracting rows using the conditions specified in the query while Union All is used for extracting all the rows from a set of two tables.
The UNION operator is used to combine the result-set of two or more SELECT statements.
The difference between Union and Union all is that Union all will not eliminate duplicate rows, instead it just pulls all the rows from all the tables fitting your query specifics and combines them into a table. A UNION statement effectively does a SELECT DISTINCT on the results set.
Both UNION and UNION ALL operators combine rows from result sets into a single result set. The UNION operator removes eliminate duplicate rows, whereas the UNION ALL operator does not. Because the UNION ALL operator does not remove duplicate rows, it runs faster than the UNION operator.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With