SQL Union

Previous Page Next Page

The UNION Operator

The UNION operator is utilized to combine the results of two or more SELECT queries into a single result set. Unlike joins, which merge columns from two tables, the union operation creates a new table by stacking all rows from the source tables into a single result table, one on top of the other.

Here are the basic rules for combining the result sets of two SELECT queries using UNION:

The number and order of the columns must be identical in all queries.
The data types of the corresponding columns must be compatible.

When these criteria are met, the tables are considered union-compatible:

Syntax

The basic syntax for UNION is as follows:

SELECT column_list FROM table1_name
UNION SELECT column_list FROM table2_name;

To better understand the union operation, let's assume there are hypothetical fields like first_name and last_name in our employees and customers tables. Please note that these fields do not actually exist in our demo 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 |
+----+------------+-----------+----------+

Table: employees Table: customers

Let's carry out a union operation to combine the results of two queries.

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

Example

Try this code »

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

After executing the statement above, the result set will appear similar to this:

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

The UNION operation, by default, removes duplicate rows from the combined result set. That's why the query above returns only 9 rows, as the name "Martin Blank" appears in both the employees and customers tables.

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

Example

Try this code »

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

Previous Page Next Page