UNION and UNION ALL are used to combine the result of two or more SELECT queries. Sometimes in our enterprise application, we need to Union two Select queries to combine the result based on the business requirement.

UNION: When we use UNION or UNION ALL command all the selected columns need to be of same data type. The major difference between UNION and UNION ALL is that UNION returns the distinct records while UNION ALL returns the all rows returned from the respect SELECT queries. UNION internally does a SELECT DISTINCT on the returned result set which we will be seeing later.

Let’s take an example of our two tables i.e. ORDER and Orders

So now I want to retrieve CustomerId, StoreId, GrossTotal, and TotalOrderDiscount from both the table and combine the same into a single result set as shown below:

SELECT CUSTOMERID, STOREID, GROSSTOTAL, TOTALORDERDISCOUNT FROM [DBO].[ORDER]
UNION
SELECT CUSTOMERID, STOREID, GROSSTOTAL, TOTALORDERDISCOUNT FROM [DBO].[ORDERS]

Output:

Result Set

Now let’s check the Execution plan for the same and see how SQL server executes UNION command.

SQL SERVER performing DISTINCT Sort which is an expensive one Cost 64% time

Now if we use UNION ALL it will return all the records set resulted from each query as shown below:

SELECT CUSTOMERID, STOREID, GROSSTOTAL, TOTALORDERDISCOUNT FROM [DBO].[ORDER]
UNION ALL
SELECT CUSTOMERID, STOREID, GROSSTOTAL, TOTALORDERDISCOUNT FROM [DBO].[ORDERS]

Output:

You can see UNION ALL contains the Duplicate records. Let’s check the SQL plan for the same as shown below:

NO distinct overhead is performed in UNION ALL

We can clearly see that there is no distinct operation being performed in UNION ALL. So UNION ALL is quite fast as compared to UNION because there is no Distinct overhead.