Рассмотрим простые подзапросы.
Пример 4.27 Предположим, что известно имя продавца (Мотика), но неизвестно значение его поля snum, и необходимо извлечь все его порядки из таблицы Порядки: SELECT * FROM Порядки WHERE snum = ( SELECT snum FROM Продавцы WHERE sname = 'Мотика');Замечание. Результат выполнения запроса будет эквивалентен результату следующей последовательности действий:
Замечание. При использовании подзапросов необходимо убедиться, что подзапрос будет выдавать одну и только одну строку вывода. Если подзапрос не выводит никаких значений, то команда не потерпит неудачи, но основной запрос не выведет никаких значений. В этом случае результат подзапроса следует рассматривать как неопределенный (неизвестный).
Можно в некоторых случаях использовать DISTINCT, чтобы обеспечить генерацию подзапросом одиночного значения. Пример 4.28 Предположим, что мы хотим найти все порядки для тех продавцов которые обслуживают заказчика с номером 2001:SELECT * FROM Порядки WHERE snum = ( SELECT DISTINCT snum FROM Заказчики WHERE cnum = 2001 );Замечание. Обратите внимание, что предикаты, включающие подзапросы, используют структуру < выражение > < оператор > < подзапрос >, а не < подзапрос > < оператор > < выражение > .Любой подзапрос, использующий агрегатную функцию без предложения 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 ); Рассмотрим работу соотнесенного подзапроса: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.