ИСПОЛЬЗОВАНИЕ ПОДЗАПРОСОВ КОТОРЫЕ ВЫДАЮТ
МНОГО СТРОК С ПОМОЩЬЮ ОПЕРАТОРА IN
Вы можете использовать подзапросы которые производят любое число
строк если вы используете специальный оператор IN ( операторы BETWEEN,
LIKE, и IS NULL не могут использоваться с подзапросами ). Как вы помните,
IN определяет набор значений, одно из которых должно совпадать с другим
термином уравнения предиката в порядке, чтобы предикат был верным.
Когда вы используете IN с подзапросом, SQL просто формирует этот набор из
вывода подзапроса. Мы можем, следовательно, использовать IN чтобы выполнить такой же подзапрос который не будет работать с реляционным оператором, и найти все атрибуты таблицы Порядков для продавца в Лондоне ( вывод
показывается в Рисунке 10.4 ):
SELECT *
FROM Orders
WHERE snum IN
( SELECT snum
FROM Salespeople
WHERE city = "LONDON" );
=============== SQL Execution Log ==============
| |
| SELECT * |
| FROM Orders |
| WHERE snum IN |
| (SELECT snum |
| FROM Salespeople |
| WHERE city = 'London'); |
| =============================================== |
| onum amt odate cnum snum |
| ----- -------- ---------- ----- ------ |
| 3003 767.19 10/03/1990 2001 1001 |
| 3002 1900.10 10/03/1990 2007 1004 |
| 3006 1098.19 10/03/1990 2008 1007 |
| 3008 4723.00 10/05/1990 2006 1001 |
| 3011 9891.88 10/06/1990 2006 1001 |
================================================
Рисунок 10. 4: Использование подзапроса с IN
В ситуации подобно этой, подзапрос - более прост для пользователя чтобы
понимать его и более прост для компьютера чтобы его выполнить, чем если
бы Вы использовали объединение:
SELECT onum, amt, odate, cnum, Orders.snum
FROM Orders, Salespeople
WHERE Orders.snum = Salespeople.snum
AND Salespeople.city = "London";
Хотя это и произведет тот же самый вывод что и в примере с подзапросом,
SQL должен будет просмотреть каждую возможную комбинацию строк из
двух таблиц и проверить их снова по составному предикату.
Проще и эффективнее извлекать из таблицы Продавцов значения пол snum
где city = "London", и затем искать эти значения в таблице Порядков, как это
делается в варианте с подзапросом. Внутренний запрос дает нам snums=1001
и snum=1004. Внешний запрос, затем, дает нам строки из таблицы Порядков
где эти пол snum найдены.
Строго говор, быстрее или нет работает вариант подзапроса, практически
зависит от реализации - в какой программе вы это используете.
Эта часть вашей программы называемой - оптимизатор, пытается найти
наиболее эффективный способ выполнения ваших запросов.
Хороший оптимизатор во всяком случае преобразует вариант объединения
в подзапрос, но нет достаточно простого способа для вас чтобы выяснить
выполнено это или нет. Лучше сохранить ваши запросы в памяти чем полагаться полностью на оптимизатор.
Конечно вы можете также использовать оператор IN, даже когда вы уверены что подзапрос произведет одиночное значение. В любой ситуации где
вы можете использовать реляционный оператор сравнения (=), вы можете
использовать IN. В отличие от реляционных операторов, IN не может заставить команду потерпеть неудачу если больше чем одно значение выбрано
подзапросом. Это может быть или преимуществом или недостатком.
Вы не увидите непосредственно вывода из подзапросов; если вы полагаете
что подзапрос собирается произвести только одно значение, а он производит
различные. Вы не сможете объяснить различи в выводе основного запроса.
Например, рассмотрим команду, которая похожа на предыдущую:
SELECT onum, amt, odate
FROM Orders
WHERE snum =
( SELECT snum
FROM Orders
WHERE cnum = 2001 );
Вы можете устранить потребность в DISTINCT используя IN вместо (=),
подобно этому:
SELECT onum, amt, odate
FROM Orders
WHERE snum IN
( SELECT snum
FROM Orders
WHERE cnum = 2001 );
Что случится если есть ошибка и один из порядков был аккредитован к
различным продавцам? Версия использующая IN будет давать вам все
порядки для обоих продавцов. Нет никакого очевидного способа наблюдения за ошибкой, и поэтому сгенерированные отчеты или решения
сделанные на основе этого запроса не будут содержать ошибки. Вариант
использующий ( = ) , просто потерпит неудачу.
Это, по крайней мере, позволило вам узнать что имеется такая проблема.
Вы должны затем выполнять поиск неисправности, выполнив этот подза-
прос отдельно и наблюдая значения которые он производит.
В принципе, если вы знаете что подзапрос должен( по логике) вывести
только одно значение, вы должны использовать = .
IN является подходящим, если запрос может ограниченно производить одно
или более значений, независимо от того ожидаете вы их или нет.
Предположим, мы хотим знать комиссионные всех продавцов обслуживаю-
щих заказчиков в Лондоне:
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 + 1000
FROM Salespeople
WHERE sname = Serres );
Он находит всех заказчиков чье значение пол cnum равное 1000, выше пол
snum Serres. Мы предполагаем что столбец sname не имеет никаких двойных
значений ( это может быть предписано или UNIQUE INDEX, обсуждаемым
в Главе 17, или ограничением UNIQUE, обсуждаемым в Главе 18 ); иначе
=============== 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: Использование подзапроса с выражением
подзапрос может произвести многочисленные значения. Когда пол 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 для ответов. )