SQL UNION Operator

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

SQL UNION Operator

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

Please note that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Additionally, the columns in each SELECT statement must be in the same order.

SQL UNION Syntax

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

SQL UNION ALL Syntax

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

Demonstration Database

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

Below is 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  | tutorialpro.org      | http://www.tutorialpro.org/    | 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 data from the "apps" table:

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 all distinct countries from the "Websites" and "apps" tables:

Example

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

Executing the above SQL outputs the following result:

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

SQL UNION ALL Example

The following SQL statement uses UNION ALL to select all countries from the "Websites" and "apps" tables, 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 all data from the "Websites" and "apps" tables where the country is China (CN), including duplicate values:

Example

Executing the above SQL outputs the following results: