How to union in sql server

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:
SQL Server UNION Venn Diagram


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.


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:
SQL Server UNION example
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.

Post a Comment