Мартин Грубер - Понимание SQL
3006
1098.19
10/03/1990
2008
1007
3009
1713.23
10/04/1990
2002
1003
3010
1309.95
10/06/1990
2004
1002
3011
9891.88
10/06/1990
2006
1001
Таблица 11.5: Выбераются порядки которые >=средней сумме приобретений для их заказчиков.
Различие, конечно, в том, что реляционный оператор основного предиката включает значения которые равняются среднему (что обычно означает что они - единственые порядки для данных заказчиков ).
СООТНЕСЕННЫЕ ПОДЗАПРОСЫ В ПРЕДЛОЖЕНИИ 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* Напишите команду SELECT использующую соотнесенный подзапрос, которая выберет имена и номера всех заказчиков с максимальными для их городов оценками.
* Напишите два запроса которые выберут всех продавцов (по их имени и номеру ) которые в своих городах имеют заказчиков которых они не обслуживают. Один запрос - с использованием обьединения и один - с соотнесенным подзапросом. Которое из решений будет более изящным? (Подсказка: один из способом это сделать, состоит в том, чтобы находить всех заказчиков не обслуживаемых данным продавцом и определить, находится ли каждый из них в городе продавца.)
Глава 12. ИСПОЛЬЗОВАНИЕ ОПЕРАТОРА EXISTS
ТЕПЕРЬ, КОГДА ВЫ ХОРОШО ОЗНАКОМЛЕНЫ С ПОДЗАПРОСАМИ, мы можем говорить о некоторых специальных операторах которые всегда берут подзапросы как аргументы. Вы узнаете о первом из их в этой главе. Остальные будут описан в следующей главе.
Оператор EXISTS используется чтобы указать предикату, - производить ли подзапросу вывод или нет. В этой главе, вы узнаете как использовать этот оператор со стандартными и (обычно ) соотнесенными подзапросами.
Мы будем также обсуждать специальные расмышления которые перейдут в игру когда вы будете использовать этот оператор как относительный агрегат, как пустой указатель NULL, и как оператор Буля. Кроме того, вы можете повысить ваш профессиональный уровень относительно подзапросов исследуя их в более сложных прикладных программах чем те которые мы видели до сих пор.
КАК РАБОТАЕТ EXISTS?EXISTS - это оператор, который производит верное или неверное значение, другими словами, выражение Буля (см. Главу 4 для обзора этого термина). Это означает что он может работать автономно в предикате или в комбинации с другими выражениями Буля использующими Булевы операторы AND, OR, и NOT. Он берет подзапрос как аргумент и оценивает его как верный если тот производит любой вывод или как неверный если тот не делает этого. Этим он отличается от других операторов предиката, в которых1 он не может быть неизвестным. Например, мы можем решить, извлекать ли нам некоторые данные из таблицы Заказчиков если, и только если, один или более заказчиков в этой таблице находятсяся в San Jose (вывод для этого запроса показывается в Таблице 12.1):
SELECT cnum, cname, city
FROM Customers
WHERE EXISTS
( SELECT *
FROM Customers
WHERE city=" San Jose' );
Внутренний запрос выбирает все данные для всех заказчиков в San Jose. Оператор EXISTS во внешнем предикате отмечает, что некоторый вывод был произведен подзапросом, и поскольку выражение EXISTS было полным предикатом, делает предикат верным. Подзапрос( не соотнесенный ) был выполнен только один раз для всего внешнего запроса, и следовательно,
SQL Execution Log
SELECT snum, sname, city FROM Customers WHERE EXISTS
(SELECT * FROM Customers WHERE city='San Jose');
cnum
cname
city
2001
Hoffman
London
2002
Giovanni
Rome
2003
Liu
San Jose
2004
Grass
Berlin
2006
Clemens
London
2008
Cisneros
San Jose
2007
Pereira
Rome
Таблица 12.1 Использование оператора EXISTS
имеет одно значение во всех случаях. Поэтому EXISTS, когда используется этим способом, делает предикат верным или неверным для всех строк сразу, что это не так уж полезно для извлечения определенной информации.
ВЫБОР СТОЛБЦОВ С ПОМОЩЬЮ EXISTS
В вышеупомянутом примере, EXISTS должен быть установлен так чтобы легко выбрать один столбец, вместо того, чтобы выбирать все столбцы используя в выборе звезду( SELECT *) В этом состоит его отличие от подзапроса который (как вы видели ранее в Главе 10 мог выбрать только один столбец ) . Однако, в принципе он мало отличается при выборе EXISTS столбцов, или когда выбираются все столбцы, потому что он просто замечает - выполняется или нет вывод из подзапроса - а не использует выведенные значения.
ИСПОЛЬЗОВАНИЕ EXISTS С СООТНЕСЕННЫМИ ПОДЗАПРОСАМИ
В соотнесенном подзапросе, предложение EXISTS оценивается отдельно для каждой строки таблицы имя которой указано во внешнем запросе, точно также как и другие операторы предиката, когда вы используете соотнесенный подзапрос. Это дает возможность использовать EXISTS как верный предикат, который генерирует различные ответы для каждой строки таблицы указанной в основном запросе. Следовательно информация из внутреннего запроса, будет сохранена, если выведена непосредственно, когда вы используете EXISTS таким способом. Например, мы можем вывести продавцов которые имеют многочисленых заказчиков (вывод для этого запроса показывается в Таблице 12.2 ):
SELECT DISTINCT snum
FROM Customers outer
WHERE EXISTS
( SELECT *
FROM Customers inner
WHERE inner.snum=outer.snum
AND inner.cnum < > outer.cnum );
SQL Execution Log
SELECT DISTINCT cnum FROM Customers outer WHERE EXISTS
(SELECT * FROM Customers inner WHERE inner.snum=outer.snum
