4.3.4 Формирование структур вложенных запросов

Следует отметить, что SQL обладает большой избыточностью в том смысле, что он часто предоставляет несколько различных способов формулировки одного и того же запроса.

Очень удобным средством, позволяющим формулировать запросы более понятным образом, является возможность использования подзапросов, вложенных в основной запрос.

Подзапрос - это запрос, который может входить в предикаты условия выборки предложений WHERE и HAVING оператора SELECT или других операторов SQL, использующих WHERE предложение. Вложенный подзапрос может содержать в своей WHERE (HAVING) фразе другой вложенный подзапрос и т.д. Нетрудно догадаться, что вложенный подзапрос создан для того, чтобы при отборе строк таблицы, сформированной основным запросом, можно было использовать данные из других таблиц. В SQL/89 к подзапросам применяется то ограничение, что результирующая таблица должна содержать в точности один столбец. Поэтому в синтаксических правилах, определяющих подзапрос, вместо списка выборки указано арифметическое выражение. Заметим еще, что поскольку подзапрос всегда вложен в некоторый другой оператор SQL, то в качестве констант в арифметическом выражении выборки и логических выражениях разделов WHERE и HAVING можно использовать значения столбцов текущих строк таблиц, участвующих в запросах (подзапросах) более внешнего уровня.

Существуют простые и коррелированные (соотнесенные) вложенные подзапросы. Они включаются в WHERE (HAVING) фразу с помощью операторов IN, EXISTS, ALL, ANY или одного из условий сравнения ( = | <> | < | <= | > | > = ). Простые вложенные подзапросы обрабатываются системой «снизу вверх». Первым обрабатывается вложенный подзапрос самого нижнего уровня. Множество значений, полученное в результате его выполнения, используется при реализации подзапроса более высокого уровня и т.д.

1. Простые подзапросы

Рассмотрим простые подзапросы.

Пример 4.27 Предположим, что известно имя продавца (Мотика), но неизвестно значение его поля snum, и необходимо извлечь все его порядки из таблицы Порядки:

SELECT * FROM Порядки WHERE snum =

( SELECT snum FROM Продавцы WHERE sname = 'Мотика');

Замечание. Результат выполнения запроса будет эквивалентен результату следующей последовательности действий:

1. выполнить один раз вложенный подзапрос и получить значение номера продавца (единственной найденной строкой естественно будет snum = 1004);

2. просканировать таблицу Порядки, каждый раз сравнивая значение номера продавца с результатом подзапроса (WHERE snum = 1004), и отобрать только те строки, в которых предикат принимает значение true.

Замечание. При использовании подзапросов необходимо убедиться, что подзапрос будет выдавать одну и только одну строку вывода. Если подзапрос не выводит никаких значений, то команда не потерпит неудачи, но основной запрос не выведет никаких значений. В этом случае результат подзапроса следует рассматривать как неопределенный (неизвестный).

Можно в некоторых случаях использовать 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 =’Мехико’);