UNION Operator in MS SQL Server
Union means joining two or more data sets into a single set. In SQL Server, the UNION operator 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:
The syntax of the UNION operator in MS SQL Server is:
SELECT column1, column2, ... FROM table1
UNION
SELECT column1, column2, ... FROM table2;
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
Let’s look at some examples of the UNION operator in MS SQL Server
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.
Union and Union All in MS SQL Server
In MS SQL Server, the UNION and UNION ALL operators are used to combine the result sets of two or more SELECT statements into a single result set. This allows you to retrieve data from multiple tables or views and combine it into a single dataset.
The primary difference between UNION
and UNION ALL
is that UNION
removes duplicate rows from the result set, while UNION ALL
includes all rows, including duplicates.