Соотнесение таблицы со своей копией

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

SELECT *

FROM Orders outer

WHERE amt > =

( SELECT AVG (amt)

FROM Orders inner

WHERE inner.cnum = outer.cnum );

Существуют так называемые специальные операторы SQL, они имеют смысл только для подзапросов. Отличительная особенность специальных операторов - они принимают подзапрос как аргумент, точно так же, как это делает IN.

Оператор (квантор) EXISTS

EXISTS(Х) - булевский оператор. Он получит значение "истинна", если запрос Х вернет хоть одну строку. Допустим, нам необходимо узнать список продавцов, у которых есть более одного покупателя

SELECT DISTINCT snum

FROM Customers outer

WHERE EXISTS

( SELECT *

FROM Customers inner

WHERE inner.snum = outer.snum

AND inner.cnum < > outer.cnum );

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

На практике очень часто приходится использовать EXIST вместе с NOT. Допустим, нам необходимо вывести список продавцов, за которыми числится только один покупатель

SELECT DISTINCT snum

FROM Customers outer

WHERE NOT EXISTS

( SELECT *

FROM Customers inner

WHERE inner.snum = outer.snum

AND inner.cnum < > outer.cnum );

Кроме EXISTS в подзапросах возможно использование еще двух операторов - ANY и ALL.

Оператор ANY

Оператор ANY становится верным, если значение из верхнего подзапроса совпадает по крайней мере с одним значением из вложенного подзапроса. Например если значение - кандидат равно 1, а вложенный подзапрос вернул {1, 2, 3}, оператор ANY станет верным (внешний запрос проверяет на равенство). Например, если нам нужно найти всех продавцов, которые живут в тех же городах, что и покупатели, можно записать следующий запрос:

SELECT *

FROM Salespeople

WHERE city = ANY

(SELECT city

FROM Customers );

Существует синоним оператора ANY - SOME. Так как SQL похож на английский, то одни предложения верно звучат с ANY, другие - с SOME. Действие операторов эквивалентно.

Оператор ALL

Вторым допустимым оператором является ALL. Действие его противоположно оператору ANY. Оператор ALL становится верным, если все значения из вложенного подзапроса равны значению-кандидату из внешнего запроса. Например если значение - кандидат равно 1, а вложенный подзапрос вернул {1, 1, 1}, оператор ALL станет верным (внешний запрос проверяет на равенство).

Например, если нам необходимо найти всех продавцов, у которых рейтинг выше чем у любого продавца из Рима, можно записать следующий запрос:

SELECT *

FROM Customers

WHERE rating > ALL

(SELECT rating

FROM Customers

WHERE city = Rome ):

Операторы ANY и ALL можно выразить через EXIST в кореллированном подзапросе, в явном виде они нужны лишь для упрощения записи запроса. Обратное утверждение не верно - т.е. не все то, что можно выполнить с помощью EXIST, можно сделать с помощью ANY и ALL

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

Замечание 2: В случае, если подчиненный запрос вернул пустое множество, оператор ALL становится верным, а ANY - ложным.