SQL SERVER – UNION ALL and UNION are Different Operation

I have previously written about the difference between UNION ALL and UNION multiple times over this blog but it seems like this question never gets old and I keep on getting the question again and again. Let us learn about the different operations about the unions.

• UNION ALL and ORDER BY – How to Order Table Separately While Using UNION ALL
• Difference Between Union vs. Union All – Optimal Performance Comparison
• Introduction and Example of UNION and UNION ALL
• Simple Puzzle Using Union and Union All
• Simple Puzzle Using Union and Union All – Answer
• Insert Multiple Records Using One Insert Statement – Use of UNION ALL
• Union vs. Union All – Which is better for performance?
• UNION ALL and ORDER BY – How to Order Table Separately While Using UNION ALL

Recently I wrote a blog post about how to Return Specific Row to at the Bottom of the Resultset – T-SQL Script where I demonstrated how to use the CASE statement in the ORDER BY clause, lots of people asked me if we can do this using UNION ALL clause. I followed up this blog post with Return Specific Row to at the Bottom of the Resultset – T-SQL Script – Part 2 where I demonstrated the same script with UNION ALL.

Now after the blog post, I got so many questions that why not use UNION instead of UNION ALL. Well, the answer is simple; it would not work. The matter of the fact, UNION will result will return totally different result because when UNION returns results it removes the redundant data and sorts the data.

Let us see run following two queries and observe how we are getting a different result when we use UNION and UNION ALL.

-- UNION ALL
USE AdventureWorks2012
GO
SELECT [ProductID], COUNT(*) CountofProductID
FROM [Sales].[SalesOrderDetail]
WHERE (ProductID BETWEEN 707 AND 716)
AND ProductID <> 715 AND ProductID <> 712
GROUP BY [ProductID]
UNION ALL
SELECT [ProductID], COUNT(*) CountofProductID
FROM [Sales].[SalesOrderDetail]
WHERE ProductID = 715
GROUP BY [ProductID]
UNION ALL
SELECT [ProductID], COUNT(*) CountofProductID
FROM [Sales].[SalesOrderDetail]
WHERE ProductID = 712
GROUP BY [ProductID]
GO
-- UNION
USE AdventureWorks2012
GO
SELECT [ProductID], COUNT(*) CountofProductID
FROM [Sales].[SalesOrderDetail]
WHERE (ProductID BETWEEN 707 AND 716)
AND ProductID <> 715 AND ProductID <> 712
GROUP BY [ProductID]
UNION 
SELECT [ProductID], COUNT(*) CountofProductID
FROM [Sales].[SalesOrderDetail]
WHERE ProductID = 715
GROUP BY [ProductID]
UNION 
SELECT [ProductID], COUNT(*) CountofProductID
FROM [Sales].[SalesOrderDetail]
WHERE ProductID = 712
GROUP BY [ProductID]
GO

You can see the result of the UNION and UNION ALL in the following result. With the use of the UNION, we will not get the same result. Honestly, I will use my first method where I used the CASE statement in the ORDER BY clause.

Reference: Pinal Dave (https://blog.sqlauthority.com)