Union and Union All in SQL Server

Union AND Union All

Union and Union All in SQL Server combines the result sets of two or more select statements.

I have created the two tables with the following data:

Create Student_A Table

  1. CREATE TABLE Student_A  
  2. (   
  3.  ID INT IDENTITY(1,1),  
  4.  NAME VARCHAR(10),  
  5.  MARKS INT  

Inserting the data into Student_A table

  1. INSERT INTO Student_A(NAME,MARKS) VALUES('Dave',500)  
  2. INSERT INTO Student_A(NAME,MARKS) VALUES('Rakesh',400)  
  3. INSERT INTO Student_A(NAME,MARKS) VALUES('Ramu',300) 

Create Student_B Table

  1. CREATE TABLE Student_B  
  2. (   
  3.  ID INT IDENTITY(1,1),  
  4.  NAME VARCHAR(10),  
  5.  MARKS INT  

Inserting the data into Student_B table

  1. INSERT INTO Student_A(NAME,MARKS) VALUES('Dave',500)  
  2. INSERT INTO Student_B(NAME,MARKS) VALUES('Raju',300)  
  3. INSERT INTO Student_B(NAME,MARKS) VALUES('Nani',350) 

Selecting two table data

  1. SELECT * FROM Student_A  
  2.   
  3. SELECT * FROM Student_B 

When we run the preceding statements the result is two result sets because these are two separate select statements.

Table

But we want both results in a single result set. That can done using the Union or Union All operators.

Union All

It combines both of the result sets but it does not select the distinct data and also it does not sort the data.

  1. SELECT * FROM Student_A  
  2. Union All  
  3. SELECT * FROM Student_B 

Union All 

When we observe the preceding result set it combines the Student_A data with Student_B data. The Union All operator does not distinct the data and the data is also not sorted.

Union

It combines both of the result sets but it selects the distinct data and also it sorts the data.
 
Union

Performance

Union All is faster than the Union because Union All does not select the distinct rows and also the data is not sorted.

We can check the Union and Union All operators with a performance test using the execution query plan.
  1. SELECT * FROM Student_A  
  2. Union All  
  3. SELECT * FROM Student_B 

Run the above query.

Press (Ctrl+L) to check the execution query plan.

execution query plan
It directly combines both result sets. It does not sort the data and does not select the distinct data because the cost will be 0%.
  1. SELECT * FROM Student_A  
  2. Union  
  3. SELECT * FROM Student_B 

Press (Ctrl+L) To check the execution query plan.

execution

Union All will be faster than Union

Note: Union and Union All operators in select statement number of columns of all selects statements should be the same and the order of the types should be the same.

Senario 1:
  1. SELECT ID,NAME FROM Student_A  
  2. Union All  
  3. SELECT ID,NAME,MARKS FROM Student_B 

In the preceding the first select satement selects the ID and Name colums from the Student_A table (2 colums). The second select statement selects the ID, Name and MARKS from the Student_B table (3 colums). When we run the above query the command will fail beacause the number of colums do not match for both tables.

Msg 205, Level 16, State 1, Line 3
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Scenario 2:

 

  1. SELECT NAME,ID FROM Student_A  
  2. Union All  
  3. SELECT ID,NAME FROM Student_B 

The preceding query will fail because of the order of the data types should match.

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'Dave' to data type int.

Differences between Union and Union All

 

Union Union All
No duplicates when combing the two or more result set Duplicate data will be available when combing the two or more result sets
Data in sorted order Data is not sorted
Performance wise Union will not be faster than Union All because it selects the distinct data and then sorts the data order Performance wise Union All will be faster than Union