Using UNION to combine the results from two tables

The SQL language offers us a fairly simple way to combine, within the same SELECT statement, the results of two different tables.

To achieve this, it's time to use UNION all'opertore which will, in fact, the union of the results obtained by querying the two tables.

Note that in order to be used properly, it's time that the UNION operator:

• that the tables are queried on the same number of columns;
• requests that the columns have the same name;
• that the required columns in the two tables have consistent data types;

Here's an example: let's say of having to operate on a database of hypothetical travel agency and assume that our DB contains only two tables:

• hotel_italia
  • name
  • stars
  • city
  • nation
• hotel_europa
  • name
  • stars
  • city
  • nation

Now suppose that a customer wants us to know either the offers of hotels in Italy and in Europe:

 SELECT name, stars, city, country







 FROM hotel_italia







 UNION







 SELECT name, stars, city, country







 FROM hotel_europa

With this query we get a complete listing of all hotels in the two tables:

name	stars	city	nation
Hotel Cavour	4	Rome	EN
Hotel Miramare	2	Catholic	EN
Hotel Manzoni	2	Milan	EN
Hotel Espana	3	Madrid	ES
Hilton	5	London	UK
Hotel am Schlossgarten	4	Stuttgart	DE

Note: please note that the UNION does not show any duplicate records (by default operates as if it were a SELECT DISTINCT ), and if you want any duplicate records also show (think back to our example, at a hotel in this both in the table in that hotel_italia hotel_europa) will need to use UNION ALL.

Sort and limit the results arising from UNION

Suppose that the results of using UNION and many want them to be limited to specified number. How? Here's a solution:

 



 SELECT * FROM







 (SELECT name, stars, city, country FROM hotel_italia







 UNION







 SELECT name, stars, city, country FROM hotel_europa)







 AS Hotel







 ORDER BY DESC LIMIT 10 stars

 

Essentially we treat the results arising from the union of two or more tables as if they were the result of a single table, by doing so we can use standard sorting and limit.