Понимание SQL

Грубер Мартин

* Напишите запрос который вывел бы имена и оценки всех заказчиков которые имеют усредненые порядки.

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

Глава 11. СООТНЕСЕННЫЕ ПОДЗАПРОСЫ

В ЭТОЙ ГЛАВЕ, МЫ ПРЕДСТАВИМ ВАС ТИПУ подзапроса о котором мы не говорили в Главе 10 - посвященной соотнесенному подзапросу. Вы узнаете как использовать соотнесенные подзапросы в предложениях запросов WHERE и HAVING. Сходства и различия между соотнесенными подзапросами

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

КАК СФОРМИРОВАТЬ СООТНЕСЕННЫЙ ПОДЗАПРОС

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

Например, имеется один способ найти всех заказчиков в порядках на 3-е Октября (вывод показывается в Таблице 11.1):

SELECT *

FROM Customers outer

WHERE 10/03/1990 IN

( SELECT odate

FROM Orders inner

WHERE outer.cnum=inner.cnum );

КАК РАБОТАЕТ СООТНЕСЕННЫЙ ПОДЗАПРОС

В вышеупомянутом примере, "внутренний"(inner) и "внешний"(outer), это псевдонимы, подобно обсужденным в Главе 9. Мы выбрали эти имена для большей ясности; они отсылают к значениям внутренних и внешних запросов, соответственно. Так как значение в поле cnum внешнего запроса меняется, внутренний запрос должен выполняться отдельно для каждой строки внешнего запроса. Строка внешнего запроса для которого внутренний

SQL Execution Log

SELECT * FROM Customers outer WHERE 10/03/1990 IN

(SELECT odate FROM Orders inner WHERE outer.cnum=inner.cnum);

cnum	cname	city	rating	snum
2001	Hoffman	London	100	1001
2003	Liu	San Jose	200	1002
2008	Cisneros	San Jose	300	1007
2007	Pereira	Rome	100	1004

Таблица 11.1: Использование соотнесенного подзапроса

запрос каждый раз будет выполнен, называется - текущей строкой-кандидатом.

Следовательно, процедура оценки выполняемой соотнесенным подзапросом - это:

* Выбрать строку из таблицы именованной в внешнем запросе. Это будет текущая строка-кандидат.

* Сохранить значения из этой строки-кандидата в псевдониме с именем в предложении FROM внешнего запроса.

* Выполнить подзапрос. Везде, где псевдоним данный для внешнего запроса найден (в этом случае "внешний" ), использовать значение для текущей строки-кандидата. Использование значения из строки-кандидата внешнего запроса в подзапросе называется - внешней ссылкой.

* Оценить предикат внешнего запроса на основе результатов подзапроса выполняемого в шаге 3. Он определяеть - выбирается ли строка-кандидат для вывода.

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

В вышеупомянутом примере, SQL осуществляет следующую процедуру:

* Он выбирает строку Hoffman из таблицы Заказчиков.

* Сохраняет эту строку как текущую строку-кандидат под псевдонимом - "внешним".

* Затем он выполняет подзапрос. Подзапрос просматривает всю таблицу Порядков чтобы найти строки где значение cnum поле - такое же как значение outer.cnum, которое в настоящее время равно 2001, - поле cnum строки Hoffmanа. Затем он извлекает поле odate из каждой строки таблицы Порядков для которой это верно, и формирует набор значений поля odate.

* Получив набор всех значений поля odate, для поля cnum=2001, он проверяет предикат основного запроса чтобы видеть имеется ли значение на 3 Октября в этом наборе. Если это так(а это так), то он выбирает строку Hoffmanа для вывода ее из основного запроса.

* Он повторяет всю процедуру, используя строку Giovanni как строку-кандидата, и затем сохраняет повторно пока каждая строка таблицы Заказчиков не будет проверена.

Как вы можете видеть, вычисления которые SQL выполняет с помощью этих простых инструкций - это полный комплекс. Конечно, вы могли бы решить ту же самую проблему используя обьединение, следующего вида (вывод для этого запроса показывается в Таблице 11.2):

SELECT *

FROM Customers first, Orders second

WHERE first.cnum=second.cnum

AND second.odate=10/03/1990;

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

SQL Execution Log

SELECT * FROM Customers first, Orders second

WHERE first.cnum=second.cnum (SELECT COUNT (*)

FROM Customers WHERE snum=main.snum;

cnum	cname
1001	Peel
1002	Serres

Таблица 11. 2 Использование обьединения вместо соотнесенного подзапроса