SQL UNION Operator

SQL UNION and operating results for two or more SELECT statements.

SQL UNION Operator

UNION operation result set operator used to combine two or more SELECT statements.

Please note, UNION inside each SELECT statement must have the same number of columns. The columns must also have similar data types. At the same time, the order of the columns in each SELECT statement must be the same.

SQL UNION Syntax

Note: By default, UNION operator to select a different value.If you allow duplicate values, use UNION ALL.

SQL UNION ALL syntax

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

The demo database

In this tutorial, we will use w3big sample database.

The following is a selected "Websites" table data:

+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1  | Google       | https://www.google.cm/    | 1     | USA     |
| 2  | 淘宝          | https://www.taobao.com/   | 13    | CN      |
| 3  | 本教程      | http://www.w3big.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 "apps" APP data:

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 examples

The following SQL statement to select all the different country (only a different value) from the "Websites" and "apps" table:

Examples

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

Execute the above SQL output results are as follows:

Note: UNION lists two tables can not be used all the country.If some sites and APP from the same country, each country is listed only once. UNION only select different values. Use UNION ALL to select duplicate values!

SQL UNION ALL instances

The following SQL statement uses UNION ALL to select all of the country from the "Websites" and "apps" table (there are duplicate values):

Examples

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

Execute the above SQL output results are as follows:

SQL UNION ALL with the WHERE

The following SQL statement uses UNION ALL to select all of China (CN) Data from the "Websites" and "apps" table (there are 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;

Execute the above SQL output results are as follows: