Связанные подзапросы
Когда Вы используете подзапросы, Вы можете обратиться во вложенном подзапросе к таблицам из внешнего подзапроса. Например, как найти всех заказчиков на 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 Результат: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.
Предыдущая | Следующая | Оглавление Original version: http://www.mjk.msk.ru/~dron/