Материал: Базы данных и их безопасность. Плотников Д.Г

подзапроса, необходимо отметить еще один важный момент. Первоначальная задача — «Выдать фамилии поставщиков, которые поставляют деталь P2» — может быть эквивалентным образом выражена как запрос с использованием соединения:

SELECT S.ФАМИЛИЯ FROM S, SP

WHERE S.НОМЕР_ПОСТАВЩИКА = SP.НОМЕР_ПОСТАВЩИКА

AND SP.НОМЕР_ДЕТАЛИ = ’P2’;

Пояснение. Соединение S и SP по номерам поставщиков представляет собой таблицу из 12 строк (по одной строке для каждой строки SP), каждая из которых состоит из соответствующей строки SP, дополненной значениями ФАМИЛИЯ, СОСТОЯНИЕ и ГОРОД для поставщика, указываемого значением НОМЕР_ПОСТАВЩИКА в этой строке. Из этих 12 строк только четыре относятся к детали P2. Окончательный результат получается, таким образом, выделением значения ФАМИЛИЯ из этих четырех строк.

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

Подзапрос с несколькими уровнями вложенности

Выдать фамилии поставщиков, которые поставляют, по крайней мере, одну красную деталь.

SELECT ФАМИЛИЯ

FROM S

WHERE НОМЕР_ПОСТАВЩИКА IN

(SELECT НОМЕР_ПОСТАВЩИКА

FROM SP

WHERE НОМЕР_ДЕТАЛИ IN (SELECT НОМЕР_ДЕТАЛИ

FROM Р

WHERE ЦВЕТ = ’Красный’));

266

Результат:

ФАМИЛИЯ

Смит

Джонс

Кларк

Пояснение. Результатом самого внутреннего подзапроса является множество (’P1’, ’Р4’, ’Р6’). Подзапрос следующего уровня в свою очередь дает в результате множество (’S1’, ’S2’, ’S4’). Последний, самый внешний SELECT, вычисляет приведенный выше окончательный результат. Вообще допускается любая глубина вложенности подзапросов.

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

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

b.Напишите эквивалентную формулировку этого же запроса с использованием соединения.

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

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

SELECT ФАМИЛИЯ

FROM S WHERE ’P2’ IN

(SELECT НОМЕР_ДЕТАЛИ

FROM SP

WHERE НОМЕР_ПОСТАВЩИКА = S.НОМЕР_ПОСТАВЩИКА);

Пояснение. В последней строке приведенного запроса неуточненная ссылка на НОМЕР_ПОСТАВЩИКА уточняется неявным образом именем таблицы SP. Другая ссылка явно уточняется именем таблицы S. Этот пример отличается от

267

предыдущих тем, что внутренний подзапрос не может быть обработан прежде, чем будет обрабатываться внешний запрос, поскольку этот внутренний подзапрос зависит от переменной, а именно от S.HOMEP_ПОСТАВЩИКА, значение которой изменяется по мере того, как система проверяет различные строки таблицы S. Следовательно, с концептуальной точки зрения обработка осуществляется следующим образом.

a.Система проверяет первую строку таблицы S. Предположим, что это строка поставщика «S1». Тогда переменная S.HOMEP_ПОСТАВЩИКА в данный момент имеет значение ’S1’, и система обрабатывает внутренний запрос

(SELECT НОМЕР_ДЕТАЛИ

FROM SP

WHERE НОМЕР_ДЕТАЛИ = ’S1’),

получая в результате множество (’Р1’, ’P2’, ’Р3’, ’Р4’, ’Р5’, ’Р6’). Теперь она может завершить обработку для S1. Выборка значения ФАМИЛИЯ для S1, а именно Смит, будет произведена тогда и только тогда, когда ’Р2’ принадлежит этому множеству, что, очевидно, справедливо.

b.Далее система будет повторять обработку такого рода для следующего поставщика и т.д. до тех пор, пока не будут рассмотрены все строки таблицы S.

Такой подзапрос, как в этом примере, называется коррелированным. Коррелированный подзапрос — это такой подзапрос, результат которого зависит от некоторой переменной. Эта переменная принимает свое значение в некотором внешнем запросе. Обработка такого подзапроса, следовательно, должна повторяться для каждого значения переменной в запросе, а не выполняться раз навсегда. Далее будет приведен другой пример коррелированного подзапроса.

Для того чтобы сделать более ясной связь коррелированных подзапросов с внешними запросами, некоторые пользователи любят вводить псевдонимы. Например:

268

SELECT SX.ФАМИЛИЯ FROM S SX

WHERE ’Р2’ IN

(SELECT НОМЕР_ДЕТАЛИ

FROM SP

WHERE НОМЕР_ПОСТАВЩИКА = SX.НОМЕР_ПОСТАВЩИКА);

Вэтом примере псевдонимом является имя SX, введенное во фразе FROM как альтернативное имя таблицы S и используемое далее в качестве явного уточнителя во фразе WHERE подзапроса, а также во фразе SELECT внешнего запроса. Действие приведенного выше полного предложения можно теперь описать более понятно и более точно следующим образом:

—SX — это переменная, областью определения которой является множество записей таблицы S, т. е. переменная, представляющая в любой заданный момент времени некоторую запись таблицы S.

—Поочередно для каждого возможного значения SX выполнить следующее:

—вычислить подзапрос и получить множество номеров деталей, например, Р;

—добавить к результирующему множеству значение SX.ФАМИЛИЯ, если и только если Р2 принадлежит множеству Р.

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

Введение псевдонима никогда не является ошибкой, а иногда оно необходимо.

269

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

Выдать номера поставщиков, которые поставляют, по крайней мере, одну деталь, поставляемую поставщиком S2.

SELECT DISTINCT НОМЕР_ПОСТАВЩИКА FROM SP

WHERE НОМЕР_ДЕТАЛИ IN

(SELECT НОМЕР_ДЕТАЛИ

FROM SP

WHERE НОМЕР_ПОСТАВЩИКА = ’S2’);

Результат:

НОМЕР_ПОСТАВЩИКА

S1

S2

S3

S4

Отметим здесь, что ссылка на SP в подзапросе означает не то же самое, что ссылка на SP во внешнем запросе. В действительности, два имени SP обозначают различные переменные. Чтобы этот факт стал явным, можно использовать псевдонимы:

SELECT DISTINCT SPX.НОМЕР_ПОСТАВЩИКА FROM SP SPX

WHERE SPX.НОМЕР_ДЕТАЛИ IN (SELECT SPY.НОМЕР_ДЕТАЛИ FROM SP SPY

WHERE SPY.НОМЕР_ПОСТАВЩИКА = ’S2’);

270