The UNION statement enables the user to generate a union from result tables produced by two or more SELECTs.
In the simplest case, two result tables which have been generated from the same base table can be related to each other.
For example, if all customers living either in Los Angeles or in New York are to be found and if UNION is used, this can be represented in the following way:
SELECT title, firstname, name, city
FROM customer
WHERE city = 'Los Angeles'
UNION
SELECT title, firstname, name, city
FROM customer
WHERE city = 'New York'
This result could also have been obtained by a simple SELECT and an OR specification:
SELECT title, firstname, name, city
FROM customer
WHERE city = 'Los Angeles' OR city = 'New York'
Beyond this, the UNION statement allows result tables to be combined that have been generated from different tables.
It must be ensured then that the data types of the respective ith output columns can be compared to each other. Equality is not required, because the maximum length is used, if this should be necessary. Consequently, CHAR (10) and CHAR (15) columns can be combined with each other, because the length is automatically extended to CHAR (15).
The effect of UNION, INTERSECT, and EXCEPT is shown with and without ALL, using the column 'city' of the tables 'hotel' and 'customer'.
To get a better overview of the retrieved results, the example shall only refer to cities located in the states greater than or equal to IL.
The examples are based on the following result tables:
SELECT cities_of_customers = city
FROM customer
WHERE zip < 50000
SELECT cities_of_hotels = city
FROM hotel
WHERE zip < 50000
Find both the cities where the customers live and the cities where the hotels are located. For this purpose, a union is formed across the tables 'customer' and 'hotel'.
SELECT city
FROM customer
WHERE zip < 50000
UNION
SELECT city
FROM hotel
WHERE zip < 50000
It is obvious here that repeatedly occurring cities are output only once. The database issues an implicit DISTINCT for UNION.
To obtain all cities with all their occurrences, the statement UNION ALL can be specified.
SELECT city
FROM customer
WHERE zip < 50000
UNION ALL
SELECT city
FROM hotel
WHERE zip < 50000
