четверг, 23 февраля 2017 г.

Использование ключевых слов SOME | ANY и ALL с предикатами сравнения

Использование ключевых слов SOME | ANY и ALL с предикатами сравнения

<выражение> <оператор сравнения> SOME|ANY (<подзапрос>)
SOME и ANY являются синонимами, т.е. может использоваться любое из них. Результатом подзапроса является один столбец величин. Если для какого-нибудь значения V, получаемого из подзапроса, результат операции "<значение выражения> <оператор сравнения> V " равняется TRUE, то предикат ANY также равняется TRUE.


<выражение> <оператор сравнения> ALL (<подзапрос>)
исполняется так же, как и ANY, но для всех значений, получаемых из подзапроса, проверка должна удовлетворять результату TRUE для предиката "<значение выражения> <оператор сравнения> V ".
Пример. Найти поставщиков компьютеров, моделей которых нет в продаже (т.е. отсутствуют в таблице PC):
SELECT DISTINCT maker
FROM Product
WHERE type = 'PC' AND NOT model = ANY
       (SELECT model
       FROM PC);

Оказалось, что только у поставщика Е есть модели отсутствующие в продаже:

maker
E
Рассмотрим подробно этот пример. Предикат
model = ANY (SELECT model FROM PC);
вернет значение TRUE, если модель, определяемая полем model основного запроса, найдется в списке моделей таблицы PC (возвращаемом подзапросом). Поскольку предикат используется в запросе с отрицанием NOT, то значение TRUE будет получено, если модели не окажется в списке. Этот предикат проверяется для каждой записи основного запроса, которыми являются все модели ПК (предикат type = 'PC') из таблицы Product. Результирующий набор состоит из одного столбца - имени производителя. Чтобы один производитель не выводился несколько раз (что может случиться, если он производит несколько моделей, отсутствующих в таблице PC), используется служебное слово DISTINCT.
Пример. Найти модели и цены ПК-блокнотов, стоимость которых превышает стоимость любого ПК:
SELECT DISTINCT model, price
FROM Laptop
WHERE price > ALL
       (SELECT price
       FROM PC);

modelprice
12981050.0
17501200.0
17521150.0
Приведем формальные правила оценки предикатов, использующих параметры ANY|SOME и ALL:
  • Если определен параметр ALL или SOME и все результаты сравнения значения выражения и каждого значения, полученного из подзапроса, являются TRUE, истинностное значение равно TRUE.
  • Если результат выполнения подзапроса не содержит строк и определен параметр ALL, результат равен TRUE. Если же определен параметр SOME, результат равен FALSE.
  • Если определен параметр ALL и результат сравнения значения выражения хотя бы с одним значением, полученным из подзапроса, является FALSE, истинностное значение равно FALSE.
  • Если определен параметр SOME и хотя бы один результат сравнения значения выражения и значения, полученного из подзапроса, является TRUE, истинностное значение равно TRUE.
  • Если определен параметр SOME и каждое сравнение значения выражения и значений, полученных из подзапроса, равно FALSE, истинностное значение тоже равно FALSE.
  • В любом другом случае результат будет равен UNKNOWN.
Рекомендуемые упражнения: 172430.

Еще о подзапросах

Заметим, что в общем случае запрос возвращает множество значений. Поэтому использование подзапроса в предложении WHERE без операторов EXISTSINALL и ANY, которые дают булево значение, может привести к ошибке времени выполнения запроса.
Пример. Найти модели и цены ПК, стоимость которых превышает минимальную стоимость ПК-блокнотов:
SELECT DISTINCT model, price
FROM PC
WHERE price >
       (SELECT MIN(price)
       FROM Laptop);

Этот запрос вполне корректен, т.к. скалярное значение price сравнивается с подзапросом, который возвращает единственное значение. В результате получим три модели ПК:

modelprice
1121850.0
1233950.0
1233980.0
Однако, если в ответ на вопрос "найти модели и цены ПК, стоимость которых совпадает со стоимостью ПК-блокнота" написать следующий запрос:
SELECT DISTINCT model, price
FROM PC
WHERE price =
       (SELECT price
       FROM Laptop);

то при выполнении последнего мы можем получить такое сообщение об ошибке:
Эта ошибка будет возникать при сравнении скалярного значения с подзапросом, который либо возвращает более одного значения, либо ни одного.
Подзапросы, в свою очередь, также могут содержать вложенные запросы.
С другой стороны, подзапрос, возвращающий множество строк и содержащий несколько столбцов, вполне естественно может использоваться в предложении FROM. Это позволяет ограничить набор столбцов и/или строк при выполнении операции соединения таблиц.
Пример. Вывести производителя, тип, модель и частоту процессора для ПК-блокнотов, частота процессора которых превышает 600 МГц.
Этот запрос может быть сформулирован, например, следующим образом:
SELECT prod.maker, lap.*
FROM (SELECT 'Laptop' AS type, model, speed
       FROM Laptop
       WHERE speed > 600) AS lap INNER JOIN
       (SELECT maker, model
       FROM Product) AS prod ON lap.model = prod.model;

В результате получим:
makertypemodelspeed
BLaptop1750750
ALaptop1752750
Наконец, подзапросы могут присутствовать в предложении SELECT. Это иногда позволяет весьма компактно сформулировать запрос.
Пример. Найти разницу между средними значениями цены ПК-блокнотов и ПК, т.е. на сколько в среднем ПК-блокнот стоит дороже, чем ПК.
Здесь вообще можно обойтись одним предложением SELECT:
SELECT (SELECT AVG(price)
       FROM Laptop) -
       (SELECT AVG(price)
       FROM PC) AS dif_price;

В результате получим:
dif_price
365.81818181818187
Рекомендуемые упражнения: 18252627283739465657.