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.

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 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 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 (http://blog.sqlauthority.com)

About these ads

One thought on “SQL SERVER – UNION ALL and UNION are Different Operation

  1. Hello Sir,

    I was working with debugging one query, (Recently migrated from SQL Server 2000 to SQL Server 2008) and query was working fine in SQL Server 2000.

    We are observing weird behavior for one table, where we are not able to perform UNION with same table but surprisingly union all is working fine. Can you give us a hint what might be the problem with server.

    Error message is saying 9100 contact administrator.

    Thanks,
    Santosh

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s