4.4 Использование оператора exists.

Квантор EXISTS (существует) - понятие, заимствованное из формальной логики. В языке SQL предикат с квантором существования представляется выражением

… EXISTS (SELECT * FROM ...).

Такое выражение считается истинным только тогда, когда результат вычисления "SELECT * FROM ..." является непустым множеством (вернул хотя бы одну строку). Рассмотрим примеры.

Пример 33.

Выдать названия Блюд, представленных в меню на данное число.

SELECT Блюдо

Результат
Блюдо
Салат летний
Салат мясной
Паштет из рыбы
Суп харчо
Суп молочный
Бефстроганов
Судак по-польски
Суфле яблочное
Кофе на молоке

FROM Блюда

WHERE EXISTS (SELECT * FROM Меню m

WHERE m.Блюдо = Блюда.ID_Блюда

and Дата = '2011-01-02');

Система последовательно выбирает строки таблицы Блюда, выделяет из них значения столбцов Блюдо и ID_Блюда, а затем проверяет, является ли истинным условие существования, т.е. существует ли в таблице Меню(m) хотя бы одна строка со значением Дата = '2011-01-02' и значением m.Блюдо, равным значению Блюда.ID_Блюда. Если условие выполняется, то полученное значение столбца Блюдо включается в результат. Соответственно, запрос

SELECT Блюдо

FROM Блюда

WHERE NOT EXISTS (SELECT *

FROM Меню m

WHERE m.Блюдо = Блюда.ID_Блюда );

выдаст названия блюд, которые ни разу не включались в состав меню. Данные запросы также являются соотнесенными.

Хотя этот рассмотренный пример только показывает иной способ формулировки запроса для задачи, решаемой и другими путями (с помощью оператора IN или соединения), EXISTS представляет собой одну из наиболее важных возможностей SQL. Фактически любой запрос, который выражается через IN, может быть альтернативным образом сформулирован также с помощью EXISTS. Однако обратное высказывание несправедливо.

4.5. Использование операторов any и all.

Операторы ANY и ALL напоминают EXISTS, который воспринимает подзапросы как аргументы; однако они отличаются от EXISTS тем, что используются совместно с реляционными операторами. В этом отношении, они напоминают оператор IN, когда тот используется с подзапросами; они берут все значения выведенные подзапросом и обрабатывают их как модуль. Однако, в отличие от IN, они могут использоваться только с подзапросами.

Оператор ANY.

Оператор ANY означает, что предикат истинен, если хотя бы для одного значения из подзапроса предикат сравнения истинен.

Пример 34.

Имеется иной способ нахождения названий Блюда представленных в меню на данное число.

SELECT Блюдо

FROM Блюда

WHERE ID_Блюда = ANY (SELECT Блюдо

FROM Меню

WHERE Дата = '2011-01-02');

Оператор ANY берет все значения выведенные подзапросом, (для этого случая - это все значения Блюд в таблице Меню на указанную дату), и оценивает их как верные, если любой(ANY) из них равняется значению Блюда текущей строки внешнего запроса.

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

Если при работе оператора ANY используется равенство, то этот запрос может быть всегда заменен запросом с IN

SELECT Блюдо

FROM Блюда

WHERE ID_Блюда IN (SELECT Блюдо

FROM Меню

WHERE Дата = '2011-01-02');

или EXISTS.

SELECT Блюдо

FROM Блюда b

WHERE EXISTS (SELECT Блюдо

FROM Меню m

WHERE Дата = '2011-01-02' and m.Блюдо = b. ID_Блюда);

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

Пример 35.

Список блюд с ценой, меньшей, чем любое блюдо из заказа номер 2.

SELECT b.Блюдо,

cast(SUM (s.Вес * n.Цена/1000)+b.Труд as numeric(6,2)) as Стоимоть

FROM (Блюда b JOIN Состав s ON b.ID_Блюда = s.Блюдо)

JOIN Наличие n ON s.Продукт = n.Продукт

WHERE b.ID_Блюда NOT IN (select Блюдо from Заказы where ID_Заказ = 2)

GROUP BY b.Блюдо,b.ID_Блюда, b.Труд

HAVING (SUM (s.Вес * n.Цена/1000)+b.Труд) < ANY

(SELECT SUM (s.Вес*n.Цена/1000)+b.Труд as Стоимоть

FROM ((Блюда b JOIN Состав s ON b.ID_Блюда = s.Блюдо)

JOIN Наличие n ON s.Продукт = n.Продукт)

JOIN Заказы z ON s.Блюдо = z.Блюдо

WHERE ID_Заказ = 2

GROUP BY b.Блюдо, b.Труд);

Подзапрос определяет стоимость блюд заказа. Эти данные используются для отбора в основном запросе.

Оператор ALL.

С помощью ALL, предикат является верным, если каждое значение выбранное подзапросом удовлетворяет условию в предикате внешнего запроса. Если мы хотим найти самый дорогой (дешевый Цена <=) продукт, то можем это сделать следующим образом:

Пример 36.

SELECT Продукт FROM Наличие

WHERE Цена >= ALL (SELECT DISTINCT Цена FROM Наличие);

Т.е. мы найдем те продукты, которые имеют цену выше или равную всем остальным (максимальную). Как и в случае с ANY, мы можем использовать EXISTS для производства альтернативной формулировки такого же запроса:

SELECT Продукт

FROM Наличие n

WHERE NOT EXISTS (SELECT * FROM Наличие n1

WHERE n1.Цена > n.Цена);

ALL чаще используется с неравенствами, чем с равенствами, так как значение может быть "равным для всех" результатом подзапроса, только если все результаты, фактически, идентичны. Рассмотрим следующий запрос:

Пример 37.

SELECT Продукт

FROM Наличие

WHERE Цена = ALL (SELECT DISTINCT Цена FROM Наличие);

Эта команда допустима, но, мы не получим никакого вывода. Только в единственном случае вывод будет выдан этим запросом - если все цены на продукты окажутся одинаковыми.

В SQL, выражение - < > ALL - в действительности соответствует " не равен любому " результату подзапроса. Другими словами, предикат верен, если данное значение не найдено среди результатов подзапроса.

Следует отметить, что вариант с EXISTS не абсолютно идентичен вариантам с ANY(ALL) из-за различия в том как обрабатываются NULL-значения. Всякий раз, когда допустимый подзапрос не в состоянии сделать вывод, ALL - автоматически верен, а ANY автоматически неправилен. Большинство пользователей, однако, находят ANY и ALL более удобными в использовании, чем EXISTS, который требует соотнесенных подзапросов. Кроме того, в зависимости от реализации, ANY и ALL могут, по крайней мере, в теории, быть более эффективными, чем EXISTS. Подзапросы ANY или ALL могут выполняться один раз и иметь вывод, используемый чтобы определять предикат для каждой строки основного запроса. EXISTS, с другой стороны, берет соотнесенный подзапрос, который требует, чтобы весь подзапрос повторно выполнялся для каждой строки основного запроса. Основная причина для формулировки EXISTS как альтернативы ANY и ALL в том, что ANY и ALL могут быть несколько неоднозначен, из-за способа использования этого термина в Английском языке.

Правильное понимание ANY и ALL.

В SQL, сказать что - значение больше (или меньше) чем любое(ANY) из набора значений – то же самое что сказать, что оно больше (или меньше) чем любое одно отдельное из этих значений. И наоборот, сказать, что значение не равно всему(ALL) набору значений, тоже что сказать, что нет такого значения в наборе которому оно равно.