Difference Between Union and Union All in SQL Server

UNION

UNION combines the result set of two or more queries into a single result set. This result set includes all the rows that belong to all queries in the UNION.

The following points need to be considered when using the UNION operator:

• The number of columns and sequence of columns must be the same in all queries
• The data types must be compatible.

Syntax

Select Col1, Col2, Clo3 from Table1
UNION
Select Col1, Col2, Clo3 from Table2

 

Example

Consider the following example. I have two tables that have Id, name and phone number columns.

 

DECLARE @Table1 AS Table (ID INT, Name VARCHAR(10), PhoneNumber VARCHAR(12))
DECLARE @Table2 AS Table (ID INT, Name VARCHAR(10), PhoneNumber VARCHAR(12))

INSERT INTO @Table1 VALUES(1,'Tejas', '88996655')
INSERT INTO @Table1 VALUES(2,'Jignesh', '99986655')

INSERT INTO @Table2 VALUES(1,'Rakesh', '88446655')
INSERT INTO @Table2 VALUES(2,'Purvi', '99986655')

SELECT * FROM @Table1
UNION
SELECT * FROM @Table2

 

Output

UNION ALL

UNION ALL is very similar to UNION. It also includes duplicate rows in the result set.

Behavior of UNION / UNION ALL operator with Duplicate Data

UNION always returns distinct rows. In other words it eliminates duplicate rows from the result set.

Example

Consider the following example. I have two tables and both has two rows. One row is common to both tables. When we union these two tables it returns three rows (removes duplicate rows from the result set).

 

DECLARE @Table1 AS Table (ID INT, Name VARCHAR(10), PhoneNumber VARCHAR(12))
DECLARE @Table2 AS Table (ID INT, Name VARCHAR(10), PhoneNumber VARCHAR(12))

INSERT INTO @Table1 VALUES(1,'Tejas', '88996655')
INSERT INTO @Table1 VALUES(2,'Jignesh', '99986655')

INSERT INTO @Table2 VALUES(1,'Rakesh', '88446655')
INSERT INTO @Table2 VALUES(2,'Jignesh', '99986655')

SELECT * FROM @Table1
UNION
SELECT * FROM @Table2

 

Output

A UNION operator does a DISTINCT on the result set, SQL Server automatically does a distinct sort operation on the result set. Consider the following execution plan, in this execution plan we can see that the distinct sort is taking 63% of the time of the actual execution time.

“UNION ALL” always returns all the rows of the result set. It does not remove duplicate rows. Consider the preceding example with the UNION ALL operator.

 

SELECT * FROM @Table1
UNION ALL
SELECT * FROM @Table2

 

Output

When we look into the execution plan of a UNION ALL Query, it does not include a distinct sort. UNION must perform a distinct sort operation to remove the duplicate value from the result set that makes a UNION ALL faster than the UNION.

Observation: UNION and UNION ALL with TEXT Data Type

When we use a UNION for the TEXT type columns, SQL Server throws a runtime error. This error is not generated when we use the “UNION ALL” operator.

Example

Consider the following example. I have two tables and both tables have columns with the data type TEXT. Now I am trying to UNION the result set.

 

DECLARE @Table3 AS Table (ID INT, Comment TEXT)
DECLARE @Table4 AS Table (ID INT, Comment TEXT)

INSERT INTO @Table3 VALUES(1,'This is Union test by Tejas')
INSERT INTO @Table3 VALUES(2,'This is Union test by Jignesh')

INSERT INTO @Table4 VALUES(1,'This is Union test by Rakesh')
INSERT INTO @Table4 VALUES(2,'This is Union test by Jignesh')

SELECT * FROM @Table3
UNION
SELECT * FROM @Table4

 

Output: SQL server throws a runtime exception.

With the “UNION ALL” operator, it returns all the rows of both tables.

 

SELECT * FROM @Table3
UNION ALL
SELECT * FROM @Table4

 

Output

Summary

UNION	UNION ALL
UNION removes duplicate rows.	“UNION ALL” does not remove the duplicate row. It returns all from all queries.
UNION uses a distinct sort	“UNION ALL” does not use a distinct sort, so the performance of “UNION ALL” is slightly higher than “UNION”.
UNION cannot work with a column that has a TEXT data type.	UNION ALL can work with all data type columns.

Each SELECT statement within the UNION / UNION ALL must have the same number of columns and the columns must have similar or compatible data types. They must be in the same order.

If the column sizes of the two tables are different then the result set has a column type that is the larger of two columns. For example, if SELECT ... UNION has CHAR (5) and CHAR (10) columns then it displays the output data of both of the columns as CHAR (10).

If the columns across the table have different column names then in general, the column names of the first query are used in a final result set.