SQL UNION operator

In this tutorial, you will learn how to combine the results of two or more SQL queries.

UNION operator

The UNION operator is used to merge the results of two or more SELECT queries into a single result set. UNION operation is different from joining two tables by merging their columns. The UNION operator places all rows from the two source tables into a single result table, thus creating a new table.

The following are the basic rules for using UNION to combine the result sets of two SELECT queries:

• In all queries, the number and order of the columns must be the same.

• The data types of the corresponding columns must be compatible.

When these conditions are met, these tables are jointly compatible (union-compatible) :

Syntax

Basic syntax of UNION:

SELECT column_list FROM table1_name
　　　UNION SELECT column_list FROM table2_name;

To better understand the union operation, we assume that there are some hypothetical fields, such as first_name and last_name, exist in the employees and customers tables. Please note that these fields actually do not exist in our demonstration database tables.

+----+------------+-----------+--------+
| id | first_name | last_name | salary |
+----+------------+-----------+--------+
|　　1　| Ethan | Hunt |　　　5000 |
|　　2　| Tony | Montana |　　　6500 |
|　　3　| Sarah | Connor |　　　8000 |
|　　4　| Rick | Deckard |　　　7200 |
|　　5　| Martin | Blank |　　　5600 |
+----+------------+-----------+--------+

+----+------------+-----------+----------+
| id | first_name | last_name | city |
+----+------------+-----------+----------+
|　　1　| Maria | Anders | Berlin |
|　　2　| Fran | Wilson | Madrid |
|　　3　| Dominique | Perrier | Paris |
|　　4　| Martin | Blank | Turin |
|　　5　| Thomas | Hardy | Portland |
+----+------------+-----------+----------+

Let's execute the union operation to merge the results of two queries.

The following statement returns the names and surnames of all customers and employees:

SELECT first_name, last_name FROM employees　
UNION
SELECT first_name, last_name FROM customers;

After executing the above statement, the result set will be as follows:

+---------------+--------------+
| first_name | last_name |
+---------------+--------------+
| Ethan | Hunt |
| Tony | Montana |
| Sarah | Connor |
| Rick | Deckard |
| Martin | Blank |
| Maria | Anders |
| Fran | Wilson |
| Dominique | Perrier |
| Thomas | Hardy |
+---------------+--------------+

By default, the UNION operator removes duplicate rows from the combined result set. This is why the above query only returns9rows, if you notice the name 'Martin Blank' appearing in both the employees and customers tables.

However, if you want to retain duplicate rows, you can use the ALL keyword as shown below:

SELECT first_name, last_name FROM employees　
UNION ALL
SELECT first_name, last_name FROM customers;