Соотнесенные подзапросы в предложении HAVING

Также как предложение HAVING может брать подзапросы, оно может брать и соотнесенные подзапросы. Когда вы используете соотнесенный подзапрос в предложении HAVING, вы должны ограничивать внешние ссылки к позициям, которые могли бы непосредственно использоваться в самом предложении HAVING. Вы можете вспомнить из Главы 6, что предложение HAVING может использовать только агрегатные функции, которые указаны в их предложении SELECT или поля используемые в их предложении GROUP BY. Они являются только внешними ссылками, которые вы можете делать. Все это потому, что предикат предложения HAVING оценивается для каждой группы из внешнего запроса, а не для каждой строки. Следовательно, подзапрос будет выполняться один раз для каждой группы выведенной из внешнего запроса, а не для каждой строки.

Предположим, что вы хотите суммировать значения сумм приобретений покупок из таблицы Заказов, сгруппировав их по датам, удалив все даты, где бы сумма (SUM) не была, по крайней мере, на 2000.00 выше максимальной (MAX) суммы:

Select odate, SUM (amt)
FROM orders a
GROUP BY odate
HAVING SUM (amt) > (SELECT 2000.00 + MAX (amt)
FROM Orders b
WHERE a.odate = b.odate);

Подзапрос вычисляет значение MAX для всех строк с той же самой датой, что и у текущей агрегатной группы основного запроса. Это должно быть выполнено, как и ранее, с использованием предложения WHERE. Сам подзапрос не должен использовать предложения GROUP BY или HAVING.

Соотнесенные подзапросы и объединения

Как вы и могли предположить, соотнесенные подзапросы по природе близки к объединениям — они оба включают проверку каждой строки одной таблицы с каждой строкой другой (или псевдонимом из той же) таблицы. Вы найдете, что большинство операций, которые могут выполняться с одним из них, будут также работать и с другим.

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

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

Резюме

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

Следующий шаг — описание некоторых специальных операторов SQL. Они берут подзапросы как аргументы, как это делает IN, но в отличие от IN, они могут использоваться только в подзапросах. Первый из них, представленный в Главе 12, — называется EXISTS.

Работа с SQL

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

2. Напишите два запроса, которые выберут всех продавцов (по их имени и номеру), которые в своих городах имеют заказчиков, которых они не обслуживают. Один запрос — с использованием объединения и один — с соотнесенным подзапросом. Которое из решений будет более изящным? (Подсказка: один из способов это сделать состоит в том, чтобы находить всех заказчиков, не обслуживаемых данным продавцом и определить, находится ли каждый из них в городе продавца).

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