A UNION is used to combine the result sets of multiple queries into one result set. The queries that are being combined may come from any table, but must have two similarities in order to be combined.

  • The queries must return the same number of fields. If the number of fields does not match, the two queries cannot be accurately merged.
  • The data types of the queries must match. A UNION basically creates a new table from two queries, so naturally, the data type of the new table should match.

This example will use the 'world.sql' sample database provided by http:dev.myql.com/doc

Let's say we want one table that contains a list of all countries and cities alongside their country codes. We can do so by creating a UNION with this query: 

mysql> SELECT name, code FROM country;
+-----------------------------+------+
| name                        | code |
+-----------------------------+------+
| Aruba                       | ABW  |
| Afghanistan                 | AFG  |
| Angola                      | AGO  |
| Anguilla                    | AIA  |
| Albania                     | ALB  |
...

And this query: 

mysql> SELECT name, countrycode FROM city;
+------------------+-------------+
| name             | countrycode |
+------------------+-------------+
| Kabul            | AFG         |
| Qandahar         | AFG         |
| Herat            | AFG         |
| Mazar-e-Sharif   | AFG         |
| Amsterdam        | NLD         |
...

The full UNION query and it's results are as follows: 

mysql> SELECT name, code FROM country
    -> UNION
    -> SELECT name, countrycode FROM city
    -> ORDER BY code LIMIT 20;
+------------------+------+
| name             | code |
+------------------+------+
| Aruba            | ABW  |
| Oranjestad       | ABW  |
| Kabul            | AFG  |
| Afghanistan      | AFG  |
| Qandahar         | AFG  |
| Herat            | AFG  |
| Mazar-e-Sharif   | AFG  |
| Huambo           | AGO  |
| Lobito           | AGO  |
| Angola           | AGO  |
| Benguela         | AGO  |
| Namibe           | AGO  |
| Luanda           | AGO  |
| South Hill       | AIA  |
| The Valley       | AIA  |
| Anguilla         | AIA  |
| Tirana           | ALB  |
| Albania          | ALB  |
| Andorra          | AND  |
| Andorra la Vella | AND  |
+------------------+------+
20 rows in set (0.01 sec)

Here we have a result set containing all countries, and cities with their corresponding country codes.

By default, a UNION will remove any duplicates from the result set. In this example, if a city and country happen to have the same name, one of them will be omitted.
If we want to keep the entire result set regardless of duplicates, we can can use UNION ALL instead of just UNION.

You might also be interested in