Union and Union All in MS SQL Server

1. Union :
Union means joining two or more data sets into a single set. In SQL Server, Union is used to combine two queries into a single result set using the select statements. Union extracts all the rows that are described in the query.

Syntax –

query1 UNION query2

Union holds a few conditions before being used in a query. One such condition is that the rows to be extracted must come from the same columns from the tables.

Example –

select name, rollnumber 
from student 
UNION 
select name, rollnumber 
from marks


Table – Student



Name Rollnumber Course
Maya 111 CSE
Riya 112 Mech



Table – Marks

Name Rollnumber Marks
Maya 111 8.9
Riya 112 7.8

Output –

Name Rollnumber Name Rollnumber
Maya 111 Maya 111
Riya 112 Riya 112

Two different tables are being used here for extraction of rows yet the column specified for extraction is the same for both. An error occurs if different columns are being used. The data type specified also must be the same for both the queries.



2. Union All :
A union is used for extracting rows using the conditions specified in the query while Union All is used for extracting all the rows from a set of two tables.

Syntax –

query1 UNION ALL query2

The same conditions are applicable to Union All. The only difference between Union and Union All is that Union extracts the rows that are being specified in the query while Union All extracts all the rows including the duplicates (repeated values) from both the queries.

Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.

My Personal Notes arrow_drop_up


If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.