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

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

SELECT *
FROM Customers C
WHERE '1999-10-03' IN (
   SELECT odate
   FROM Orders O
   WHERE O.cnum = C.cnum
)

Результат:

CNUM  CNAME                CITY       RATING  SNUM
----- -------------------- ---------- ------- -----
2001  ТОО Рога и копыта    Москва     100     1001
2002  AО Бендер и К        Одесса     200     1003
2003  Фирма ХХХ            Рязань     200     1002
2007  ОАО "ООО"            ТОМСК      100     1004
2008  ОАО "Валют-транзит"  Караганда  300     1007

Как это все работает?

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

Процедура оценки, выполняемая связанным запросом:

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

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

SELECT C.*
FROM Customers C, Orders O
WHERE C.cnum = O.cnum AND
	O.odate = '1999.10.03'

Но, если один и тот же заказчик сделал бы 2 и более заказов, то он появился в результате несколько раз. Конечно этого можно избежать использовав DISTINCT, но это не эффективное решение. Оператор IN в варианте с подзапросом не делает различия между значениями, которые выбираются подзапросом один раз и которые выбираются многократно. Следовательно DISTINCT необязателен.

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

SELECT snum, sname
FROM Salespeople S
WHERE 1 < (
   SELECT COUNT(*)
   FROM Customers c
   WHERE c.snum = s.snum
)

Результат:

snum  sname
----- -------
1001  Иванов
1002  Петров

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

SELECT *
FROM Orders O
WHERE amt > (
   SELECT AVG( amt )
   FROM Orders O1
   WHERE O1.cnum = O.cnum
)

Результат:

ONUM  ODATE                    AMT        CNUM  SNUM
----- ------------------------ ---------- ----- -----
3009  1999-10-04 00:00:00.000  1713.2300  2002  1003
3010  1999-10-06 00:00:00.000  1309.9500  2004  1002
3011  1999-10-06 00:00:00.000  9891.8800  2006  1001

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

Ну вот собственно и все. Поздравляю Вас овладением большим куском из понятий SQL - связанного подзапросом. Следующая - описание некоторых специальных операторов SQL. Они используют подзапросы как аргумент, как это делает IN, но в отличие от IN, они могут использоваться только с подзапросами. Это операторы EXISTS, ANY, ALL.