MySQL UNION Operator

This tutorial introduces the syntax and examples of the MySQL UNION operator.

Description

The MySQL UNION operator is used to combine the results of two or more SELECT statements into a single result set. Duplicate data is removed from multiple SELECT statements.

Syntax

The syntax for the MySQL UNION operator is as follows:

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

Parameters

• expression1, expression2, ... expression_n: The columns to retrieve.
• tables: The tables to retrieve data from.
• WHERE conditions: Optional, the conditions for retrieval.
• DISTINCT: Optional, removes duplicate entries from the result set. By default, the UNION operator already removes duplicates, so the DISTINCT modifier has no impact on the result.
• ALL: Optional, returns all results, including duplicates.

Demo Database

In this tutorial, we will use the tutorialpro sample database.

Below are the data from the "Websites" table:

mysql> SELECT * FROM Websites;
+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1  | Google       | https://www.google.cm/    | 1     | USA     |
| 2  | 淘宝          | https://www.taobao.com/   | 13    | CN      |
| 3  | tutorialpro.org      | http://www.tutorialpro.org/    | 4689  | CN      |
| 4  | 微博          | http://weibo.com/         | 20    | CN      |
| 5  | Facebook     | https://www.facebook.com/ | 3     | USA     |
| 7  | stackoverflow | http://stackoverflow.com/ |   0 | IND     |
+----+---------------+---------------------------+-------+---------+

Below are the data from the "apps" table:

mysql> SELECT * FROM apps;
+----+------------+-------------------------+---------+
| id | app_name   | url                     | country |
+----+------------+-------------------------+---------+
|  1 | QQ APP     | http://im.qq.com/       | CN      |
|  2 | 微博 APP | http://weibo.com/       | CN      |
|  3 | 淘宝 APP | https://www.taobao.com/ | CN      |
+----+------------+-------------------------+---------+
3 rows in set (0.00 sec)

SQL UNION Example

The following SQL statement selects all distinct countries from both the "Websites" and "apps" tables:

Example

SELECT country FROM Websites
UNION
SELECT country FROM apps
ORDER BY country;

Executing the above SQL outputs the following result:

Note: UNION cannot list all countries from both tables. If some websites and apps are from the same country, each country will be listed only once. UNION selects only distinct values. Use UNION ALL to select duplicate values!

SQL UNION ALL Example

The following SQL statement uses UNION ALL to select all countries from both the "Websites" and "apps" tables, including duplicates:

Example

SELECT country FROM Websites
UNION ALL
SELECT country FROM apps
ORDER BY country;

Executing the above SQL outputs the following result:

SQL UNION ALL with WHERE

The following SQL statement uses UNION ALL to select all data from China (CN) from both the "Websites" and "apps" tables, including duplicates:

Example

Executing the above SQL outputs the following result: