YouTip

This tutorial introduces the syntax and examples of 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, removing duplicate rows. The UNION operator must consist of two or more SELECT statements, and each SELECT statement must have the same number of columns and corresponding data types. ### Syntax MySQL UNION operator syntax format: SELECT column1, column2, ... FROM table1 WHERE condition1 UNION SELECT column1, column2, ... FROM table2 WHERE condition2 [ORDER BY column1, column2, ...]; **Parameter Description:** * `column1`, `column2`, ... are the names of the columns you want to select, if you use `*` it means select all columns. * `table1`, `table2`, ... are the names of the tables you want to query data from. * `condition1`, `condition2`, ... are the filter conditions for each SELECT statement, they are optional. * `ORDER BY` clause is an optional clause that specifies the sorting order of the merged result set. ### Examples 1. Basic UNION operation: SELECT city FROM customers UNION SELECT city FROM suppliers ORDER BY city; The above SQL statement will select all unique city values from the customers and suppliers tables, sorted in ascending order by city name. 2. Using filter conditions with UNION: SELECT product_name FROM products WHERE category = 'Electronics' UNION SELECT product_name FROM products WHERE category = 'Clothing' ORDER BY product_name; The above SQL statement will select product names from Electronics and Clothing categories, sorted in ascending order by product name. 3. Column count and data types must be the same in UNION operations: SELECT first_name, last_name FROM employees UNION SELECT department_name, NULL FROM departments ORDER BY first_name; The result includes first_name and last_name from the employees table, and department_name and NULL from the departments table, all sorted by the first_name column. 4. Using UNION ALL to include duplicate rows: SELECT city FROM customers UNION ALL SELECT city FROM suppliers ORDER BY city; The above SQL statement uses UNION ALL to combine all cities from the customers and suppliers tables, including duplicate rows. > The UNION operator removes duplicate rows when merging result sets, while UNION ALL does not remove duplicate rows. Therefore, UNION ALL may have better performance, but if you do want to remove duplicate rows, you can use UNION. * * * ## Demo Database In this tutorial, we will use the TUTORIAL sample database. The following data is 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 | | | 4689 | CN || 4 | Weibo | http://weibo.com/ | 20 | CN || 5 | Facebook | https://www.facebook.com/ | 3 | USA || 7 | stackoverflow | http://stackoverflow.com/ | 0 | IND |+----+---------------+---------------------------+-------+---------+ The following is data from the "apps" table: 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 Example The following SQL statement selects all **different** country values from the "Websites" and "apps" tables (only different values): ## Example SELECT country FROM Websites UNION SELECT country FROM apps ORDER BY country; After executing the above SQL, the output is as follows: !(#) **Note:** UNION cannot be used to list all countries from both tables. If some websites and apps come 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 Example The following SQL statement uses UNION ALL to select **all** country values from the "Websites" and "apps" tables (including duplicate values): ## Example SELECT country FROM Websites UNION ALL SELECT country FROM apps ORDER BY country; After executing the above SQL, the output is as follows: !(#) * * * ## SQL UNION ALL with WHERE The following SQL statement uses UNION ALL to select **all** China (CN) data from the "Websites" and "apps" tables (including duplicate values): ## Example SELECT country, name FROM Websites WHERE country='CN' UNION ALL SELECT country, app_name FROM apps WHERE country='CN' ORDER BY country; After executing the above SQL, the output is as follows: !(#)