Язык манипуляции данными в SQL

Оператор SELECT

Рассмотрим следующую задачу.

Определить производителей, которые выпускают модели всех типов (схема "Компьютерная фирма").

Ключевым словом здесь является "всех", т.е. производитель в таблице Product должен иметь модели каждого типа, т.е. и PC, и Laptop, и Printer.

Как раз для решения подобных задач в реляционную алгебру Коддом была введена специальная операция реляционного деления (DIVIDE BY).

С помощью этой операции наша задача решается очень просто [1]:

Product[maker, type] DIVIDE BY Product[type]

Здесь квадратными скобками обозначается операция взятия проекции на соответствующие атрибуты.

Операция реляционного деления избыточна, т.е. она может быть выражена через другие операции реляционной алгебры. Возможно, поэтому ее нет в языке SQL.

На примере решения сформулированной задачи я хочу показать несколько приемов реализации операции реляционного деления на языке SQL.

Группировка

Если использовать тот факт, что, согласно описанию предметной области, типов продукции всего три, то мы можем выполнить группировку по производителю и подсчитать количество уникальных типов. Затем мы отберем только тех производителей, у которых это число равно трем.

Итак,

Консоль

Выполнить

SELECT maker
FROM Product
GROUP BY maker
HAVING COUNT(DISTINCT type) = 3;

SELECT maker FROM Product GROUP BY maker HAVING COUNT(distinct type) = 3;

    Схема

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

Консоль

Выполнить

SELECT maker
FROM Product
GROUP BY maker
HAVING COUNT(DISTINCT type) = 
            (SELECT COUNT(DISTINCT type) FROM Product);

SELECT maker FROM Product GROUP BY maker HAVING COUNT(DISTINCT type) =             (SELECT COUNT(DISTINCT type) FROM Product);

    Схема

Разность

Если взять операцию разности ВСЕХ имеющихся типов моделей и типов у конкретного производителя, то результирующая выборка не должна содержать строк.

Консоль

Выполнить

SELECT DISTINCT maker    
FROM Product Pr1
WHERE 0 = (SELECT COUNT(*) FROM
(SELECT type FROM Product
 EXCEPT
 SELECT type FROM Product Pr2 
 WHERE Pr2.maker = Pr1.maker 
) X );

SELECT DISTINCT maker    FROM Product Pr1 WHERE 0 = (SELECT COUNT(*) FROM (SELECT type FROM Product  EXCEPT  SELECT type FROM Product Pr2  WHERE Pr2.maker = Pr1.maker ) X );

    Схема

Этот запрос можно написать короче, если воспользоваться тем свойством, что истинностное значение предиката ALL есть TRUE, если подзапрос не возвращает строк:

Консоль

Выполнить

SELECT DISTINCT maker 
FROM Product Pr1
WHERE type = ALL
(SELECT type FROM Product
 EXCEPT
 SELECT type FROM Product Pr2 
 WHERE Pr2.maker = Pr1.maker 
);

SELECT DISTINCT maker FROM Product Pr1 WHERE type = ALL (SELECT type FROM Product  EXCEPT  SELECT type FROM Product Pr2  WHERE Pr2.maker = Pr1.maker );

    Схема

Для искомых производителей список типов в предикате ALL будет пуст (предикат равен TRUE). В остальных случаях он будет содержать типы моделей, отсутствующие у производителя из внешнего запроса, поэтому операция сравнения (равенство "=") для всех его моделей даст FALSE.

Существование

Не должно существовать такого типа продукции, которого бы не было у искомого производителя.

Консоль

Выполнить

SELECT DISTINCT maker 
FROM Product Pr1
WHERE NOT EXISTS 
  (SELECT type 
    FROM Product 
    WHERE type NOT IN 
              (SELECT type 
                FROM Product Pr2 
                WHERE Pr1.maker = Pr2.maker
              )
  );

SELECT DISTINCT maker FROM Product Pr1 WHERE NOT EXISTS   (SELECT type     FROM Product     WHERE type NOT IN               (SELECT type                 FROM Product Pr2                 WHERE Pr1.maker = Pr2.maker               )   );

    Схема

Кроме первого варианта с группировкой все остальные решения используют коррелирующие подзапросы для определения множества типов моделей производителя из основного запроса.

Следует также отметить, что решение с группировкой не подойдет для случая, когда требуется выполнить деление не на все множество имеющихся типов, а на некоторое их подмножество. Например, если требуется найти производителей, у которых множество типов включает в себя (или совпадает) множество типов, определяемое некоторыми критериями. Другие же приемы можно адаптировать для решения подобной задачи.