Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What is the use of "Union all"?

Tags:

sql

I am not asking difference between them, my question is when we need to use "Union All" ?

like image 249
Jeevan Bhatt Avatar asked Oct 11 '10 06:10

Jeevan Bhatt


People also ask

What is the use of UNION and UNION all in SQL?

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.

What is the use of union in SQL?

The UNION operator is used to combine the result-set of two or more SELECT statements.

Which is better UNION or UNION all?

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.

Why UNION all is faster than UNION?

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.


1 Answers

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.

like image 197
Andrew Barber Avatar answered Oct 08 '22 01:10

Andrew Barber