MySQL UNION operator
MySQL UNION operator
This tutorial introduces you to the syntax and examples of the MySQL UNION operator.
Description
The MySQL UNION operator is used to join the results of more than two SELECT statements into a result set. Multiple SELECT statements delete duplicate data.
Grammar
MySQL UNION operator syntax format:
SELECT expression1, expression2,... Expression_nFROM tables [WHERE conditions] UNION [ALL | DISTINCT] SELECT expression1, expression2,... Expression_nFROM tables [WHERE conditions]; parameters
Expression1, expression2,... Expression_n: the column to retrieve.
Tables: the data table to retrieve.
WHERE conditions: optional, search condition.
DISTINCT: optionally, delete duplicate data in the result set. By default, the UNION operator has removed duplicate data, so the DISTINCT modifier has little 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 | Rookie 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 is the data for "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 instance
The following SQL statement selects all the different country (only different values) from the "Websites" and "apps" tables:
Example
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 the country in two tables. If some websites and APP are from the same country, each country will only be listed once. UNION only selects different values. Please use UNION ALL to select duplicate values!
SQL UNION ALL instance
The following SQL statement uses UNION ALL to select all country (with duplicate values) from the "Websites" and "apps" tables:
Example
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 China (CN) data (with duplicate values) from the "Websites" and "apps" tables:
Example
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:
