MySQL UNION operator basic knowledge points

MySQL UNION Operator

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

describe

The MySQL UNION operator is used to combine the results of two or more SELECT statements into one result set. Multiple SELECT statements will remove duplicate data.

grammar

MySQL UNION operator syntax format:

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

parameter

• expression1, expression2, ... expression_n : The columns to be retrieved.

• tables: The data tables to be retrieved.

• WHERE conditions: Optional, search conditions.

• DISTINCT: Optional, remove duplicate data in the result set. By default, the UNION operator removes duplicate data, so the DISTINCT modifier has no effect on the result.

• ALL: Optional, returns all result sets, including duplicate data.

Demo Database

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

Here is the data selected from the "Websites" table:

mysql> SELECT * FROM Websites;
+----+--------------+---------------------------+-------+---------+
| id | name | url | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1 | Google | https://www.google.cm/ | 1 | USA |
| 2 | Taobao | https://www.taobao.com/ | 13 | CN |
| 3 | Novice Tutorial | http://www.runoob.com/ | 4689 | CN |
| 4 | Weibo | http://weibo.com/ | 20 | CN |
| 5 | Facebook | https://www.facebook.com/ | 3 | USA |
| 7 | stackoverflow | http://stackoverflow.com/ | 0 | IND |
+----+---------------+---------------------------+-------+---------+

Here are the data for the "apps" APP:

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

SQL UNION Example

The following SQL statement selects all distinct countries (only distinct values) from the "Websites" and "apps" tables:

Examples

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

The output of executing the above SQL is as follows:

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

SQL UNION ALL Example

The following SQL statement uses UNION ALL to select all countries (also with duplicate values) from the "Websites" and "apps" tables:

Examples

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

The output of executing the above SQL is as follows:

SQL UNION ALL with WHERE

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

Examples

SELECT country, name FROM Websites
WHERE country='CN'
UNION ALL
SELECT country, app_name FROM apps
WHERE country='CN'
ORDER BY country;

The output of executing the above SQL is as follows: