Вложенные запросы

В SQL множество значений может задаваться не только перечислением входящих в него элементов, но и запросом. Так же, как и список элементов, запрос заключается в круглые скобки. Полученное множество используется так же, как и рассмотренное ранее. Множество содержит атомарные элементы, поэтому формирующий его запрос должен выбирать только один атрибут. В результате запроса множество может быть пустым, содержать один элемент или более одного. Если множество содержит один элемент, оно может рассматриваться как обычное значение, и для него определены соответствующие операции сравнения. Для множества, содержащего более одного элемента, эти операции, естественно, недопустимы, определена лишь операция принадлежности элемента множеству. Заметим, что в результате запроса может образоваться несколько одинаковых значений и, как и в случае обычного запроса, по умолчанию все они включаются в выборку. То есть, на самом деле речь идет не о множестве, а о мультимножестве (комплекте). Использование варианта Distinct приводит к исключению повторяющихся элементов.

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

Пример

Найти все заказы, которые выполняет продавец Сидоров:

Select * From Заказы

Where ном_прод=(Select * From Продавцы Where имя_прод=’Сидоров’);

Запрос корректен, если есть ровно один продавец Сидоров. Если такого нет – результат не определен, если их более одного – результат ошибочен.

Найти все заказы, сумма которых больше, чем средняя за 4 октября:

Select * From Заказы

Where сумма>(Select AVG(сумма) From Заказы Where дата=’04.10’);

Найти все заказы для продавцов из Москвы:

Select * From Заказы

Where ном_прод in (Select ном_прод From Продавцы

Where город=’Москва’);

Такой же результат можно получить, используя операцию соединения:

Select a.* From Заказы a, Продавцы b

Where a.ном_прод = b.ном_прод and город=’Москва’;

Найти комиссионные всех продавцов, обслуживающих покупателей из Москвы:

Select комиссия From Продавцы

Where ном_прод in (Select ном_прод From Заказы

Where ном_пок in (Select ном_пок From Покупатели

Where город=’Москва’));

Определить количество покупателей, имеющих значимость, превышающую среднюю для покупателей из Тулы:

Select значимость, COUNT(Distinct ном_пок) From Покупатели

Having значимость > (Select AVG(значимость) From Покупатели

Where город=’Тула’);

Конец примера

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

Связанные запросы

Для реализации вложенного запроса может потребоваться информация из таблиц внешнего запроса. Запросы такого типа называются связанными.

Пример

Выбрать всех покупателей, которые сделали заказы 3 октября:

Select * From Покупатели a

Where ’03.10’ in (Select дата From Заказы b Where a.ном_пок=b.ном_пок);

Конец примера

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

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

2. Сохранить ее под псевдонимом, указанном во внешней фразе From.

3. Выполнить подзапрос. Используемые в подзапросе значения строки-кандидата называются внешними ссылками.

4. Оценить результат внешнего запроса на основании результата подзапроса.

5. Повторить процедуру для следующих строк внешней таблицы.

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

Пример

Реализовать предыдущий пример, используя соединение:

Select Distinct a.* From Покупатели a, Заказы b

Where a.ном_пок=b.ном_пок and дата=’03.10’;

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

Выбрать имена и номера продавцов, обслуживающих более одного покупателя:

Select ном_прод, имя_прод From Продавцы a

Where 1<(Select COUNT(Distinct ном_пок) From Заказы

Where ном_прод=a.ном_прод);

Конец примера

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

Пример

Для каждого покупателя выбрать все заказы на сумму, большую средней суммы его заказов:

Select * From Заказы a

Where сумма>(Select AVG(сумма) From Заказы b

Where a.ном_пок=b.ном_пок);

Конец примера

Так же, как и в случае вложенного запроса, связанный подзапрос может использоваться во фразе Having, если условие накладывается на группу записей.