Shulou(Shulou.com)06/01 Report--

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:

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.