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

CREATE TABLE Student_A
(
 ID INT IDENTITY(1,1),
 NAME VARCHAR(10),
 MARKS INT
)

Inserting the data into Student_A table

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

Create Student_B Table

CREATE TABLE Student_B
(
 ID INT IDENTITY(1,1),
 NAME VARCHAR(10),
 MARKS INT
)

Inserting the data into Student_B table

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

Selecting two table data

SELECT * FROM Student_A

SELECT * FROM Student_B

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



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.

SELECT * FROM Student_A
Union All
SELECT * FROM Student_B

 

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.

 

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.

SELECT * FROM Student_A
Union All
SELECT * FROM Student_B

Run the above query.

Press (Ctrl+L) to check the 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%.

SELECT * FROM Student_A
Union
SELECT * FROM Student_B

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

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:

SELECT ID,NAME FROM Student_A
Union All
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:

 

SELECT NAME,ID FROM Student_A
Union All
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