UNION Vs UNION ALL – Sql Server

The difference between Union and Union All if one of the things which not only confuse amateurs but also veterans.Although they both appears to be similar as both are used to club together the data from different sets but hey are quite dissimilar based on result and the performance.

UNION: UNION returns the unique data from different sets joined using the UNION. It removes duplicate rows.

UNION ALL: UNION ALL returns whole data from different sets . It don’t removes duplicates

Example: Create two tables CSS and JavaScript by using the sql server queries listed below

CREATE TABLE CSS
(ID INT,
firstName VARCHAR(50)
)

CREATE TABLE JavaScript
(ID INT,
firstName VARCHAR(50)
)

INSERT INTO CSS(ID, firstName) VALUES(1,’Bob’)
INSERT INTO CSS(ID, firstName) VALUES(2,’Smith’)
INSERT INTO CSS(ID, firstName) VALUES(3,’Randy’)
INSERT INTO CSS(ID, firstName) VALUES(4,’Nate’)
INSERT INTO JavaScript(ID, firstName) VALUES(1,’Bob’)
INSERT INTO JavaScript(ID, firstName) VALUES(2,’Patrice’)
INSERT INTO JavaScript(ID, firstName) VALUES(3,’Nate’)
INSERT INTO JavaScript(ID, firstName) VALUES(4,’Alex’)
INSERT INTO JavaScript(ID, firstName) VALUES(5,’Rammy’)

UNION:
SELECT * FROM CSS
UNION
SELECT * FROM JavaScript

The result would be:

Union in SQL Server - As I Had It

Union in SQL Server


The result returned has 8 rows and none of the rows is duplicate

UNION ALL:
SELECT * FROM CSS
UNION ALL
SELECT * FROM JavaScript

The result would be:

The result returned has rows and it has duplicate row(row with firstName as Bob)

Union All in SQL Server - As I Had It

Union All in SQL Server


Notes:
1. For Union or Union All, it is mandatory that all the involved tables have exactly same number of columns. The tables CSS and JavaScript used above have 2 columns each.
2. The datatypes of the columns of involved tables should be same(in the order of the columns), otherwise the implicit data type conversions will cause queries to return error. To handle the error, we will have to do explicit data type conversions

SELECT COL1, CONVERT(VARCHAR(23), COL2, 121) AS COL2 FROM T1
UNION ALL
SELECT COL1, CAST(COL2 AS VARCHAR(10)) FROM T2

From performance viewpoint, UNION ALL is superior to UNION. It is because UNION has to remove the duplicate rows. So one should use UNION only at places where input tables contain duplicate rows and the programmer is not supposed to display duplicate data. It is used by programmers because they do not understand the difference between these two operators

The difference in performance can be seen by having at look at the sql server execution plan of the queries used above(for tables named CSS and JavaScript):

SQL Server execution plan Union Vs Union All

Union Vs Union All execution plan


Here we see that UNION is quite costly as compared to UNION ALL because UNION has taken up 73% cost where as UNION ALL consumed only 23% cost. The extra burden in case of UNION is due to sorting the data and removing duplicates from the result set.
So we should always try that we use UNION only when its absolutely required. Otherwise UNION ALL is a better option, it gives faster results.

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.