MySQL UNION Operator

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

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

Syntax

Syntax format of MySQL UNION operator:

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

参数

• expression1, expression2, ... expression_n: 要检索的列。

• tables: 要检索的数据表。

• WHERE conditions: 可选， 检索条件。

• DISTINCT: 可选，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。

• ALL: 可选，返回所有结果集，包含重复数据。

演示数据库

在本教程中，我们将使用 w3codebox 样本数据库。

下面是选自 "Websites" 表的数据：

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　　|　基础教程网　　　　　　|　http://www.oldtoolbag.com/　　　　|　4689　　|　CN　　　　　　|
|　4　　|　微博　　　　　　　　　　|　http://weibo.com/　　　　　　　　　|　20　　　　|　CN　　　　　　|
|　5　　|　Facebook　　　　　|　https://www.facebook.com/　|　3　　　　　|　USA　　　　　|
|　7　　|　stackoverflow　|　http://stackoverflow.com/　|　　　0　|　IND　　　　　|
+----+---------------+---------------------------+-------+---------+

下面是 "apps" APP 的数据：

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 from the 'Websites' and 'apps' tablesDifferentcountry (only different values):

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

The output result of the above SQL is as follows:

Note:UNION cannot be used to list all the 'country' from two tables. If some websites and APPs come from the same country, each country will only be listed once. UNION will only select different values. Use UNION ALL to select duplicate values!

SQL UNION ALL Example

The following SQL statement uses UNION ALL to select from the 'Websites' and 'apps' tablesAllcountry (also with duplicate values):

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

The output result of the above SQL is as follows:

SQL UNION ALL with WHERE

The following SQL statement uses UNION ALL to select from the 'Websites' and 'apps' tablesAllData of China (CN) (also with duplicate values):

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