SQL UNIONOperators

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

The UNION operator is used to combine the result sets of two or more SELECT statements. It can select data from multiple tables and combine the result sets into one result set. When using UNION, each SELECT statement must have the same number of columns, and the data types of corresponding columns must be similar.

SQL UNION Syntax

SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;

The UNION operator removes duplicate records by default. If you need to keep all duplicate records, you can use the UNION ALL operator.

SQL UNION ALL Syntax

SELECT column1, column2, ...
FROM table1
UNION ALL
SELECT column1, column2, ...
FROM table2;

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

Demo Database

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

Below is the data selected 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  | Example      | http://www.example.com/    | 4689  | CN      |
| 4  | 微博          | http://weibo.com/         | 20    | CN      |
| 5  | Facebook     | https://www.facebook.com/ | 3     | USA     |
| 7  | stackoverflow | http://stackoverflow.com/ |   0 | IND     |
+----+---------------+---------------------------+-------+---------+

Below is the data from 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 Example

The following SQL statement selects alldistinctcountry (only distinct values):

Example

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

Executing the above SQL outputs the following results:

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 distinct values. Use UNION ALL to select duplicate values!

SQL UNION ALL Example

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

Example

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

Executing the above SQL outputs the following results:

SQL UNION ALL with WHERE

The following SQL statement uses UNION ALL to select from the "Websites" and "apps" tablesalldata for China (CN) (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;

Executing the above SQL outputs the following results: