Упражнение 7

Продукция в базе данных может быть трех типов: ПК, ноутбуки и принтеры. Естественным решением является объединение трех наборов по каждому типу продукции. Вот как решал эту задачу один наш участник:

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'
            );

При этом на основной базе решение дает правильный результат, а на проверочной SQL Server выдает следующую ошибку:

(«Подзапрос возвращает более 1 значения. Это недопустимо, если подзапрос используется как выражение или с операторами сравнения =, !=, <, <= , >, >=».)

Иначе говоря, мы не можем сравнивать отдельное значение с набором, который имеет место, если производитель B выпускает более одной модели какого-либо типа, что и имеет место в проверочной базе данных.

Поправить запрос несложно, достаточно заменить предикат простого сравнения («=») предикатом попадания в список значений (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'
                );

Заметим, что возможные повторяющиеся здесь пары значений {модель, цена} будут устранены оператором UNION.

Однако налицо явная избыточность: в каждом из объединяемых запросов выполняется отбор моделей производителя B. Указанный недостаток можно устранить, сначала выполнив объединение, а затем отбор по производителю:

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'
                );

При этом здесь уже не может быть отбора по типу, однако в этом нет нужды, так как номер модели уникален в таблице Product, то есть один и тот же номер не может принадлежать продукции различных типов. В результате мы получим процедурный план, содержащий 8 операций вместо 12, что имело место в первом варианте решения. Соответственно и время выполнения последнего запроса будет меньше.

Предикат IN будет проверяться для каждой записи объединения. Поэтому эффективность выполнения такого запроса будет зависеть от того, как далеко в списке будет находиться искомая модель. Для исключаемых моделей придется просматривать весь список. В конечном итоге время выполнения таких запросов будет тем больше, чем длиннее список (то есть чем больше моделей имеет производитель B).

Можно вместо предиката IN использовать соединение, однако SQL Server дает для этих двух случаев идентичные планы выполнения.

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';

Альтернативой запросам, использующим объединение, могут служить запросы на основе соединения. В данной задаче такое решение будет иметь менее эффективный план выполнения, хотя в других случаях может оказаться предпочтительным. Так или иначе, в учебных целях будет полезно рассмотреть разные способы решения задачи, что и предлагается вам выполнить самостоятельно.

ПиР

Решить задачу на SQL-EX.RU