Difference between UNION and UNION ALL

                                    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

Empdetails:

Custdetails:

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.

Query:

               select * from EmpDetails union  select * from custdetails   
Output:

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

Query:

            select * from EmpDetails union all select * from custdetails 

Output:

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