1.4. Вложенные запросы
Стандарт языка позволяет в тело одного оператора select внедрять другой оператор select. В такой ситуации можно рассматривать внешний и внутренний (внедряемый) операторы запроса. Обычно внутренний запрос генерирует значение, которое проверяется в предикате внешнего запроса (в предложении where или having), определяющего, верно оно или нет. Если внутренний оператор запроса помещен в предложения where и having внешнего оператора select, to создается ситуация вложенных запросов (подзапросов).
В сочетании с другими возможностями оператора выбора, такими как группировка, возможность вкладывать запросы внутрь друг друга представляет собой мощное средство для достижения нужного результата. Во фразе from оператора select, если при формировании запроса требуется более чем один экземпляр некоторой таблицы, допустимо применять синонимы к именам таблииы. Синонимы задаются с использованием ключевого слова as:
FROM Rl AS A, Rl AS B.
Подзапросы могут быть нескольких видов:
скалярный подзапрос, возвращающий единственное значение;
строковый подзапрос, возвращающий несколько значений в виде одной строки;
табличный подзапрос, возвращающий данные в виде таблицы.
Подзапрос может указываться непосредственно после операторов сравнения (-, <! >, <=, >=)! его текст должен быть заключен в скобки.
Запрос 12
БД НИР. Вывести список платежей, где величина единовременных начислений превысила среднее значение.
Стандарт языка не позволяет применять агрегатные функции в предложении where, поэтому единовременные начисления необходимо сравнивать с результатом подзапроса, вычисляющего среднее значение начислений всех строк таблицы. Данный скалярный подзапрос вернет значение, равное 1208.33, и уже с этим значением будут сравниваться все начисления и отбираться для помещения в таблицу вывода.
Запрос будет выглядеть следующим образом:
SELECT ФИО, Этап, Начисления FROM R WHERE Начисления > (SELECT avg(Начисления) FROM R)
Результат запроса:
ФИО Этап Начисления(руб)
Просов См. |
Этап_1 |
2000 |
Немцов Я.Ю. |
Этап_3 |
2000 |
Немцов Я.Ю. |
Этап_4 |
2000 |
Яров И.М. |
Этап_4 |
2000 |
Реализация вложенных запросов требует соблюдения определенных правил
и ограничений.
В подзапросах не должна использоваться фраза order by.
Список в предложении select может включать только имена столбцов и составленные из них выражения.
По умолчанию имена столбцов в подзапросе относятся к таблице, указанной во фразе from.
Подзапрос, участвующий в операции, может быть только правым операндом .
В стандарте SQL2 операторы сравнения расширены до многократных сравнений с использованием ключевых слов any и all. Это расширение используется при сравнении значения определенного столбца со столбцом данных, возвращаемым вложенным запросом.
Ключевое слово any, поставленное в любом предикате сравнения, означает, что предикат будет истинен, если хотя бы для одного значения из подзапроса предикат сравнения истинен. Ключевое слово all требует, чтобы предикат сравнения был бы истинен при сравнении со всеми строками подзапроса.
Запрос 13
БД НИР. Определить начисления, которые превышают начисления хотя бы одного специалиста, выполнявшего Этап_1.
Подобный запрос может быть реализован разными путями. Например, можно организовать подзапрос для нахождения минимальных начислений по Этапу_1, а затем во внешнем запросе путем сравнения с полученным значением вывести те начисления, которые превосходят это значение.
Как будет выглядеть запрос с использованием ключевого слова any: в этом случае внутренний запрос должен сформировать набор числовых значений начислений для ситуации, где Этап='Этап_Г. Внешний же запрос выберет сведения о начислениях, больших любого из значений в этом наборе:
SELECT ФИО, Этап, Начисления FROM В WHERE Начисления > ANY (SELECT Начисления
FROM R
WHERE Этап = 'Этап_1')
Результат запроса:
ФИО Этап Начисления (руб)
Семенов Т.Т. Этап_1 1000
Просов СМ. Этап_1 2000
МеховаИ.И. Этап_2 1000
Просов СМ. Этап_3 1000
МеховаИ.И. Этап_3 1000
Немцов Я.Ю. Эгап_3 2000
Немцов Я.Ю. Этап_4 2000
Яров И.М. Этап_4 3000
Запрос 14
БД НИР. Определить начисления, которые превышают начисления любого специалиста, выполнявшего Этап_:.
SELECT ФИО, Этап, Начисления FROM R WHERE Начисления > ALL (SELECT Начисления FROM R WHERE Этап = 'Этап_1'];
В данном случае результат запроса содержит только одну строку, поскольку только эта выплата больше 2 000. Именно такое значение имеют наибольшие начисления по первому этапу.
ФИО Этап Начисления(руб)
Яров И.М. Этап_4 3000
Запрос 15
БД Сессия. Найти студентов, которые сдали все экзамены на оценку не ниже чем "хорошо:
SELECT ФИО FROM S WHERE 4 >= ALL (SELECT Оценка FROM S AS SI WHERE S.ФИО =S1.ФИО)
Результат запроса:
ФИО
Мур СМ.
Цуканов Т.Т.
Дрозд Г.Р.
Петрова СО.
Часов И.И.
Иванова Я.С.
Здесь также внутренний запрос формирует набор оценок определенного студента, которые сравниваются со значением 4. В том случае, если все оценки равны или превосходят данное значение, ФИО студента помещается в таблицу вывода.
Совместно с подзапросами могут быть использованы специально предназначенные для этого предикаты exists и нот exists. Результат их обработки имеет значение true или false.
Для ключевого слова exists результат равен true в том и только в том случае, если в возвращаемой подзапросом таблице присутствует хотя бы одна строка. Если результирующая таблица подзапроса пуста, результат обработки этого ключевого слова будет иметь значение false.
Для ключевого слова not exists наблюдается полностью противоположная ситуация. Он возвращает истину, если вывод подзапроса пуст, и ложь, если вывод подзапроса не пуст.
Возможности использования данных предикатов будут проиллюстрированы ниже при обсуждении многотабличных запросов.