SQL UNION

The SQL UNION operator combines the results of two or more SELECT statements.

SQL UNION Operator

The UNION operator is used to merge the result sets of two or more SELECT statements.

Please note that each SELECT statement inside a UNION must have the same number of columns. Columns must also have similar data types. Also, the order of the columns in each SELECT statement must be the same.

SQL UNION syntax

Note: By default, the UNION operator selects different values. If duplicate values ​​are allowed, use UNION ALL.

SQL UNION ALL syntax

Note: The column name in the UNION result set is always equal to the first SELECT statement in UNION column name.

Demo Database

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

The following is the data selected from the "Websites" table:

+----+--------------+--- ------------------------+------+---------+
| id | name | url                                                                    --------+-------+---------+
| 1 | Google | https://www.google.cm/ | 1 | USA |
| 2 | Taobao | https://www.taobao.com/ | 13 | CN |
| 3 | php Chinese website | //m.sbmmt.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 |
+----+---------------+----------- ----------------+------+---------+

The following is the data of "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 Example

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

Example

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

Execute the above SQL and the output result is as follows:

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

---

SQL UNION ALL Example

The following SQL statement uses UNION ALL to select all country (there are also duplicates) from the "Websites" and "apps" tables Value):

Instance

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

Execute the above SQL and the output result is as follows:

---

SQL UNION ALL WITH WHERE

The following SQL statement uses UNION ALL to select all China( CN) data (there are also 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;

The output result of executing the above SQL is as follows: