Union order by query syntax/statement usage in MySQL database

MySQL database UNION ORDER BY query syntax/statement usage

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 combine the results of two or more SELECT statements into a single result set.Multiple SELECT statements 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];

参数

• expression1, expression2, ... expression_n: Column to retrieve.
• tables: The data table to retrieve.
• WHERE conditions: Optional, search criteria.
• DISTINCT: Optionally, remove duplicate data from the result set.The UNION operator has deduplicated data by default, so the DISTINCT modifier has no effect on the result.
• ALLES: Optional, returns all result sets, including duplicates.

demo database

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

Here is the data from the "Websites" table:

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.chenweiliang.com/    | 4689  | CN      |
| 4  | 微博          | 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 the "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 instance

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

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

SQL UNION ALL instance

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

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

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):

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