Introduction to SQL Server UNION
SQL Server
UNION
is one of the set operations that allows you to combine results of two SELECT
statements into a single result set which includes all the rows that belongs to the SELECT
statements in the union.
The following illustrates the syntax of the SQL Server
UNION
:
The following are requirements for the queries in the syntax above:
- The number and the order of the columns must be the same in both queries.
- The data types of the corresponding columns must be the same or compatible.
The following Venn diagram illustrates how the result set of the T1 table unions with the result set of the T2 table:

UNION
vs. UNION ALL
By default, the
UNION
operator removes all duplicate rows from the result sets. However, if you want to retain the duplicate rows, you need to specify the ALL
keyword explicitly as shown below:
In other words, the
UNION
operator removes the duplicate rows while the UNION ALL
operator includes the duplicate rows in the final result set.
UNION
vs. JOIN
The join such as
INNER JOIN
or LEFT JOIN
combines columns from two tables while the UNION
combines rows from two queries.
In other words, join appends the result sets horizontally while union appends result set vertically.
The following picture illustrates the main difference between
UNION
and JOIN
:
SQL Server UNION
examples
See the following
staffs
and customers
tables from the sample database:

UNION
and UNION ALL
examples
The following example combines names of staffs and customers into a single list:

It returns 1,454 rows.
The
staffs
table has 10 rows and the customers table has 1,445 rows as shown in the following queries:
Because the result set of the union returns only 1,454 rows, it means that one duplicate row was removed.
To include the duplicate row, you use the
UNION ALL
as shown in the following query:
The query returns 1,455 rows as expected.
UNION
and ORDER BY
example
To sort the result set returned by the
UNION
operator, you place the ORDER BY
clause in the last query as follows:
For example, to sort the first names and last names of customers and staffs, you use the following query:
In this tutorial, you have learned how to use the SQL Server
UNION
to combines rows from multiple queries into a single result set.
0 Comments