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
UNION
SELECT column_name( s) FROM table2;
Note: By default, the UNION operator selects different values. If duplicate values are allowed, use UNION ALL.
SQL UNION ALL syntax
UNION ALL
SELECT column_name(s) FROM table2;
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 | http://www.php.cn/ | 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:
+----+------------+ --------------------------+---------+
| 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
UNION
SELECT country FROM apps
ORDER BY country;
Execute the above SQL and the output result is as follows:
![](https://img.php.cn/upload/course/000/000/004/580471ee5cd2c922.jpg)
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
UNION ALL
SELECT country FROM apps
ORDER BY country;
Execute the above SQL and the output result is as follows:
![](https://img.php.cn/upload/course/000/000/004/5804720b9d799664.jpg)
SQL UNION ALL WITH WHERE
The following SQL statement uses UNION ALL to select all China( CN) data (there are also duplicate values):
Example
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:
![](https://img.php.cn/upload/course/000/000/004/5804721dbdaf9685.jpg)