Материал: Graber_Martin_-_SQL_dlya_prostykh_smertnykh_-_2014

Как вьтолняются подзапросы?

WHERE odate = 10/04/1990);

Средняя стоимость заказов за 4 октября 1990 г. составляет 1788.98 (1713.23 + 75.75), деленное на 2, что равно 894.49. Строки, имеющие в поле amt (amount) значение, превышающее 894.49, выбираются в качестве результата запроса с

вложенным подзапросом.

Сгруппированные, то есть примененные с предложением GROUP ВУ, агрегатные функции могут дать в результате множество значений. Поэтому их нельзя применять в подзапросах. Такие команды отвергаются в принципе, несмотря на то, что примене­ ние GROUP ВУ и HAVING в некоторых случаях дает единственную группу в качестве

выходных данных подзапроса. Для исключения ненужных групп следует применить

единственную агрегатную функцию с предложением WHERE. Например, следующий запрос, составленный с целью найти средние комиссионные (comm) для продавцов (salespeople}, находящихся в Лондоне,

SELECT AVG (comm)

FROM Salespeople

GROUP ВУ city

HAVING city = 'London';

нельзя использовать в качестве подзапроса! Это вообще не лучший способ формули­

ровки запроса. Вот вариант, который нужен в данном случае:

SELECT AVG (comm)

FROM Salespeople

WHERE city = 'London';

Применение подзапросов, которые формируют множественные строки с помощью IN

Можно формулировать подзапросы, в результате выполнения которых получается лю­ бое количество строк, применяя специальный оператор IN (операторы BETWEEN, LIКE, IS NULL в подзапросах применять нельзя). IN определяет множество значений, которые

тестируются на совпадение с другими значениями д'lя определения истинности предика­

та. Когда IN применяется в подзапросе, SQL просто строит это множество из выходных данных подзапроса. Следовательно, можно использовать IN для выполнения подзапроса, который не работал бы с реляционным оператором, и найти все заявки (Orders) для про­ давцов (salespeople) из London (выходные данные представлены на рис. 10.4):

SELECT "

FROM Orders

WHERE snum IN

(SELECT snum

99

Глава 10. Вложение запросов

Рис. 10.4. Использование подзапроса с IN

FROM Salespeople

WHERE city = 'London·);

В подобной ситуации пользователю легче понять, а компьютеру проще (в конеч­ ном счете и быстрее) выполнить подзапрос, чем решать эту же задачу, применяя joiп:

SELECT onum, amt, odate, cnum, Orders.snum

FROM Orders, Salespeople

WHERE Orders.snum =Salespeople.snum

AND Salespeople.city = 'London·;

Выходные данные совпадают с результатом выполнения подзапроса, но SQL про­ сматривает все возможные комбинации строк из двух таблиц и проверяет, удовлетво­ ряет ли каждая из них составному предикату. Проще и эффективнее извлечь из таблицы Salespeople значение поля snum для тех строк, где city = 'Loпdon', а затем вы­ полнить поиск этих значений в таблице Orders; именно по такой схеме выполняется

вариант с вложенным подзапросом. Вложенный запрос дает номера 1001 и 1004.

Внешний запрос дает строки таблицы Orders, в которых значение поля snum совпадает

с одним из полученных (1001 или 1004).

Эффективность варианта с использованием подзапроса зависит от вьтолнения - от особенностей реализации той программы, с которой идет работа. В любом коммер­

ческом программном продукте есть часть программы, называемая оптимизатором,

которая пытается найти самые эффективные способы выполнения запросов. Хороший оптимизатор преобразует версию с joiп в версию с подзапросом, но простого способа

проверки, сделано это или нет, не существует. Поэтому при написании запросов луч-

100

Как выполняются подзапросы?

ше использовать заведомо более эффективный вариант, чем полностью полагаться на

возможности оптимизатора.

Можно применять IN и в тех ситуациях, когда есть абсолютная уверенность в полу­ чении единственного значения в результате выполнения подзапроса. IN можно также использовать там, где применим реляционный оператор сравнения. В отличие от реля­ ционных операторов IN не приводит к ошибке выполнения команды, когда в результате выполнения подзапроса получается не одно, а несколько значений (выходных данных). В этом есть и плюсы, и минусы. Результаты выполнения подзапроса непосредственно не видны. При абсолютной уверенности в том, что в результате выполнения подзапроса будет получено только одно значение, а в действительности их получается несколько, невозможно объяснить разницу в выходных данных, полученных в результате выполне­ ния основного запроса. Рассмотрим следующую команду, сходную с командой преды­

дущего примера:

SELECT onum, amt, odate FROM Orders

WHERE snum =

(SELECT DISTINCT snum FROM Orders

WHERE cnum = 2001);

Можно отказаться от DISТINCT, воспользовавшись IN вместо равенства. В резуль­

тате получается:

SELECT onum, amt, odate FROM Orders

WHERE snum IN (SELECT snum

FROM Orders

WHERE cnum =2001);

Если допушен~,. ошибка и один из заказов (orders) бьm адресован нескольким продав­ цам (salesperson), версия с IN выдаст все заказы для обоих продавцов. Ошибку обнару­ жить невозможно и отчет или решения, принятые на основе полученных данных, бьmи бы неверными. С другой стороны, вариант с равенством, будет просто признан ошибоч­ ным, в результате чего возникнет проблема. Затем можно выполнить подзапрос сам по себе и внимательно рассмотреть его выходные данные.

Если есть уверенность в получении единственного значения в результате выполне­

ния подзапроса, следует использовать равенство. IN подходит для тех случаев, когда за­

прос может генерировать одно или несколько значений, независимо от того, что именно

ожидается. Предположим, нужно знать комиссионные всех продавцов (salespeople), об­ служивающих покупателей (customers) в Лондоне (London):

SELECT comm

FROM Salespeople WHERE snum IN

101

Глава 10. Вложение запросов

(SELECT snum

FROM Customers

WHERE city = "London");

Выходные данные для запроса, представленные на рис. 10.5, показывают комисси­ онные продавца PEEL (snum = 1001), обслуживающего обоих лондонских покупате­ лей. Однако, такой результат получается только на основе текущих данных. Нет (очевидной) причины, в силу которой невозможно, чтобы кого-то из лондонских поку-

Рис. 1О.5. Использование IN в подзапросе, результатом которого является единственное значение

пателей обслуживал другой продавец. Следовательно, для данного запроса наиболее

логичным вариантом является использование IN.

Отказ от испош.зовання префиксов таблиц в подзапросах. Префикс имени таблицы для поля city в предыдущем примере не является необходимым, несмотря на то, что поле city есть в таблице Salespeople и в таб,1ице Customers. SQL всегда снача­ ла пытается найти поля в таблице (таблицах), заданной (заданных) в предложении FROM текущего запроса (подзапроса). Если поле с указанным именем здесь не най­

дено, то анализируется внешний запрос. В рассмотренном примере предполагается,

что "city" в предложении WHERE относится к столбцу city таблицы Customers (Custoшers.city). Поскольку имя таблицы Custoшers указано в предложении FROM

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

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

102

Как вьтолпяются подзапросы?

Подзапросы используют единственный столбец. Общая черта всех подзапросов, рассмотренных в этой главе, состоит в том, что они выбирают единственный столбец. Это существенно, так как выходные данные вложенного SЕLЕСТ-предложения сравни­ ваются с единственным значением. Из этого следует, что вариант SELECT * нельзя ис­

пользовать в подзапросе. Исключением из этого правила являются подзапросы с

оператором EXISTS, который рассматривается в главе 12.

Использование выражений в подзаnросах. В предложении подзапроса SELECT можно использовать выражения, основанные на столбцах, а не сами столбцы. Это

можно сделать, применяя операторы отношения или IN. Например, следующий за­

прос использует оператор отношения = (выходные данные для этого запроса пред­

ставлены на рис. 10.6:

SELECT *

FROM Customers

WHERE cnum

(SELECT snum + 1000

FROM Salespeople

WHERE sname = ·serres');

Запрос находит всех покупателей, для которых cnuш на 1ООО превосходит значение

поля snuш для Serres. В данном случае предполагается, что в столбце snuш нет повто­ ряющихся значений (этого можно добиться, применяя либо UNIQUE INDEX, обсуж­ даемый в главе 17, либо ограничение UNIQUE, обсуждаемое в главе 18); в противном случае результатом выполнения подзапроса может оказаться множество значений. За-

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

103