SQL UNION

Summary: This tutorial shows you how to use the SQL UNION to combine two or more result sets from multiple queries and explains the difference between UNION and UNION ALL.

Introduction to SQL UNION operator

The UNION operator combines result sets of two or more SELECT statements into a single result set. The following statement illustrates how to use the UNION operator to combine result sets of two queries:

SELECT 
    column1, column2
FROM
    table1 
UNION [ALL]
SELECT 
    column3, column4
FROM
    table2;

To use the UNION operator, you write the dividual SELECT statements and join them by the keyword UNION.

The columns returned by the SELECT statements must have the same or convertible data type, size, and be in the same order.

The database system processes the query by executing two SELECT statements first. Then, it combines two individual result sets into one and eliminates duplicate rows. To eliminate the duplicate rows, the database system sorts the combined result set by every column and scans it for the matching rows located next to one another.

To retain the duplicate rows in the result set, you use the UNION ALL operator.

Suppose, we have two result sets A(1,2) and B(2,3). The following picture illustrates A UNION B:

And the following picture illustrates A UNION ALL B

The union is different from the join in that the join combines columns of multiple tables while the union combines rows of the tables.

The SQL UNION examples

SQL UNION example

To get the data from the A table, you use the following SELECT statement:

SELECT 
    id
FROM
    A;

To retrieve the data from the B table, you use the following statement:

SELECT 
    id
FROM
    B;

To combine result sets of these two queries, you use the UNION operator as follows:

SELECT
	id
FROM
	a
UNION
SELECT
	id
FROM
	b;

The result set includes only 3 rows because the UNION operator removes one duplicate row.

SQL UNION ALL example

To retain the duplicate row, you use the UNION ALL operator as follows:

SQL UNION with ORDER BY example

To sort the result set, you place the ORDER BY clause after all the SELECT statements as follows:

SELECT
	id
FROM
	a
UNION
SELECT
	id
FROM
	b
ORDER BY id DESC;

The database system performs the following steps:

1. First, execute each SELECT statement individually.
2. Second, combine result sets and remove duplicate rows to create the combined result set.
3. Third, sort the combined result set by the column specified in the ORDER BY clause.

In practice, we often use the UNION operator to combine data from different tables. See the following employees and dependents tables:

The following statement uses the UNION operator to combine the first name and last name of employees and dependents.

SELECT
	first_name,
	last_name
FROM
	employees
UNION
SELECT
	first_name,
	last_name
FROM
	dependents
ORDER BY
	last_name;

In this tutorial, you have learned how to use the UNION operator to combine two or more result sets from multiple queries.