2014-02-02
704
Рассмотрим простые подзапросы.
Пример 4.27 Предположим, что известно имя продавца (Мотика), но неизвестно значение его поля snum, и необходимо извлечь все его порядки из таблицы Порядки: SELECT * FROM Порядки WHERE snum = (SELECT snum FROM Продавцы WHERE sname = 'Мотика');Замечание. Результат выполнения запроса будет эквивалентен результату следующей последовательности действий:
- выполнить один раз вложенный подзапрос и получить значение номера продавца (единственной найденной строкой естественно будет snum = 1004);
- просканировать таблицу Порядки, каждый раз сравнивая значение номера продавца с результатом подзапроса (WHERE snum = 1004), и отобрать только те строки, в которых предикат принимает значение true.
Замечание. При использовании подзапросов необходимо убедиться, что подзапрос будет выдавать одну и только одну строку вывода. Если подзапрос не выводит никаких значений, то команда не потерпит неудачи, но основной запрос не выведет никаких значений. В этом случае результат подзапроса следует рассматривать как неопределенный (неизвестный).
|
|
|
Любой подзапрос, использующий агрегатную функцию без предложения GROUP BY, будет возвращать одиночное значение для использования в основном предикате.
Пример 4.29 Вывести все порядки, имеющие сумму приобретений выше средней на 4-е октября: SELECT * FROM Порядки WHERE amt > (SELECT AVG (amt) FROM Порядки WHERE odate = 10/04/2003);Средняя сумма приобретений на 4 октября – 894,38. Все строки со значением в поле amt выше 894,38 являются выбранными. Замечание. Агрегатные функции, примененные к группе (при использовании предложения GROUP BY), могут возвращать несколько значений, следовательно, не допускаются в подзапросах такого характера.Можно использовать оператор IN с подзапросами, которые возвращают любое число строк. Пример 4.30 Вывести все атрибуты таблицы Порядки для продавцов из Лондона: SELECT * FROM Порядки WHERE snum IN (SELECT snum FROM Продавцы WHERE city =’Лондон’);Можно также использовать подзапросы внутри предложения HAVING. Пример 4.31 Подсчитать число заказчиков с оценками выше, чем средняя оценка в Мехико: SELECT rating, COUNT (DISTINCT cnum) FROM Заказчики GROUP BY rating HAVING rating > (SELECT AVG (rating) FROM Заказчики WHERE city =’Мехико’);4.3.4.2 Соотнесенные (коррелированные) подзапросы
|
|
|
Запросы с коррелирова н ными вложенными подзапросами обрабатываются системой в обратном порядке. Сначала выбирается первая строка рабочей таблицы, сформированной основным запросом, и из нее выбираются значения тех столбцов, которые используются во вложенном подзапросе (вложенных подзапросах). Если эти значения удовлетворяют условиям вложенного подзапроса, то выбранная строка включается в результат. Затем выбирается вторая строка и т.д., пока в результат не будут включены все строки, удовлетворяющие вложенному подзапросу (последовательности вложенных подзапросов).
Пример 4.32 Найти всех заказчиков в порядках на 3-е октября: SELECT * FROM Заказчики outer WHERE 10/03/2003 IN (SELECT odate FROM Порядки inner WHERE outer.cnum = inner.cnum); Рассмотрим работу соотнесенного подзапроса:- Выбирается первая строка из внешнего запроса (строка- кандидат).
- Сохраняются значения из строки кандидата под псевдонимом.
- Выполняется вложенный запрос. Там, где есть ссылка на внешний запрос, используется поле из строки кандидата.
- Оценивается предикат внешнего запроса на основе результатов подзапроса, выполненного на шаге 3. Если предикат принимает значение «истина», то выводится строка кандидат.
- Выполняются шаги 1-4 для следующей строки внешнего запроса и т. д.
4.3.4.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.
