describe

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:

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

parameter

  • expression1, expression2, … Expression_n: Column to be retrieved.
  • Tables: The data table to retrieve.
  • WHERE conditions: Optional.
  • DISTINCT: Deletes duplicate data from the result set. This option is optional. The UNION operator removes duplicates by default, so the DISTINCT modifier has no effect on the result.
  • ALL: ALL result sets, including duplicate data, are returned.

Demo database

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

Here’s some data from the “Websites” table:

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

Here is the data of the “apps” APP:

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

 

The UNION SQL instance

The following SQL statement selects all different countries (with different values) from the “Websites” and “apps” tables:

The instance

SELECT country FROM Websites

UNION

SELECT country FROM apps

ORDER BY country;

The following output is displayed:

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

SQL UNION ALL instance

Select * from ‘Websites’ and’ apps’ tables using UNION ALL;

The instance

SELECT country FROM Websites

UNION ALL

SELECT country FROM apps

ORDER BY country;

The following output is displayed:

 

SQL UNION ALL with WHERE

Select * from ‘Websites’; select * from’ Websites’; select * from ‘apps’;

The instance

SELECT country, name FROM Websites

WHERE country=’CN’

UNION ALL

SELECT country, app_name FROM apps

WHERE country=’CN’

ORDER BY country;

The following output is displayed: