Упражнение 8 (подсказки и решения)

Сначала пара «естественных» решений, которые отличаются лишь предикатом, проверяющим отсутствие у поставщика модели ноутбука.

Решение 4.4.1. Предикат NOT IN

SELECT DISTINCT maker
FROM Product
WHERE type = 'PC' 
    AND maker NOT IN (SELECT maker
                      FROM Product
                      WHERE type = 'Laptop'
) ;

Решение 4.4.2. Предикат EXISTS (что обычно для этого предиката, подзапрос является коррелирующим)

SELECT DISTINCT maker
FROM Product AS pc_product
WHERE type = 'pc' 
    AND NOT EXISTS (SELECT maker
                    FROM Product
                    WHERE type = 'laptop' 
                        AND maker = pc_product.maker
                    );

Теперь приведу несколько оригинальных решений.

Решение 4.4.3. Использование коррелирующих запросов с группировкой

SELECT DISTINCT maker
FROM Product as p
WHERE (SELECT COUNT(1)
        FROM Product pt
        WHERE pt.type = 'PC' 
            AND pt.maker = p.maker
        ) > 0 
        AND (SELECT COUNT(1)
            FROM Product pt
            WHERE pt.type = 'Laptop' 
                AND pt.maker = p.maker
            ) = 0;

В подзапросах проверяется, что число моделей ПК поставщика из основного запроса больше нуля, в то время как число моделей ноутбуков этого же поставщика равно нулю.

Следует обратить внимание на аргумент функции COUNT(1). Стандарт определяет два типа аргументов этой функции: «*» и выражение. Применение «*» приводит к подсчету числа строк, отвечающих запросу. Использование выражения дает число строк, для которых выражение имеет значение, то есть не является NULL. В качестве выражения обычно служит имя столбца, поэтому задействование константы может вызвать удивление у тех, кто еще недостаточно хорошо знаком с языком. Поскольку константа (в рассматриваемом запросе 1) не может быть NULL, то такое выражение вполне эквивалентно COUNT(*).

На примере этой несложной задачи можно продемонстрировать многообразие решений, которое обусловлено гибкостью языка SQL.

Решение 4.4.4. Внешнее самосоединение

SELECT DISTINCT p.maker
FROM Product p 
    LEFT JOIN Product p1 ON p.maker = p1.maker 
                        AND p1.type = 'Laptop'
WHERE p.type = 'PC' 
    AND p1.maker IS NULL;

Левое соединение таблицы Product с собой при условии, что производитель один и тот же, а тип продукции из второй таблицы есть ноутбук. Тогда в столбце p1.maker будет находиться NULL, если у поставщика нет моделей ноутбуков, что и используется в предикате предложения WHERE наряду с условием, что в той же строке типом продукции является ПК.

Решение 4.4.5. Группировка

SELECT maker
FROM (SELECT DISTINCT maker, type
    FROM Product
    WHERE type IN ('PC', 'Laptop')
    ) AS a
GROUP BY maker
HAVING COUNT(*) = 1 
    AND MAX(type) = 'PC';

В подзапросе выбираются уникальные пары {поставщик, тип}, если типом является ПК или ноутбук. Затем выполняется группировка по поставщику, при этом сгруппированные строки должны отвечать следующим условиям:

COUNT(*) = 1 — то есть поставщик должен выпускать только один тип продукции из оставшихся (поскольку мы уже отсекли принтеры, то остается либо ПК, либо ноутбук);

MAX(type) = ‘PC’ — этим типом продукции является ПК. Поскольку в предложении HAVING не могут присутствовать ссылки на столбцы без агрегатных функций, то используется MAX(type), хотя с тем же успехом можно было написать и MIN(type).

При таком обилии подходов естественен вопрос об эффективности, то есть какой из представленных запросов будет выполняться быстрее. Лидером здесь, как по числу операций, так и по оценке стоимости, является решение 4.4.5. Наихудшие показатели у третьего варианта. Остальные примерно в два раза по стоимости уступают лидеру.

SET SHOWPLAN_ALL ON;

SET SHOWPLAN_ALL OFF;

Если у вас не установлен SQL Server, вы можете получить план выполнения запроса непосредственно на сайте: https://sql-ex.ru/perfcon.php.

Вернуться к обсуждению упражнения 8

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