Таблица SALESBEPS

JOIN }<-

Таблица с группировкой

Таблица ORDERS

Таблица ORDEflS

Подчиненный запрос

ТТ--~-г

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

Если слегка изменить предыдущий запрос, то подчиненный запрос в предложении having станет коррелированным:

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

SELECT NAME, AVG(AMOONT) IROM SALESREPS, ORDERS WHERE EMPL NOM = REP

AND MFR = ACI GROUP BY NAME, EMPL NOM HAVING AVG(AMOONT) >= (SELECT AVG(AMOONT)

FROM ORDERS WHERE REP = EMPL NDM)

NAME

AVG (AMOUNT)

Bill Adams $8,895.50

Dan Roberts $3,978.00

Sue Smith $15,000.00

Tom Snyder $22,500.00

В последнем примере подчиненный запрос должен вычисл5пъ среднюю стоимость заказов служащего, чья группа строк проверяется в настоящий момент предложением having. Подчиненный запрос считьшает заказы, принятые этим служащим, с помошью вне1Ш1ей ссылки empl num. Эта внешняя ссылка является правильной, поскольку столбец empl num имеет одинаковые значения во всех строках группы, созданной главным запросом.

Подчиненные запросы - резюме

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

Подчиненный запрос является запросом внутри запроса . Такие запросы содержатся в одном из условий отбора в предложении where либо having

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

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

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

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

При проверке на принадлежность результатам подчиненного загфоса (предикат in) значение выражения проверяется на равенство одному из множества значений, которые возвращаются подчиненным загфосом.

Проверка на существование (предикат exists) позволяет выяснить, возвращает ли подчиненный запрос какие-нибудь значения.

При многократном сравнении (предикаты any и all) значение выражения сравнивается посредством одного из операторов сравнения со всеми значениями, отобранными подчиненным запросом, чтобы выяснить, вьшолняется ли условие сравнения для некоторых либо для всех значений.

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

Сложные запросы в SQi2

До сих пор (в главах 6-9) мы с вами говорили о базовых возможностях языка SQL, поддерживаемых большинством его современных реализаций. Комплекс этих возможностей: выбор столбцов в предложении select, отбор строк по критерию,

заданному в предложении where, объединение таблиц, указанных в предложении from, вычисление итоговых данных с помощью предложений group by и having и использование подчиненных запросов - все это предоставляет пользователю мощный и эффективный механизм получения и анализа данных Однако профессионалы сталкиваются с рядом серьезных ограничений, затрудняющих или даже делающих невозможным извлечение из базы данных информации некоторых видов а Отсутствие средств принятия решений. Предположим, что вы хотите сгенерировать отчет на основе информации из нащей учебной базы данных, состоящий из двух колонок: в первой выводятся названия всех офисов компании, а во второй для каждого офиса выводится либо его плановый объем продаж на год, либо объем продаж на текущий период - в зависимости от того, какая сумма больще С помощью стандартных средств SQL сделать это трудно. Еще один пример. У вас есть база данных с квартальными объемами продаж каждого офиса (четыре столбца данных для каждого офиса), и вы хотите написать программу, которая выводит перечень офисов и их объемов продаж за указанный квартал Для рещения этой задачи стандартные SQL-запросы тоже подходят плохо Вам придется включить в программу четыре отдельных SQL-запроса (по одному для каждого квартала), и в зависимости от выбора пользователя программа должна направлять СУБД один из этих запросов Конечно, это не так уж сложно сделать, по крайней мере для столь простого примера, но в целом данное ограничение ведет к усложнению прикладных программ за счет переноса в них логики, которую естественнее было бы включить в запросы.

Ограниченное использование подчиненных запросов. На подчиненные запросы стандарт SQL1 накладывает ряд ограничений. Простейщий пример - правило, согласно которому в предложении where подчиненный запрос может стоять только справа от оператора сравнения. Загфос перечислить офисы, для которых сумма плановых объемов продаж служащих превышает план самого офиса наиболее естественно бьшо бы выразить так:

SELECT OFFICE FROM OFFICES WHERE (SELECT SUM(QUOTA) FROM SALESREPS WHERE REP OFFICE = OFFICE) > TARGET

Однако с точки зрения стандарта SQLl эта инструкция неверна. Сравниваемые элементы в ней следует поменять местами:

SELECT OFFICE FROM OFFICES WHERE TARGET > (SELECT SUM(QUOTA) FROM SALESREPS WHERE REP OFFICE = OFFICE)

В таком гфостом примере перевернуть логику нетрудно, но это ограничение не просто доставляет неудобства и не только ведет к ошибкам - оно еще и не позволяет сравнить результаты двух подчиненных запросов

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