SELECT comm

FROM Salespeople

WHERE snum IN ( SELECT snum

FROM Customers

WHERE city = London );

Выводимыми для этого запроса, показанного в Рисунке 10.5, являются значения комиссионных продавца Peel (snum = 1001), который имеет обоих заказчиков в Лондоне. Это - только для данного случая. Нет никакой причины чтобы некоторые заказчики в Лондоне не могли быть назначеными к кому-то еще. Следовательно,IN - это наиболее логичная форма чтобы использовать ее в запросе.

=============== SQL Execution Log ==============

SELECT comm

FROM Salespeople

WHERE snum IN

(SELECT snum

FROM Customers

WHERE city = London);

comm

0.12

Рисунок 10.5: Использование IN с подзапросом для вывода одного значения

Между прочим, префикс таблицы для поля city необязателен в предыдущем примере, несмотря на возможную неоднозначность между полями city таблицы Заказчика и таблицы Продавцов.

SQL всегда ищет первое поле в таблице обозначенной в предложении FROM текущего подзапроса. Если поле с данным именем там не найдено, проверяются внешние запросы. В вышеупомянутом примере, city в предложении WHERE означает что имеется ссылка к Customer.city (поле city таблицы Заказчиков).

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

ПОДЗАПРОСЫ ВЫБИРАЮТ ОДИНОЧНЫЕ СТОЛБЦЫ

Смысл всех подзапросов обсужденных в этой главе тот, что все они выбирают одиночный столбец. Это обязательно, поскольку выбранный вывод сравнивается с одиночным значением. Подтверждением этому то, что SELECT * не может использоваться в подзапросе. Имеется исключение из этого, когда подзапросы используются с оператором EXISTS, который мы будем представлять в Главе 12.

ИСПОЛЬЗОВАНИЕ ВЫРАЖЕНИЙ В ПОДЗАПРОСАХ

Вы можете использовать выражение основанное на столбце, анепростосам столбец, в предложении SELECT подзапроса. Это может быть выполнено или с помощью реляционных операторов или с IN. Например, следующий запрос использует реляционный оператор = (вывод показывается в Рисунке 10.6):

SELECT *

FROM Customers

WHERE cnum = ( SELECT snum + 10 0 0 FROM Salespeople WHERE sname = Serres );

SELECT *

FROM Customers

WHERE cnum =

(SELECT snum + 1000

WHERE Salespeople

WHERE sname = Serres

SQL Execution Log

cnum

2002

cname

Giovanni

city Rome

rating

snum

1003

Рисунок 10.6: Использование подзапроса с выражением

Он находит всех заказчиков, чье значение поля cnum равное 1000, выше поля snum Serres. Мы предполагаем, что столбец sname не имеет никаких двойных значений (это может быть предписано или UNIQUE INDEX, обсуждаемым в Главе 17, или ограничением UNIQUE, обсуждаемым в Главе 18); иначе подзапрос может произвести многочисленые значения. Когда поля snum исnum не имеют такого простого функционального значения как например первичный ключ, что не всегда хорошо, запрос типа вышеупомянутого невероятно полезен.

ПОДЗАПРОСЫ В ПРЕДЛОЖЕНИИ HAVING

Вы можете также использовать подзапросы внутри предложения HAVING. Эти подзапросы могут использовать свои собственные агрегатные функции если они не производят многочисленых значений или использовать GROUP BY или HAVING. Следующий запрос является этому примером (вывод показывается в Рисунке 10.7):

SELECT rating, COUNT ( DISTINCT cnum )

FROM Customers

GROUP BY rating

HAVING rating > ( SELECT AVG (rating) FROM Customers WHERE city = San Jose );

=============== SQL Execution Log ============

SELECT rating,count (DISTINCT cnum)

FROM Customers

GROUP BY rating

HAVING rating >

(SELECT AVG (rating)snum + 1000

FROM Custimers

WHERE city = San Jose

rating

200 2

Рисунок 10.7: Нахождение заказчиков с оценкой выше среднего в San Jose

Эта команда подсчитывает заказчиков с оценками выше среднего в San Jose. Так как имеются другие оценки отличные от 300, они должны быть выведены с числом номеров заказчиков которые имели эту оценку.

РЕЗЮМЕ

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

В следующих главах, мы будем разрабатывать подзапросы. Сначала в Главе 11, мы обсудим другой вид подзапроса, который выполняется отдельно для каждой строки таблицы вызываемой во внешнем запросе. Затем, вГлаве12 и 13, мы представим вас нескольким специальным операторам которые функционируют на всех подзапросах, как это делает IN, за исключением когда эти операторы могут использоваться только в подзапросах.

РАБОТА С SQL

1. Напишите запрос, который бы использовал подзапрос для получения всех порядков для заказчика с именем Cisneros. Предположим, что вы не знаете номера этого заказчика, указываемого в поле cnum.

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

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

(См. Приложение A для ответов.)