4.3. Соотнесенные подзапросы.

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

Пример 31.

Рассчитать стоимость блюд на основе молока.

SELECT b.Блюдо, ( SELECT SUM (s.Вес*n.Цена/1000)

FROM Состав s JOIN Наличие n ON s.Продукт = n.Продукт

WHERE s.Блюдо = b.ID_Блюда) + b.Труд as Стоимость

FROM Блюда b

WHERE Основа = 'Молоко';

Результат
Блюдо	Стоимость
Сметана	28,2671
Творог	19,9821
Суп молочный	35,9746
Сырники	25,0069
Вареники ленивые	24,1267
Суфле из творога	36,7818
Крем творожный	22,8368
Молочный напиток	45,4615

Такой подзапрос отличается от рассмотренного выше, тем, что вложенный подзапрос не может быть обработан прежде, чем будет обрабатываться внешний подзапрос. Это связано с тем, что вложенный подзапрос зависит от значения b.ID_Блюда, а оно изменяется по мере того, как система проверяет различные строки таблицы Блюда. Следовательно, с концептуальной точки зрения обработка осуществляется следующим образом:

1. Система проверяет первую строку таблицы Блюда. Предположим, что это строка удовлетворяет условию Основа = 'Молоко'

2. Теперь система может передать b.ID_Блюда в подзапрос и вычислить стоимость блюда.

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

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

Пример 32.

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

SELECT С1.Продукт

FROM Состав С1

WHERE С1.Продукт NOT IN (SELECT С2.Продукт

FROM Состав С2

WHERE С2.Блюдо <> С1.Блюдо);

Данный запрос возвращает пустую таблицу, так нет таких продуктов в БД.

Действие этого запроса можно пояснить следующим образом: "Поочередно для каждой строки таблицы Состав, скажем С1, выделить значение номера продукта (С1.Продукт) и номер блюда (С1.Блюдо), в котором этот продукт используется. А затем проверить, не встречается ли этот продукт в списке продуктов (С2) необходимых для приготовления других блюд (<>С1.Блюдо). Если не встречается, то выводим его".

Отметим, что в этой формулировке должен быть использован по крайней мере один псевдоним - либо С1, либо С2.

Данный запрос может быть реализован и по-другому.

SELECT Продукт

FROM Состав

GROUP BY Продукт

HAVING COUNT (DISTINCT Блюдо) = 1;

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

Результат
Продукт
1	5
2	5
3	19
4	4
5	11
6	10
7	11
8	6
9	7
10	8
11	8
12	13
13	5
14	8
15	9
16	17
17	2

SELECT Продукт , COUNT (DISTINCT Блюдо)

FROM Состав

GROUP BY Продукт;

И получить результат: