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:

1 2 3

query_1 UNION query_2

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:

1 2 3

query_1 UNION ALL query_2

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:

1 2 3 4 5 6 7 8 9 10 11

SELECT     first_name,     last_name FROM     sales.staffs UNION SELECT     first_name,     last_name FROM     sales.customers;

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:

1 2 3 4 5 6 7 8 9 10 11

SELECT     COUNT (*) FROM     sales.staffs; -- 10         SELECT     COUNT (*) FROM     sales.customers; -- 1454

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:

1 2 3 4 5 6 7 8 9 10 11

SELECT     first_name,     last_name FROM     sales.staffs UNION ALL SELECT     first_name,     last_name FROM     sales.customers;

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:

1 2 3 4 5 6 7 8 9 10 11

SELECT     select_list FROM     table_1 UNION SELECT     select_list FROM     table_2 ORDER BY     order_list;

For example, to sort the first names and last names of customers and staffs, you use the following query:

1 2 3 4 5 6 7 8 9 10 11 12 13 14

SELECT     first_name,     last_name FROM     sales.staffs UNION ALL SELECT     first_name,     last_name FROM     sales.customers ORDER BY     first_name,     last_name;

In this tutorial, you have learned how to use the SQL Server UNION to combines rows from multiple queries into a single result set.