Union and Union All in MS SQL Server
Last Updated :
17 Jun, 2024
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.
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 ALL in MS SQL Server
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
The syntax to use UNION ALL in MS SQL Server is:
SELECT column1, column2, ...
FROM table1
UNION ALL
SELECT column1, column2, ...
FROM table2;
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.
Example
Let’s look at some examples of the UNION ALL operator in MS SQL Server
SELECT Name, Rollnumber, 'Student' AS Type
FROM Student
UNION ALL
SELECT Name, Rollnumber, 'Marks' AS Type
FROM Marks;
Output:
| Name |
Rollnumber |
Type |
| Maya |
111 |
Student |
| Riya |
112 |
Student |
| Maya |
111 |
Marks |
| Riya |
112 |
Marks |
As you can see, UNION ALL operator also keeps duplicate values in it’s result set.
Similar Reads
UNION vs UNION ALL in SQL
SQL UNION and UNION ALL operators are used to concatenate results of multiple SELECT statements. However, they are different from each other. One key difference between UNION and UNION ALL in SQL is that the UNION command removes duplicates from the final results set, whereas the UNION ALL command a
6 min read
SQL Full Outer Join Using Union Clause
In this article, we will discuss the overview of SQL, and our main focus will be on how to perform Full Outer Join Using Union Clause in SQL. Let's discuss it one by one. Overview :To manage a relational database, SQL is a Structured Query Language to perform operations like creating, maintaining da
3 min read
Joins in MS SQL Server
A database comprises tables and each table in case of RDBMS is called a relation. Let us consider a sample database named University and it has two tables named Student and Marks. If a user wants to transfer a certain set of rows, insert into select statement is used along with the query. But if a u
2 min read
Find Duplicates in MS SQL Server
Finding duplicate values in a database is a common task when managing data integrity. In SQL, several methods can be employed to identify and handle duplicate entries. In this article, We will explore two effective techniques for locating duplicates using SQL queries: the GROUP BY clause and the ROW
4 min read
SQLite Union All Operator
SQLite is a server-less database engine written in C programming language. It is developed by D. Richard Hipp in the year 2000. The main moto for developing SQLite is escaping complex database engines like MYSQL etc. It has become one of the most popular database engines as we use it in Television,
6 min read
Combine Rows into String in SQL Server
To combine rows into a string in SQL Server, use the SQL COALESCE() function or the SQL CONCAT() function. COALESCE() function in SQL is used to handle null values. It returns non-null values from a row, which can be concatenated into string. CONCAT() function in SQL is used to concatenate two or mo
2 min read
Full join and Inner join in MS SQL Server
Full Join Full join selects all the rows from the left and right tables along with the matching rows as well. If there are no matching rows, it will be displayed as NULL. Syntax: select select_list from table1 full join table2 on join _predicate (OR) select table1.*, table2.* from table1 full join t
2 min read
How to Join to First Row in SQL Server
Joining the first row in SQL Server can be a common requirement in various scenarios, such as when we need to retrieve specific data associated with the first occurrence of a particular group or when you want to fetch additional details related to the first record in a result set. In this article, W
4 min read
Pivot and Unpivot in SQL
In SQL, PIVOT and UNPIVOT are powerful operations used to transform data and make it more readable, efficient, and manageable. These operations allow us to manipulate tables by switching between rows and columns, which can be crucial for summarizing data, reporting, and data analysis. Understanding
4 min read
PL/SQL UNION ALL Operator
In PL/SQL, the UNION ALL operator is a powerful tool that allows us to combine the results of two or more SELECT queries into a single result set. Unlike the UNION operator, which eliminates duplicate rows, UNION ALL includes all rows, including duplicates. This makes it faster and more efficient wh
4 min read