Post
Ask Question

Difference Between Union and Union All in SQL Server

UNION in SQL Server

 
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
  1. Select Col1, Col2, Clo3 from Table1  
  2. UNION  
  3. Select Col1, Col2, Clo3 from Table2  
Example
 
Consider the following example. I have two tables that have Id, name and phone number columns. 
  1. DECLARE @Table1 AS Table (ID INT, Name VARCHAR(10), PhoneNumber VARCHAR(12))  
  2. DECLARE @Table2 AS Table (ID INT, Name VARCHAR(10), PhoneNumber VARCHAR(12))  
  3.   
  4. INSERT INTO @Table1 VALUES(1,'Tejas', '88996655')  
  5. INSERT INTO @Table1 VALUES(2,'Jignesh', '99986655')  
  6.   
  7. INSERT INTO @Table2 VALUES(1,'Rakesh', '88446655')  
  8. INSERT INTO @Table2 VALUES(2,'Purvi', '99986655')  
  9.   
  10. SELECT * FROM @Table1  
  11. UNION  
  12. SELECT * FROM @Table2  
Output
 
UNION in SQL Server
 

UNION ALL in SQL Server

 
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).
  1. DECLARE @Table1 AS Table (ID INT, Name VARCHAR(10), PhoneNumber VARCHAR(12))  
  2. DECLARE @Table2 AS Table (ID INT, Name VARCHAR(10), PhoneNumber VARCHAR(12))  
  3.   
  4. INSERT INTO @Table1 VALUES(1,'Tejas', '88996655')  
  5. INSERT INTO @Table1 VALUES(2,'Jignesh', '99986655')  
  6.   
  7. INSERT INTO @Table2 VALUES(1,'Rakesh', '88446655')  
  8. INSERT INTO @Table2 VALUES(2,'Jignesh', '99986655')  
  9.   
  10. SELECT * FROM @Table1  
  11. UNION  
  12. SELECT * FROM @Table2  
Output
 
UNION in SQL Server
 
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.
  1. SELECT * FROM @Table1  
  2. UNION ALL  
  3. SELECT * FROM @Table2  
Output
 
UNION ALL in SQL Server
 
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.
 
 
 

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.
  1. DECLARE @Table3 AS Table (ID INT, Comment TEXT)  
  2. DECLARE @Table4 AS Table (ID INT, Comment TEXT)  
  3.   
  4. INSERT INTO @Table3 VALUES(1,'This is Union test by Tejas')  
  5. INSERT INTO @Table3 VALUES(2,'This is Union test by Jignesh')  
  6.   
  7. INSERT INTO @Table4 VALUES(1,'This is Union test by Rakesh')  
  8. INSERT INTO @Table4 VALUES(2,'This is Union test by Jignesh')  
  9.   
  10. SELECT * FROM @Table3  
  11. UNION   
  12. SELECT * FROM @Table4  
Output: SQL server throws a runtime exception.
 
 
With the “UNION ALL” operator, it returns all the rows of both tables.
  1. SELECT * FROM @Table3  
  2. UNION ALL  
  3. SELECT * FROM @Table4  
Output
 
 

Union Vs Union ALL

 
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.


Similar Articles