Подзапросы выбирают одиночные столбцы

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

Использование выражений в подзапросах

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

SELECT *
FROM Customers
WHERE cnum = (SELECT snum + 1000
FROM Salespeople
WHERE sname = 'Serres');

=============== SQL Execution Log ============
| SELECT * |
| FROM Customers |
| WHERE cnum = |
| (SELECT snum + 1000 |
| WHERE Salespeople |
| WHERE sname = 'Serres' |
| ============================================= |
| cnum cname city rating snum |
| ----- -------- ---- ------ ----- |
| 2002 Giovanni Rome 200 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) |
| FROM Customers |
| WHERE city = 'San Jose' |
|===============================================|
| rating |
| -------- -------- |
| 300 2 |
===============================================

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

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

Резюме

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

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

Работа с SQL

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

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

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

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

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

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