Article directory
MySQL databaseUnion 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 from the "Websites" and "apps" tablesdifferentcountry (only distinct values):
Examples
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 from the "Websites" and "apps" tablesallcountry (also has duplicate values):
Examples
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 from the "Websites" and "apps" tablesallData for China (CN) (also with duplicate values):
Examples
SELECT country, name FROM Websites WHERE country='CN' UNION ALL SELECT country, app_name FROM apps WHERE country='CN' ORDER BY country;
Hope Chen Weiliang Blog ( https://www.chenweiliang.com/ ) shared "union order by query syntax/statement usage in MySQL database", which is helpful to you.
Welcome to share the link of this article:https://www.chenweiliang.com/cwl-475.html
Welcome to the Telegram channel of Chen Weiliang's blog to get the latest updates!
📚 This guide contains huge value, 🌟This is a rare opportunity, don’t miss it! ⏰⌛💨
Share and like if you like!
Your sharing and likes are our continuous motivation!