Exercise #7

The products in the database can be of three types: PCs, laptops, and printers. A natural way to solve this exercise is combining the three sets corresponding to each type of products. And here is the solution by one of our users:

SELECT model, price
FROM PC
WHERE model = (SELECT model
FROM Product
WHERE maker = 'B' AND
type = 'PC'
)
UNION
SELECT model, price
FROM Laptop
WHERE model = (SELECT model
FROM Product
WHERE maker = 'B' AND
type = 'Laptop'
)
UNION
SELECT model, price
FROM Printer
WHERE model = (SELECT model
FROM Product
WHERE maker = 'B' AND
type = 'Printer'
);

This query returns the correct result for the main database, but terminates with the following error message when run against the checking database:

In other words, we cannot compare a single value with the set that is returned if the maker B manufactures more than one model of any type - which is exactly the case for the checking database.

There is no difficulty correcting the query - the simple predicate («=») just has to be replaced by the predicate checking the occurrence in a list of values (IN):

SELECT model, price
FROM PC
WHERE model IN (SELECT model
FROM Product
WHERE maker = 'B' AND
type = 'PC'
)
UNION
SELECT model, price
FROM Laptop
WHERE model IN (SELECT model
FROM Product
WHERE maker = 'B' AND
type = 'Laptop'
)
UNION
SELECT model, price
FROM Printer
WHERE model IN (SELECT model
FROM Product
WHERE maker = 'B' AND
type = 'Printer'
);

Note that possible duplicate pairs of values {model, price} will be eliminated by the  UNION operator.

However, redundancy is evident in this case: for each of the queries combined filtering by manufacturer B is done separately. This can be fixed by first doing the combining, then the filtering by manufacturer:

SELECT * FROM (SELECT model, price
FROM PC
UNION
SELECT model, price
FROM Laptop
UNION
SELECT model, price
FROM Printer
) AS a
WHERE a.model IN (SELECT model
FROM Product
WHERE maker = 'B'
);

Here, filtering by product type is no longer possible, but we don’t really need it since the model number is unique within the Product table – i.e., a single model number can’t belong to products of different types. As a result, the query plan will have 8 operations instead of 12 for the initial query. Consequently, the second query will have a shorter execution time.

The IN predicate is evaluated for each record combined by UNION. Therefore the efficiency of such a query depends on how far down the list the model sought for is. For models excluded from the result set the whole list has to be scanned. Eventually, the execution time of such queries is the longer, the larger the list (i.e. the more models are manufactured by maker B).

It is possible to use a table join instead of the IN predicate; however, SQL Server builds identical execution plans for both cases.

SELECT a.model,
price
FROM (SELECT
model, price
FROM PC
UNION
SELECT model, price
FROM
Laptop
UNION
SELECT model, price
FROM Printer
) AS a JOIN
Product p ON a.model = p.model
WHERE p.maker = 'B';

Queries based on table joins can be an alternative to union queries. For this particular task, such a solution has a less efficient execution plan, but in other cases it may prove to be preferable. Anyway, for learning purposes, it makes sense to try different ways of solving the task, which is left as an exercise to the reader.

T&S

Solve this task at SQL-EX.RU