Базы Данных - Сибилев, 2007

		201
S1	P2		1200
S3	P9		600
S6	P5		8000
S7	P3		1000
S7	P6		2200
S7	P8		2500
S8	P1		2600

Пример 2. Получить значения номеров деталей, суммарные объёмы поставок каждой из которых, выполненные поставщиком S7, превосходят

500 штук.

SELECT Pnum Деталь, SUM(Qt) Объём

FROM SPJ

WHERE Snum = 'S7'

GROUP BY Pnum

HAVING SUM(Qt) > 500;

Деталь	Объём
P3	1000
P6	2200
P8	2500

Другая, может быть, менее очевидная формула такова:

SELECT PNUM Деталь, SUM(QT) Объём

FROM SPJ

GROUP BY SNUM, PNUM

HAVING SNUM = 'S7' AND SUM(QT) > 500;

7.2.12 Вложение запросов

Оператор SELECT производит неименованную таблицу. На неё нельзя ссылаться из другого оператора. Однако какой-то механизм управ-

ления работой операторов в зависимости от результатов выборки необхо-

дим. В современном SQL это механизм вложения запросов.

Вложенным запросом или подзапросом называется оператор

SELECT, включённый в формулу другого (включающего) оператора.

202

Подзапросы обычно используются в предикатах. Это означает, что подзапрос может быть частью другого подзапроса.22 Стандарт SQL1 до-

пускает только такое применение. Стандартом же SQL2 разрешается, кро-

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

ний, в предложении FROM оператора выборки данных и в конструкторах значений строк. Познакомимся с некоторыми деталями механизма вложе-

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

Подзапрос в предложении FROM определяет часть источника дан-

ных для включающего оператора выборки. В процессе обработки вклю-

чающего запроса он исполняется один раз – при формировании F-таблицы.

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

ное только внутри включающего запроса. На это имя можно ссылаться во всех предложениях включающего запроса как на имя таблицы. Один при-

мер использования подзапроса в предложении FROM мы уже видели (см.

п. 7.2.9, пример 1, вариант 3).

Пример 1. Получить все сведения о поставляемых деталях и общие объёмы поставок этих деталей.

Может показаться, что требуемый результат выдаст оператор

SELECT P.*, SUM(Qt)

FROM P JOIN SPJ

GROUP BY P.Pnum;

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

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

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

203

таблицей P. Нужную таблицу можно получить непосредственно при обра-

ботке предложения FROM.

SELECT P.*, SUMQ.SQT

FROM P JOIN (

SELECT Pnum, SUM(Qt) AS SQT

FROM SPJ

GROUP BY Pnum

) AS SUMQ;

Из нашей учебной БД будут извлечены следующие данные:

Pnum	Pnam	We	Co	Ci	SQT
P1	корпус	300	белый	Томск	2700
P7	кнопка	10	оранжевый	Асино	200
P4	корпус	800	синий	Лесото	280
P9	лампочка	1	красный	Томск	950
P5	панель	100	серый	Асино	8000
P6	кинескоп	1200	черный	Яя	2200
P2	разъём	3	чёрный	Яя	1200
P8	панель	450	розовый	Асино	2800
P3	кнопка	5	красный	Томск	2000

Попытайтесь записать другие варианты формул операторов выборки,

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

Подзапросы в предикатах предложений WHERE и HAVING

используются для управления процессом выборки данных. Далее в настоящем разделе мы будем для определённости говорить о предикатах предложения WHERE. Однако всё нижесказанное в равной мере относится и к предикатам предложения HAVING.

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

Стандарт допускает использование подзапросов в любых предикатах. Однако существуют некоторые ограничения. Укажем

204

важнейшие из них, имея в виду приведённое нами определение предиката

SQL1.23

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

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

− В предикате квантифицированного сравнения и предикате IN

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

− В предикате EXISTS можно использовать табличный подзапрос,

возвращающий таблицу из любого количества столбцов и строк.

Подзапрос может ссылаться только на столбцы собственного источ-

ника данных, а может, кроме того, содержать ссылки на столбцы источни-

ка данных включающего запроса (внешние ссылки). В первом случае под-

запрос называется простым, во втором – коррелированным или связанным.

Существуют важные различия в порядке обработки простого и коррелиро-

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

Простой подзапрос обрабатывается один раз. Возвращённая подза-

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

дой строке F-таблицы.

Пример 2. Вернёмся к примеру 1 из п. 7.2.9 и запишем формулу оператора выборки, основанную на следующем словесном представлении:

«Получить значения атрибута Jnam из таких кортежей отношения J,

в которых значения атрибута J.Jnum принадлежат множеству значений ат-

рибута SPJ.Jnum из таких кортежей отношения SPJ, в которых SPJ.Snum = 'S1'».

В соответствии с этим представлением достаточно однажды постро-

ить нужное множество значений SPJ.Jnum, а затем для каждой строки таб-

23 Аналогичные ограничения есть и в SQL2. Их отличия обусловлены различиями в определениях предиката.

205

лицы J выполнить проверку принадлежности значения J.Jnum этому мно-

жеству. В переводе на SQL это выглядит так:

SELECT Jnam

FROM J

WHERE Jnum IN (

SELECT DISTINCT Jnum

FROM SPJ

WHERE Snum = 'S1'

);

Обрабатывая предложение WHERE включающего запроса, система прежде всего исполнит подзапрос в предикате IN. Он возвратит значения номеров изделий, для которых поставщик S1 выполнял поставки деталей:

Jnum

J3

J5

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

проса.

Замечание 1. Параметр DISTINCT в подзапросе можно было бы не использовать. Тогда результат содержал бы дубликат первой строки. Од-

нако это никак не повлияло бы на результаты проверки.

Замечание 2. Имя Jnum во включающем и вложенном запросах от-

носится к столбцам разных таблиц, но мы не использовали префиксы для уточнения имён. В этом нет необходимости, т.к. обрабатывая подзапрос,

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

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

206

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

Пример 3. Рассмотрим ещё одну возможную словесную формули-

ровку запроса о наименованиях изделий: «Получить значения атрибута

Jnam из таких кортежей отношения J, для которых в отношении SPJ суще-

ствуют кортежи со значениями SPJ.Jnum, совпадающими с текущим зна-

чением J.Jnum и значениями SPJ.Snum, равными S1».

Эта формулировка предполагает проверку факта существования со-

ответствующих строк в таблице SPJ для каждой строки таблицы J. Такую проверку может выполнить предикат EXISTS.

SELECT Jnam

FROM J

WHERE EXISTS (

SELECT *

FROM SPJ

WHERE SPJ.Jnum = J.Jnum

AND Snum = 'S1'

);

В этом случае префиксы имён столбцов необходимы, поскольку внешняя и внутренняя ссылки в подзапросе указывают на одноимённые столбцы разных таблиц.

Обычно в целевом списке подзапроса предиката EXISTS использу-

ется спецификатор *. Хотя стандарт не запрещает явное указание имён столбцов, это не имеет смысла, т.к. значения столбцов не используются при вычислении значения предиката. Оно зависит лишь от наличия или от-

сутствия строк в выходной таблице подзапроса.

На практике предикат EXISTS используется с коррелированными подзапросами. Формально его можно использовать и с простым подзапро-

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

207

Пример 4. Получить номера, наименования и города размещения де-

талей, суммарные объёмы поставок которых максимальны.24

Требуемые данные можно получить только из таблицы P, но для выбора нужных строк придётся использовать данные из таблицы SPJ. Вы-

полняя запрос средствами «бумажной» технологии, мы, скорее всего, ис-

пользовали бы такой план.

–Создать вспомогательную таблицу с заголовком (номер детали,

суммарный объём поставок).

–Для каждого значения номера детали из таблицы SPJ вычислить сум-

марный объём поставок и заполнить вспомогательную таблицу.

–Выбрать значения номеров деталей из тех строк вспомогательной таблицы, в которых значение суммарного объёма поставок макси-

мально.

–С выбранными значениями номеров деталей войти в таблицу P и по-

лучить значения столбцов Pnum, Pnam, Ci.

Приведённая ниже формула SQL точно описывает логику этой про-

цедуры.

SELECT P.Pnum AS Номер,

P.Pnam AS Наименование,

P.Ci AS Город

FROM P

WHERE P.Pnum IN

( SELECT Pnum

FROM ( SELECT Pnum, SUM(Qt) AS Sumqt

FROM SPJ

GROUP BY Pnum

) AS TQt

WHERE

NOT EXISTS

24 Ни одна из приведённых ниже формул этого запроса не удовлетворяет требованиям SQL1.

208

( SELECT *

FROM TQt AS Y

WHERE Y.Sumqt > TQt. Sumqt

)

);

Вспомогательная таблица будет построена подзапросом в предложе-

нии FROM подзапроса предиката IN. Ей будет присвоено локальное имя

TQt.

TQt

Pnum	Sumqt
P1	2700

P7	200
P2	1200
P9	950
P5	2800
P6	2800
P8	2800
P4	280
P3	2000

Затем на каждой строке этой таблицы будет обрабатываться подза-

прос в предикате NOT EXISTS предложения WHERE подзапроса преди-

ката IN. В выходную таблицу подзапроса первого уровня вложения попа-

дут значения Pnum из тех строк таблицы TQt, в которых значение суммар-

ного объёма поставок Sumqt максимально. Вот результат, произведённый этим подзапросом:

Pnum

P5

P8

P6

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

лицы P.

Результат этих манипуляций таков:

Номер Наименование Город

P5

панель

Асино

		209
P8	панель		Асино
P6	кинескоп		Яя

Другая формула запроса приведена ниже.

SELECT P.Pnum AS Номер,

P.Pnam AS Наименование,

P.Ci AS Город

FROM P

WHERE (

SELECT SUM(Qt)

FROM SPJ AS X

WHERE P.Pnum = X.Pnum

)

>= ALL

(

SELECT SUM(Qt)

FROM SPJ AS Y

GROUP BY Y.Pnum

);

Простой подзапрос в правой части предиката квантифицированного сравнения исполняется один раз и производит множество значений сум-

марных объёмов поставок деталей. Оно используется для вычисления зна-

чений предиката в процессе дальнейшей обработки запроса.

Коррелированный подзапрос в левой части предиката для текущей строки таблицы P вычисляет значение суммарного объёма поставок соот-

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

тем выбирается следующая строка таблицы и вновь исполняется коррели-

рованный подзапрос.

210

Приведём ещё одну, возможно, наиболее наглядную формулу запро-

са:

SELECT P.Pnum AS Номер,

P.Pnam AS Наименование,

P.Ci AS Город

FROM P,

(

SELECT Pnum, SUM(Qt) AS Sumqt

FROM SPJ

GROUP BY Pnum

) AS TQt

WHERE P.Pnum = TQT.Pnum

AND Sumqt = (SELECT MAX(Sumqt) FROM TQT);

В логике этой формулы читатель разберётся самостоятельно. Заме-

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

суммарные объёмы поставок которых максимальны, а также значение мак-

симального объёма». Как? Почему так же нельзя изменить предыдущие формулы?

Как видно из приведённых примеров, механизм вложения запросов – это мощное выразительное средство. С его помощью можно представить запрос любой сложности в виде одного оператора SELECT. Все запросы,

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

прос без использования подзапросов сформулировать невозможно. Поче-

му?