Коррелированные подчиненные запросы *

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

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

SELECT CITY FROM OFFICES WHERE SALES < (SELECT AVG (TARGET) FROM OFFICES)

CITY

Denver Atlanta

В этом загфосе было бы неразумно выполнять подчиненный запрос пять раз (один раз для каждого офиса). Усредненный план не изменяется; он абсолютно не зависит от проверяемого в настоящий момент офиса. Следовательно, подчиненный запрос можно выполнить только один раз и, получив усредненный план ($550000), преобразовать главный запрос к виду:

SELECT CITY FROM OFFICES WHERE SALES < 550000.00

В коммерческих СУБД это упрощение используется всякий раз, когда есть такая возможность, чтобы уменьщить объем работы, необходимой для выполнения запроса. Но когда подчиненный запрос содержит внешнюю ссылку, данное упрощение применять нельзя, как показано на следующем примере:

Вывести список офисов, в которых плановые объемы продаж превышают суммарные планы служащих, работающих в них

SELECT CITY FROM OFFICES WHERE TARGET > (SELECT SOM(Q0OTA)

FROM SALESREPS

WHERE REP OFFICE = OFFICE)

CITY

Chicago Los Angeles

В разных строках таблицы offices, проверяемой предложением where главного запроса, столбец office (который является внешней ссылкой в подчиненном запросе) имеет различные значения. Поэтому подчиненный запрос должен выполняться пять раз - один раз для каждой строки таблицы offices. Подчиненный запрос, содержащий внешнюю ссылку, называется коррелированным подчиненным запросом.

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

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

Так как подчиненный запрос может содержать внешние ссылки, вероятность появления неоднозначных ссылок на имена столбцов в подчиненном запросе еще выше, чем в главном запросе. Если в подчиненном запросе присутствует неполное имя столбца, СУБД должна определить, относится ли оно к таблице гфедложения from самого подчиненного запроса или к предложению from запроса, содержащего подчиненный запрос. Чтобы минимизировать возможность путаницы, в SQL всегда предполагается, что ссьмка на столбец в подчиненном запросе относится к ближайшему возможному предложению from. Для иллюстрации приведем пример, где одна и та же таблица используется и в главном, и в подчиненном запросах:

Вывести список руководителей старше 40 лет, у которых есть служащие, опережающие план.

select name

from salesreps where age > 40

and empl num in (select manager

from salesreps where sales > qoota)

name

Sam Clark Larry Fitch

Столбцы manager, quota и sales в подчиненном запросе являются ссылками на таблицу salesreps в предложении from самого подчиненного запроса; СУБД не интерпретирует их как внешние ссылки, и подчиненный запрос не является коррелированным. Как уже говорилось ранее, СУБД в данном случае может сначала выполнить подчиненный запрос: найти служащих, опережающих план, и составить список, содержащий идентификаторы их руководителей. Затем СУБД может приступить к вьпюлне-нию главного запроса и отобрать имена руководителей из полученного списка.

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

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

select name

from salesreps mgrs where age > 40

AND MGRS.EMPL NOM IN (SELECT MANAGER

FROM SALESREPS EMPS WHERE EMPS.SALES > EMPS.QUOTA

AND EMPS. REP OFFICE <> MGRS.IP OFFICE)

NAME

Sam Clark Larry Fitch

Теперь копия таблицы salesreps в главном запросе имеет псевдоним mgrs, а копия в подчиненном запросе - псевдоним emps Подчиненный запрос содержит одно дополнительное условие отбора, требующее, чтобы идентификатор офиса служащего не был равен идентификатору офиса руководителя Полное имя столбца mgrs. rep 0ffice в подчиненном запросе - это внешняя ссылка, и данный подчиненный запрос является коррелированным.

Подчиненные запросы в предложении HAVING *

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

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

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

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

FROM ORDERS)

NAME AVG(AMOUNT)

Bill Adams $8,895.50

Sue Smith $15,000.00

Tom Snyder $22,500.00

Ha рис 9.7 приведена схема выполнения этого запроса Подчиненный запрос вычисляет среднюю стоимость по всем заказам. Это простой подчиненный запрос, не содержащий внешних ссььлок, поэтому искомая средняя стоимость вычисляется один раз, а затем многократно используется в предложении having. Главный запрос просматривает строки таблицы orders, отыскивая все заказы на товары компании АС1, и грухпхирует их по именам служащих. Затем предложение having сравнивает среднюю стоимость по каждой группе заказов со средней стоимостью по всем заказам, вычисленной ранее. Если средняя стоимость по группе больше, чем общая средняя стоимость, то данная группа строк сохраняется, если нет, то группа строк исключается. И наконец, предложение select создает для каждой группы итогову10 строку, содержащую имя служащего и среднюю стоимость принятых им заказов