Вложенные запросы на чтение

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

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

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

· существуют запросы, которые нельзя сформулировать на SQL, не прибегая к помощи вложенных запросов.

Вложенные запросы заключаются в круглые скобки и чаще всего встречаются в предложении WHERE или HAVING оператора SQL. Во вложенном запросе может потребоваться ссылка на значение столбца текущей строки главного запроса, поэтому допустимо использовать имена столбцов главного запроса. С другой стороны главный запрос не может ссылаться на столбцы вложенного запроса. Можно сравнить это с областью видимости локальных переменных во вложенных процедурах языка ПАСКАЛЬ.

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

Вложенный запрос может в свою очередь иметь подзапросы. Иногда в СУБД количество уровней вложения ограничивается. На практике даже три уровня вложенности запросов используются редко.

По механизму выполнения различают простые и коррелированные (связанные) подзапросы. Простые запросы обрабатываются системой “снизу вверх”. Первым обрабатывается вложенный подзапрос самого нижнего уровня. Множество значений, полученное в результате его выполнения, используется при реализации подзапроса более высокого уровня и т.д.

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

Рассмотрим типичный пример вложенного запроса. В таблице A (Name, Gr, Height) имеется информация о росте студентов из разных групп. Для занятия баскетболом требуется сформировать список тех студентов, чей рост выше средного роста в их группе. Задачу можно решить с помощью вложенного запроса

SELECT Name, Gr, Height FROM A X WHERE

Height > (SELECT AVG(Height) FROM A Y WHERE X.Gr = Y.Gr)

Здесь в скобках задан коррелированный подзапрос. Для каждой строки главного запроса по группе определяется средний рост в этой группе. Строка отправляется в результат при выполнении заданного условия. Поскольку одна и та же таблица участвует в главном и вложенном запросах, используются псевдонимы X и Y.

В SQL имеются следующие условия поиска во вложенном запросе:

· сравнение с результатом вложенного запроса;

· проверка на принадлежность результатам вложенного запроса;

· проверка на существование строк вложенного запроса;

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

Рассмотрим эти условия подробнее.

Пример сравнения с результатом вложенного запроса был приведен выше. Конечно, возможны и другие операции сравнения. Подзапрос должен формировать единственное значение. Иначе будет сообщение об ошибке.

Принадлежность результатам вложенного запроса определяется с помощью связки [NOT] IN между главным и вложенным запросами. Вернемся к таблицам поставщиков, изделий и поставок, которые встречались в примерах многотабличных запросов. Требуется определить поставщиков, которые имеют поставки изделия P1 в количестве не менее 100. Решение дает многотабличный запрос

SELECT Sname FROM S, SP WHERE

S.S#=SP.S# AND Q ≥ 100

Возможно, более естественный способ, соответствующий словесному описанию задачи (сложноподчиненное предложение), дает вложенный запрос

SELECT Sname FROM S, SP WHERE

S IN (SELECT S # FROM SP WHERE Q >= 100)

Приведем еще один пример. Имеется таблица A (Name, Sec, Raz) с данными о спортсменах-разрядниках различных секций, где Sec – наимнование секции, а Raz – спортивный разряд. Нужно получить список тех секций, где нет мастеров спорта. На первый взгляд достаточно задать простой запрос

SELECT DISTINCT Sec FROM A WHERE Raz <> ‘мс’

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

SELECT DISTINCT Sec FROM A WHERE

Sec NOT IN (SELECT Sec FROM A WHERE Raz = ‘мс’)

Проверка на существование строк вложенного запроса выполняют с помощью связки [NOT] EXISTS. Альтернативным вариантом решения предыдущей задачи является запрос

SELECT DISTINCT X.Sec FROM A X WHERE NOT EXISTS

(SELECT * FROM A Y WHERE X.Sec = Y.Sec AND Y.Raz = ‘мс’)

Обратим внимание, что этот запрос содержит, в отличие от предыдущего, коррелированный, а не простой подзапрос. Выбор всех полей с помощью параметра ‘*’ сокращает время выполнения подзапроса, а не увеличивает его, как иногда считают, т. к. не требует проекции строки по указанным полям.

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

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

SELECT DISTINCT Sec FROM A WHERE

Sec = ANY (SELECT Sec FROM A X WHERE Raz = ‘мс’)

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

SELECT DISTINCT Sec FROM A X WHERE Raz = ‘мс’ AND

Raz = ALL (SELECT Raz FROM A Y WHERE X.Sec = Y.Sec)

Более естественные (и безопасные!) запросы для этой задачи:

SELECT DISTINCT Sec FROM A WHERE

Sec NOT IN (SELECT Sec FROM A WHERE Raz <> ‘мс’)

либо

SELECT DISTINCT Sec FROM A X WHERE

NOT EXISTS (SELECT * FROM A Y WHERE

X.Sec = Y.Sec AND Y.Raz <> ‘мс’)

(секция не находится среди тех, где имеются отличные от мастеров спортсмены).

Отметим одну особенность подзапросов с ALL. Оказывается, что если подзапрос дает пустой результат, то по стандарту SQL сравнение считается истинным, хотя в некоторых СУБД может получиться другой результат. На этом примере видно, что иногда запросы дают формально правильный, но неожиданный для пользователя результат. Очень полезно проверять сложные запросы в режиме диалога на простых тестовых данных.

Подзапросы могут использоваться также в качестве вычисляемых полей. Например, запрос к БД поставщиков

SELECT Sname,

(SELECT COUNT(*) FROM SP

WHERE S.S# = SP.S#) AS Number

FROM S

выводит таблицу с полями Sname и Number, в которой для каждого поставщика указывается количество его поставок.

Дата добавления: 2014-01-07