Difference between UNION and UNION ALL
Both Union and Union all statements are use to merge data from multiple queries which has same number of columns. Also, the data types of the column should be same.
We will look into the difference between both operators using an example. We have the following two tables
Union: Union merge data from two or more queries and returns a sorted unique result set. Since this operator provides distinct result set it takes more time than Union All.
select * from EmpDetails union select * from custdetails
Union All: Union all merge data from two or more queries and returns non-unique result set. Since this operator does not perform distinct on the result set, it's performance is much faster than Union operator
select * from EmpDetails union all select * from custdetails
Following are the constraints that are associated with UNION and UNION ALL operator:
- The tables should contains the same number of columns
- Columns in the tables must be of the same data type
- Order by clause should be applied to the final result set
- Column name for the result set would be of the first query mentioned
See Also: Different Types of Joins in SQL , Difference between CTE and View