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