ПОДЧИНЕННЫЕ ЗАПРОСЫ (ПОДЗАПРОСЫ)

Подчиненным называется запрос, содержащийся в предложении WHERE или HAVING другой инструкции SQL. В предложении WHERE они помогают отбирать из таблицы результатов запроса отдельные строки, а в предложении HAVING – группы строк. Кроме этого подчиненные запросы используются в инструкциях INSERT, UPDATE, DELETE, а также в списке столбцов инструкции SELECT.

Механизм подчиненных запросов позволяет использовать результаты одного запроса в качестве составной части другого.

По типу возвращаемых значений существует три типа подзапросов:

1. скалярный – возвращает единственное значение;

2. строковый – возвращает значения нескольких столбцов таблицы, но в одной строке;

3. табличный – возвращает значения одного и более столбцов таблицы, размещенные более чем в одной строке.

Понятие подчиненного запроса играет важную роль в SQL по трем следующим причинам:

- инструкция SQL с подчиненным запросом зачастую является самым естественным способом выражения запроса, так как она лучше всего соответствует его словесному описанию;

- подчиненные запросы облегчают написание инструкции SELECT, поскольку они позволяют разбивать запрос на части (на запрос и подчиненные запросы);

- существуют запросы, которые нельзя сформулировать на SQL иначе как с помощью подзапросов.

Подчиненный запрос всегда заключается в круглые скобки и сохраняет структуру инструкции SELECT, содержащей предложение from и необязательные предложения where, group by и having. Структура этих предложений в подчиненном запросе идентична их структуре в инструкции select; в подчиненном запросе эти предложения выполняют свои обычные функции.

Однако между подчиненным запросом и инструкцией SELECT в соответствии со стандартом имеется ряд отличий:

- таблица результатов подчиненного запроса должна состоять только из одного столбца. Это означает, что в предложении SELECT подчиненного запроса всегда указывается только один возвращаемый столбец.

- в подчиненный запрос не может входить предложение ORDER BY. Результаты подчиненного запроса используются только внутри главного запроса и для пользователя остаются невидимыми, поэтому нет смысла их сортировать.

- имена столбцов в подчиненном запросе могут являться ссылками (внешними ссылками) на столбцы таблиц главного запроса.

- подчиненный запрос не может быть запросом на объединение нескольких различных инструкций select (т.е. нельзя использовать UNION).

- подчиненный запрос в операции сравнения может стоять только справа от оператора сравнения, т.е. неравенство А<(подчиненный запрос) разрешается, а неравенство (подчиненный запрос)>А недопустимо. Сказанное не ограничивает возможности операции сравнения, поскольку знак любого неравенства всегда можно "перевернуть" так, чтобы подчиненный запрос оказался с правой стороны. Однако это говорит о том, что иногда требуется "переворачивать" логику словесного запроса так, чтобы он формально соответствовал действительной инструкции SQL.

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

Условия отбора в подчиненном запросе. В SQL используются следующие условия отбора в подчиненном запросе:

- сравнение с результатом подчиненного запроса. Значение выражения сравнивается с одним значением, которое возвращается подчиненным запросом.

- проверка на принадлежность результатам подчиненного запроса. Значение выражения проверяется на равенство одному из множества значений, которые возвращаются подчиненным запросом. Эта проверка напоминает простую проверку на членство во множестве.

- проверка на существование. Проверяется наличие строк в таблице результатов подчиненного запроса.

- многократное сравнение. Значение выражения сравнивается с каждым из множества значений, которые возвращаются подчиненным запросом.

Сравнение с результатом подчиненного запроса (операторы =, !=, <, <=, >, >=). Сравнение с результатом подчиненного запроса является модифицированной формой простого сравнения. Значение выражения сравнивается со значением, которое возвращается подчиненным запросом, и если условие сравнения выполняется, то проверка дает результат TRUE. Эта проверка используется для сравнения значения из проверяемой строки с одним значением, полученным от подчиненного запроса, как показано в следующих примерах:

Вывести список сотрудников, котрые работают в офисе на заданной улице

SELECT sno, fname, lname, position

FROM staff

WHERE bno = (SELECT bno

FROM branch

WHERE street=‘Main Street’);

Вывести список сотрудников, заработная плата которых превышает среднюю заработную плату сотрудников всей компании, и определить, сколько составляет это превышение:

SELECT sno, fname, lname, position, salary-(SELECTAVG(salary)FROM staff) AS sal_diff

FROM staff

WHERE salary>(SELECT AVG(salary)

FROM staff);

Подчиненный запрос, участвующий в операции сравнения, должен возвращать в качестве результата единичное значение, т.е. одну строку, содержащую один столбец. Если результатом подчиненного запроса являются несколько строк или несколько столбцов, то сравнение не имеет смысла и СУБД выдает сообщение об ошибке. Если в результате выполнения подчиненного запроса не будет получено ни одной строки или будет получено значение NULL, то операция сравнения возвращает NULL.

Проверка на принадлежность результатам подчиненного запроса (предикат IN). Эта проверка является видоизмененной формой простой проверки на членство во множестве. Одно значение сравнивается со столбцом данных, которые возвращаются подчиненным запросом, и если это значение равно одному из элементов столбца, проверка дает результат TRUE. Данная проверка используется, когда необходимо сравнить значение из проверяемой строки с множеством значений, отобранных подчиненным запросом. Рассмотрим пример:

Вывести список сотрудников, работающих в Минских отделениях компании

SELECT fname, lname, position

FROM staff

WHERE bno IN (SELECT bno

FROM branch

WHERE city=‘Minsk’);

Этот подчиненный запрос возвращает список идентификаторов отделений, расположенных в Минске. Главный (внешний запрос) посредством проверки IN возвращает выборочную информацию о сотрудниках из тех строк таблицы staff , которые по значению в bno удовлетворяют ее истинности.

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

Проверка на существование (предикат EXIST). В результате проверки на существование (предикат EXIST) можно выяснить, содержится ли в таблице результатов подчиненного запроса хотя бы одна строка. Аналогичной простой проверки не существует. Проверка на существование допустима только в подчиненных запросах.

Если подчиненный запрос не возвращает ни одной строки, проверка EXIST возвращает значение FALSE. Эта проверка не может возвращать NULL. Можно изменить логику проверки EXIST и использовать форму NOT EXIST.

Предикат EXIST в действительности совсем не использует результаты подчиненного запроса. Проверяется только наличие результатов. По этой причине в SQL смягчается правило, согласно которому “подчиненный запрос должен возвращать один столбец данных”, и в подчиненном запросе проверки EXIST допускается использование формы SELECT *, что и наблюдается на практике. Рассмотрим еще один вариант создания предыдущего вопроса:

Вывести список сотрудников, работающих в Минских отделениях компании

SELECT sno, fname, lname, position

FROM staff s

WHERE EXIST (SELECT *

FROM branch b

WHERE s.bno=b.bno AND city=‘Minsk’);

Отметим, что в приведенном примере подчиненный запрос содержит внешнюю ссылку на столбец таблицы из главного запроса (s.bno). Внешняя ссылка представляет собой имя столбца, не входящего ни в одну из таблиц, перечисленных в предложении FROM подчиненного запроса, и принадлежащего таблице, указанной в предложении FROM главного запроса. На практике в подчиненном запросе проверки EXIST всегда имеется внешняя ссылка, "связывающая" подчиненный запрос со строкой, проверяемой в настоящий момент главным запросом.

Многократное сравнение (предикаты ANY и ALL). В SQL имеются также две разновидности многократного сравнения – ANY и ALL, расширяющие предыдущие проверки до уровня других операторов сравнения, таких как больше (>) или меньше (<). В обеих проверках некоторое значение сравнивается со столбцом данных, отобранных подчиненным запросом.

Предикат ANY. В проверке ANY, для того чтобы сравнить проверяемое значение со столбцом данных, отобранных подчиненным запросом, используется один из шести операторов сравнения (=, !=, <, <=, >, >=). Проверяемое значение поочередно сравнивается с каждым элементом, содержащимся в столбце. Если любое из этих сравнений дает результат TRUE, то проверка ANY возвращает значение TRUE. Вот пример запроса с предикатом ANY:

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

SELECT fname, lname, tel_no

FROM renter

WHERE max_rent >= ANY (SELECT rent

FROM property_for_rent

WHERE type=‘f’ AND rooms=2);

В соответствии со стандартом ANSI/ISO вместо предиката ANY можно использовать предикат SOME. Обычно можно употреблять любой из них, но некоторые СУБД не поддерживают предикат SOME.

Если подчиненный запрос в проверке ANY не создает ни одной строки или если столбец результатов содержит значения NULL, то в различных СУБД проверка ANY может выполниться по-разному. В стандарте ANSI/ISO для языка SQL содержатся подробные правила, определяющие результаты проверки ANY, когда проверяемое значение сравнивается со столбцом результатов подчиненного запроса.

Если подчиненный запрос возвращает результат в виде пустого столбца, то проверка ANY возвращает значение FALSE (в результате выполнения подчиненного запроса не получено ни одного значения, для которого выполнялось бы условие сравнения).

Если операция сравнения имеет значение TRUE хотя бы для одного значения в столбце, то проверка ANY возвращает значение TRUE (имеется некоторое значение, полученное подчиненным запросом, для которого условие сравнения выполняется).

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

Если операция сравнения не имеет значение TRUE ни для одного значения в столбце, и в нем имеется одно или несколько значений NULL, то проверка ANY возвращает результат NULL. (В этой ситуации невозможно с определенностью утверждать, существует ли полученное подчиненным запросом значение, для которого выполняется условие сравнения; может быть, существует, а может и нет — все зависит от "настоящих" значений неизвестных данных.)

На практике проверка ANY иногда может приводить к ошибкам, которые трудно выявить, особенно когда применяется оператор сравнения "не равно" (!=).

Запрос с предикатом ANY всегда можно преобразовать в запрос с предикатом EXIST, перенося операцию сравнения внутрь условия отбора подчиненного запроса.

Предикат ALL.В проверке ALL, как и в проверке ANY, используется один из шести операторов сравнения проверяемого значения со столбцом данных, отобранных подчиненным запросом. Проверяемое значение поочередно сравнивается с каждым элементом, содержащимся в столбце. Если все сравнения дают результат TRUE, то проверка ALL возвращает значение TRUE. Вот пример запроса с предикатом ALL:

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

SELECT fname, lname, tel_no

FROM renter

WHERE max_rent>=ALL(SELECT rent

FROM property_for_rent

WHERE type=‘f’ AND rooms=2);

Проверка ALL, подобно проверке ANY, может оказаться трудной для понимания, поскольку включает в себя не одно сравнение, а несколько.

Если подчиненный запрос в проверке ALL не возвращает ни одной строки или если столбец результатов запроса содержит значения NULL, то в различных СУБД проверка ALL может выполняться по-разному. В стандарте ANSI/ISO для языка SQL содержатся подробные правила, определяющие результаты проверки ALL, когда проверяемое значение сравнивается со столбцом результатов подчиненного запроса. Если подчиненный запрос возвращает результат в виде пустого столбца, то проверка ALL возвращает значение TRUE. Считается, что условие сравнения выполняется, даже если результаты подчиненного запроса отсутствуют.

Если операция сравнения дает результат FALSE для какого-нибудь значения в столбце, то проверка ALL возвращает значение FALSE. В этом случае можно утверждать, что условие сравнения выполняется не для каждого значения, возвращенного подчиненным запросом.

Если операция сравнения не дает результат FALSE ни для одного значения в столбце, но для одного или нескольких значений дает результат NULL, то проверка ALL возвращает значение NULL. В этой ситуации нельзя с определенностью утверждать, для всех ли значений, возвращенных подчиненным запросом, справедливо условие сравнения; может быть, для всех, а может и нет — все зависит от "настоящих" значений неизвестных данных.

Ошибки, которые могут случиться, если проверка ANY содержит оператор сравнения "не равно" (<>), происходят и в проверке ALL. Проверку ALL, так же как и проверку ANY, всегда можно преобразовать в эквивалентную проверку на существование (EXIST), перенеся операцию сравнения в подчиненный запрос.

Уровни вложенности запросов.Все рассмотренные до сих пор запросы были "двухуровневыми" и состояли из главного и подчиненного запросов. Точно так же, как внутри главного запроса может находиться подчиненный запрос, внутри подчиненного запроса может находиться еще один подчиненный запрос, называемый в таком случае вложенным. Вот пример "трехуровневого" запроса:

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

SELECT street, area, city, type, rooms, rent

FROM property_for_rent IN (SELECT sno

FROM staff

WHERE bno=(SELECT bno

FROM branch

WHERE street ='…'));

По такой же методике можно создавать запросы с четырьмя и более уровнями вложенности. Стандарт ANSI/ISO не определяет максимальное число уровней вложенности, но на практике с ростом их числа очень быстро увеличивается время выполнения запроса. Когда запрос имеет более двух уровней вложенности, он становится трудным для чтения и понимания. Во многих СУБД количество уровней вложенности запросов ограничено относительно небольшим числом.