Материал: Graber_Martin_-_SQL_dlya_prostykh_smertnykh_-_2014

Глава 10. Вло;щ:ение запросов

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

ми. В большинстве случаев это можно сделать, размещая один запрос внутри преди­

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

определения истинности или ложности предиката. Из этой главы вы узнаете, какого

рода операторы могут использовать подзапросы, как их применять с DISTINCT, агре­ гатными функциями и выражениями вывода; как использовать подзапросы с предло­ жением HAVING и получать указатели правильного способа применения

подзапросов.

Как выполняются подзапросы?

SQL позволяет вкладывать запросы друг в друга. Обычно внутренний запрос гене­

рирует зна<1ения, которые тестируются на предмет истинности предиката. Предполо­

жим, известно имя, но мы не знаем значения поля snuш для продавца Motica.

Необходимо извлечь все ее заказы из таблицы Orders. Перед вами один из способов

решения этой проблемы (выходные данные представлены на рис. 10.1 ):

SELECT •

FROM Orders

WHERE snum

(SELECT snum

FROM Salespeople

WHERE sname = 'Motika');

Рис. 10.1. использование подзапроса

94

Как вьтолняются подзапросы?

Чтобы оценить внешний (основной) запрос, SQL прежде всего должен оценить внутренний запрос (или подзапрос) в предложении WHERE. Эта оценка осуществля­ ется так, как если бы запрос был единственным: просматриваются все строки таблицы Salespeople и выбираются все строки, для которых значение поля sname равно Motika, для таких строк выбираются значения поля snum.

В результате выбранной оказывается единственная строка с snum = 1004. Одна­ ко вместо простого вывода этого значения SQL подставляет его в предикат основ­

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

образом:

WHERE snum = 1004

WHERE snum = 1004

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

зволяет использовать его во множестве вариантов.

Значения, получаемые в процессе выполнения подзапросов

Весьма полезно то, что подзапрос в данном случае возвращает одно и только одно

значение. Если вместо WHERE sname = 'Motika' подставить WHERE city = 'London', то

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

сти, что приводит к оценке запроса как ошибочного.

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

ходными данными подзапроса является только одна строка. Если применяется

подзапрос, не генерирующий никаких значений, то это не является ошибкой, одна­

ко в настоящем случае и основной запрос не даст никаких выходных данных. Под­

запросы, не генерирующие никаких выходных данных (или NULL-выxoд),

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

имеющий значение "неизвестно" (unknown). Предикат со значением "неизвестно" работает как и предикат со значением "ложь": основной запрос не выбирает ни од-

95

Глава 10. Вложение заt~росов

ной строки (информацию по поводу unknoun-пpeдикaтa см. в главе 5). Попытку ис­

пользовать нечто вроде

SELECT •

FROM Orders

WHERE snum =

(SELECT snum

FROM Salespeople

WHERE city = 'Barcelona');

нельзя признать удачной.

Если в городе Barcelona есть только один продавец (salesperson) Mr.Rifkin, то подза­ прос выберет единственное значение snum, и, следовательно, будет воспринят. Однако это справед.1иво только для текущих данных. В результате изменения состава данных табли­ цы Salespeople вполне реальной может стать ситуация, кшда в городе (city) Barcelona поя­

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

следовательно, запрос будет признан ошибочным.

DISTINCT с подзапросами

В некоторых случаях можно использовать DISTINCT для гарантии получения

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

найти все заказы (orders), с которыми работает продавец, обслуживающий покупателя Hoffman (cnнm = 2001 ). Вот один из вариантов решения этой задачи (выходные дан­ ные представлены на рис. 10.2):

SELECT •

FROM Orders

WHERE snum =

(SELECT DISTINCT snum

FROM Orders

WHERE cnum = 2001);

Подзапрос выясняет, что значение поля snum для продавца, обслуживающего

Hoffman, равно l 00 l; следовательно, основной запрос извлекает из таблицы Orders

всех покупателей с тем же значением поля snum. Поскольку каждый покупатель об­ служивается только одним продавцом, каждая строка таблицы Orders с данным значе­ нием cnum имеет то же значение поля snum. Однако, поскольку может быть любое количество таких строк, подзапрос может дать в результате множество значений snum (возможно, одинаковых) для данного cnum. Если бы подзапрос возвращал более одно­ го значения, получилась бы ошибка в данных. Аргумент DISTINCT предотвращает та­

кую ситуацию.

96

Как вьтолняются подзапросы?

Рис. 10.2. Использование DISTINCT с целью получить единственное значение в результате

выплнения подзапроса

Альтернативный способ действия - сослаться в подзапросе на таблицу Customers, а не на таблицу Orders. Поскольку cnuш - первичный ключ таблицы Customer, есть гарантия, что в результате выполнения запроса будет получено единственное значе­ ние. Однако, если пользователь имеет доступ к таблице Orders, а не к таблице Customers, остается вариант, рассмотренный ранее. (SQL располагает механизмами определения того, кто и какие привилегии имеет при работе с таблицами. Эти вопросы рассмотрены в главе 22.)

Надо помнить, что приведенный в предыдущем примере прием приемлем только

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

значения. Такая ситуация является скорее исключением, а не правилом для реляцион­

ных баз данных.

Предикаты с подзапросами являются неперемещаемыми

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

таком виде:

SELECT •

FROM Orders

WHERE (SELECT DISTINCT snum

FROM Orders

97

Глава 10. Вложение запросов

WHERE cnum 2001)

snum;

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

выполнения подзапросов.

Использование агрегатных функций в подзапросах

Одним из видов функций, которые автоматически выдают в результате единствен­ ное значение для любого количества строк, конечно, являются агрегатные функции. Любой запрос, использующий единственную агрегатную функцию без предложения GROUP ВУ, дает в результате единственное значение для использования его в основ­ ном предикате. Например, нужно узнать все заказы, стоимость которых превышает среднюю стоимость заказов за 4 октября 1990 г. (выходные данные представлены на

рис. 10.3):

SELECT *

FROM Orders

WHERE amt >

(SELECT AVG (amt)

FROM Orders

Рис. 10.3. Выбор заявок, в которых указано количество, превышающее среднее значение для заявок, поступивших 4 октября 1990 г.

98