when to use union and when to use union all, and which is better in performance

Problem: when to use union and when to use union all, and which is better in performance

Solution: UNION, Starting SQL Server 2008 or higher.

Combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union. The UNION operation is different from using joins that combine columns from two tables.

The following are basic rules for combining the result sets of two queries by using UNION:

·         The number and the order of the columns must be the same in all queries.

·         The data types must be compatible.

UNION ALL is similar to union but it does not remove duplicate records.

There is also an overhead when using UNION as it is distinct records from the result set.

So, really we need to use union and union all based on the purpose and needs.

Example:

In this article I am going to show you how Union and Union all can show different results.

select  1, 2, 3,4

UNION ALL

select  1, 2, 3,4

select  1, 2, 3,4

UNION

select  1, 2, 3,4

OUTPUT:

Execution plans: