Union

The UNION keyword is used for integrating queries:

< query 1 >
UNION [ALL]
< query 2 >

The UNION clause combines the results of two SELECT statements into a single result set. If the ALL parameter is given, all the duplicates of the rows returned are retained; otherwise the result set includes only unique rows. Note that any number of queries may be combined. Moreover, the union order can be changed with parentheses.

The following conditions should be observed:

• The number of columns of each query must be the same;
• Result set columns of each query must be compared by the data type to each other (as they follows);
• The result set uses the column names from the first query;
• The ORDER BY clause is applied to the union result, so it may only be written at the end of the combined query.

Example 5.7.1

SELECT model, price
FROM PC
UNION
SELECT model, price
FROM Laptop
ORDER BY price DESC;

Example 5.7.2

SELECT Product.type, PC.model, price
FROM PC INNER JOIN
     Product ON PC.model = Product.model
UNION
SELECT Product.type, Laptop.model, price
FROM Laptop INNER JOIN
     Product ON Laptop.model = Product.model
ORDER BY price DESC;

Suggested exercises: 7, 24, 26, 29, 30, 32, 36, 37, 41, 43, 44, 45, 48, 49, 51, 54, 56, 57, 102, 113, 131

Let’s consider the task.

One laptop and three PCs are in the database from maker ‘B’, and all the PCs are of the same model.

If we’ll use UNION ALL, we get all these items. Using UNION,  the duplicates will be excluded from the result:

select p.model, p.type from pc join Product p on PC.model=p.model where maker='B'
UNION ALL
select p.model, p.type from printer pr join Product p on pr.model=p.model where maker='B'
UNION ALL
select p.model, p.type from laptop lp join Product p on lp.model=p.model where maker='B';

select p.model, p.type from pc join Product p on PC.model=p.model where maker='B'
UNION
select p.model, p.type from printer pr join Product p on pr.model=p.model where maker='B'
UNION
select p.model, p.type from laptop lp join Product p on lp.model=p.model where maker='B';

The solution on the base of UNION corresponds to the somewhat different task, namely: