4.3.4.2 Соотнесенные (коррелированные) подзапросы

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

Пример 4.32 Найти всех заказчиков в порядках на 3-е октября:

SELECT * FROM Заказчики outer WHERE 10/03/2003 IN

( SELECT odate FROM Порядки inner WHERE outer.cnum = inner.cnum );

Рассмотрим работу соотнесенного подзапроса:

1. Выбирается первая строка из внешнего запроса (строка- кандидат).

2. Сохраняются значения из строки кандидата под псевдонимом.

3. Выполняется вложенный запрос. Там, где есть ссылка на внешний запрос, используется поле из строки кандидата.

4. Оценивается предикат внешнего запроса на основе результатов подзапроса, выполненного на шаге 3. Если предикат принимает значение «истина», то выводится строка кандидат.

5. Выполняются шаги 1-4 для следующей строки внешнего запроса и т. д.

Пример 4.33 Вывести имена и номера всех продавцов, у которых более одного заказчика:

SELECT snum, sname FROM Продавцы main WHERE 1 <

(SELECT COUNT (*) FROM Заказчики WHERE snum = main.snum );

Предложение HAVING также может включать и коррелированные подзапросы.

Пример 4.34 Предположим, что необходимо вывести суммарные значения сумм приобретений из таблицы Порядки, сгруппированные по датам, удалив из вывода все строки, где бы сумма не была по крайней мере на 2000.00 выше максимальной (MAX) суммы:

SELECT odate, SUM (amt) FROM Порядки a GROUP BY odate HAVING SUM (amt) >

( SELECT 2000.00 + MAX (amt) FROM Порядки b WHERE a.odate = b.odate );

3. Запросы с использованием кванторов

Кванторы EXISTS (существования), ALL (всеобщности) - понятия, заимствованные из формальной логики. В языке SQL предикат с квантором существования представляется выражением EXISTS (SELECT * FROM ...). Такое выражение считается истинным только тогда, когда результат вычисления «SELECT * FROM ...» является непустым множеством, т.е. когда существует какая-либо запись в таблице, указанной во фразе FROM подзапроса, которая удовлетворяет условию WHERE подзапроса. (Практически этот подзапрос всегда будет коррелированным множеством.). Фактически любой запрос, который выражается через IN, может быть альтернативным образом сформулирован также с помощью EXISTS. Предикат с квантором всеобщности представляется выражением ALL (SELECT * FROM ...). Такое выражение считается истинным только тогда, когда предикат сравнения внешнего запроса будет истинным при сравнении со всеми строками подзапроса.

Пример 4.35 Вывести некоторые данные из таблицы Заказчики если, и только если, один или более заказчиков в этой таблице находятся в Мехико:

SELECT cnum, cname, city FROM Заказчики WHERE EXISTS

( SELECT * FROM Заказчики WHERE city =’Мехико’);

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

Пример 4.36 Вывести номера продавцов, которые имеют нескольких заказчиков:

SELECT DISTINCT snum FROM Заказчики outer WHERE EXISTS

( SELECT * FROM Заказчики inner WHERE inner.snum = outer.snum

AND inner.cnum < > outer.cnum );

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

SELECT DISTINCT snum FROM Заказчики outer WHERE NOT EXISTS

( SELECT * FROM Заказчики inner WHERE inner.snum = outer.snum

AND inner.cnum < > outer.cnum );

Кроме предиката EXISTS в подзапросах могут использоваться предикаты ANY (SOME), ALL.

Пример 4.38 Имеется новый способ нахождения продавцов, у которых заказчики размещены в тех же городах: SELECT * FROM Продавцы WHERE city = ANY

(SELECT city FROM Заказчики );

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

Можно также использовать оператор IN, чтобы создать запрос аналогичный предыдущему:

SELECT * FROM Продавцы WHERE city IN (SELECT city FROM Заказчики );

С помощью ALL, предикат принимает значение «истина», если каждое значение выбранное подзапросом удовлетворяет условию в предикате внешнего запроса.

Пример 4.39 Вывести только тех заказчиков, чьи оценки, выше чем у каждого заказчика в

Риме: SELECT * FROM Заказчики WHERE rating > ALL

(SELECT rating FROM Заказчики WHERE city = Rome );

Приведем основные правила записи подзапросов:

1. подзапрос должен быть заключен в круглые скобки;

2. подзапрос должен находиться справа от оператора сравнения в предикате;

3. в подзапросе нельзя использовать GROUP BY.