Мартин Грубер - Понимание SQL (991940), страница 18
Текст из файла (страница 18)
Давайте введем команду другим способом (вывод показывается в Рисунке 11.5):SELECT *FROM Orders outerWHERE amt >= ( SELECT AVG (amt)FROM Orders innerWHERE inner.cnum = outer.cnum );=============== SQL Execution Log ==============| SELECT *|| FROM Orders outer|| WHERE amt > =|| (SELECT AVG (amt)|| FROM Orders inner|| WHERE inner.cnum = outer.cnum);|| =============================================== ||onumamtodatecnumsnum || ------------ ---------- ---------- ||3003767.19 10/03/199020011001 ||30021900.10 10/03/199020071004 ||30055160.45 10/03/199020031002 ||30061098.19 10/03/199020081007 ||30091713.23 10/04/199020021003 ||30101309.95 10/06/199020041002 ||30119891.88 10/06/199020061001 |=================================================Рисунок 11.5: Выбераются порядки которые >= средней сумме приобретенийдля их заказчиков.Различие, конечно, в том, что реляционный оператор основного предикатавключает значения которые равняются среднему (что обычно означает что они —единственые порядки для данных заказчиков).СООТНЕСЕННЫЕ ПОДЗАПРОСЫ В ПРЕДЛОЖЕНИИ HAVINGТакже как предложение HAVING может брать подзапросы, он может брать и соотнесенные подзапросы.
Когда вы используете соотнесенный подзапрос в предложении HAVING, вы должны ограничивать внешние ссылки к позициям которые могли бынепосредственно использоваться в самом предложении HAVING. Вы можете вспомнить из Главы 6 что предложение HAVING может использовать только агрегатныефункции которые указаны в их предложении SELECT или поля используемые в ихпредложении GROUP BY.
Они являются только внешними ссылками, которые вы можете делать. Все это потому, что предикат предложения HAVING оценивается длякаждой группы из внешнего запроса, а не для каждой строки. Следовательно, подзапрос будет выполняться один раз для каждой группы выведеной из внешнего запроса,а не для каждой строки.Предположим, что вы хотите суммировать значения сумм приобретений покупокиз таблицы Порядков, сгруппировав их по датам, удалив все даты где бы SUM не былпо крайней мере на 2000.00 выше максимальной (MAX) суммы:SELECT odate, SUM (amt)FROM Orders aGROUP BY odateHAVING SUM (amt) > ( SELECT 2000.00 + MAX (amt)FROM Orders bWHERE a.odate = b.odate );Подзапрос вычисляет значение MAX для всех строк с той же самой датой что и утекущей агрегатной группы основного запроса.
Это должно быть выполнено, как и ранее, с испошльзованием предложения WHERE. Сам подзапрос не должен использовать предложения GROUP BY или HAVING.СООТНЕСЕННЫЕ ПОДЗАПРОСЫ И ОБЬЕДИНЕНИЯКак вы и могли предположить, соотнесенные подзапросы по природе близки кобьединениям — они оба включают проверку каждой строки одной таблицы с каждойстрокой другой (или псевдонимом из той же) таблицы.
Вы найдете, что большинствоопераций, которые могут выполняться с одним из них, будут также работать и с другим.Однако имеется различие в прикладной программе между ними, такое как вышеупомянутая потребность в использовании DISTINCT с обьединением и его необязательность с подзапросом. Имеются также некоторые вещи, которые каждый можетделать так, как этого не может другой. Подзапросы, например, могут использовать агрегатную функцию в предикате, делая возможным выполнение операций типа нашегопредыдущего примера, в котором мы извлекли порядки, усредненные для их заказчиков.Обьединения, с другой стороны, могут выводить строки из обеих сравниваемыхтаблиц, в то время как вывод подзапросов используется только в предикатах внешнихзапросов.
Как правило, форма запроса которая кажется наиболее интуитивной будетвероятно лучшей в использовании, но при этом хорошо бы знать обе техники для техситуаций когда та или иная могут не работать.РЕЗЮМЕВы можете поздравлять себя с овладением большого куска из рассмотреных понятий в SQL — соотнесенного подзапроса. Вы видели как соотнесенный подзапроссвязан с обьединение, а также, как как его можно использовать с агрегатными функциями и в предложении HAVING. В общем, вы теперь узнали все типы подзапросовполностью.Следующий шаг — описание некоторых специальных операторов SQL. Они берут подзапросы как аргументы, как это делает IN, но в отличие от IN, они могут ис-пользоваться только в подзапросах. Первый из их, представленный в Главе 12, — называется EXISTS.РАБОТА С SQL1.
Напишите команду SELECT, использующую соотнесенный подзапрос, которая выберет имена и номера всех заказчиков с максимальными для их городов оценками.2. Напишите два запроса, которые выберут всех продавцов (по их имени и номеру)которые, в своих городах имеют заказчиков, которых они не обслуживают. Один запрос — с использованием обьединения и один — с соотнесенным подзапросом. Которое из решений будет более изящным? (Подсказка: один из способом этосделать, состоит в том, чтобы находить всех заказчиков, не обслуживаемых данным продавцом и определить, находится ли каждый из них в городе продавца.)(См.
Приложение A для ответов.)12ИСПОЛЬЗОВАНИЕОПЕРАТОРА EXISTSТЕПЕРЬ, КОГДА ВЫ ХОРОШО ОЗНАКОМЛЕНЫ С ПОДЗАПРОСАМИ, мы можемговорить о некоторых специальных операторах, которые всегда берут подзапросы какаргументы. Вы узнаете о первом из них в этой главе. Остальные будут описаны в следующей главе.Оператор EXISTS используется чтобы указать предикату, производить ли подзапросу вывод или нет. В этой главе вы узнаете, как использовать этот оператор состандартными и (обычно) соотнесенными подзапросами.
Мы будем также обсуждатьспециальные размышления, которые перейдут в игру, когда вы будете использоватьэтот оператор, как относительный агрегат, как пустой указатель NULL и как операторБуля. Кроме того, вы можете повысить ваш профессиональный уровень относительноподзапросов, исследуя их в более сложных прикладных программах чем те, которыемы видели до сих пор.КАК РАБОТАЕТ EXISTS?EXISTS — это оператор, который производит верное или неверное значение,другими словами, выражение Буля (см. Главу 4 для обзора этого термина).
Это означает, что он может работать автономно в предикате или в комбинации с другими выражениями Буля, использующими Булевы операторы AND, OR, и NOT. Он беретподзапрос как аргумент и оценивает его как верный, если тот производит любой вывод или как неверный, если тот не делает этого. Этим он отличается от других операторов предиката, в которых он не может быть неизвестным. Например, мы можемрешить, извлекать ли нам некоторые данные из таблицы Заказчиков если, и толькоесли, один или более заказчиков в этой таблице находятсяся в San Jose (вывод дляэтого запроса показывается в Рисунке 12.1):SELECT cnum, cname, cityFROM CustomersWHERE EXISTS ( SELECT *FROM CustomersWHERE city = " San Jose' );=============== SQL Execution Log ============| SELECT snum, sname, city|| FROM Customers|| WHERE EXISTS|| (SELECT *|| FROM Customers|| WHERE city = 'San Jose');|| ============================================= ||cnumcnamecity|| --------------||2001HoffmanLondon||2002GiovanniRome||2003LiuSan Jose||2004GrassBerlin||2006ClemensLondon||2008CisnerosSan Jose||2007PereiraRome|===============================================Рисунок 12.1 Использование оператора EXISTSВнутренний запрос выбирает все данные для всех заказчиков в San Jose.
Оператор EXISTS во внешнем предикате отмечает, что некоторый вывод был произведенподзапросом, и поскольку выражение EXISTS было полным предикатом, делает пре-дикат верным. Подзапрос (не соотнесенный) был выполнен только один раз для всеговнешнего запроса, и следовательно имеет одно значение во всех случаях. ПоэтомуEXISTS, когда используется этим способом, делает предикат верным или невернымдля всех строк сразу, что это не так уж полезно для извлечения определенной информации.ВЫБОР СТОЛБЦОВ С ПОМОЩЬЮ EXISTSВ вышеупомянутом примере, EXISTS должен быть установлен так чтобы легковыбрать один столбец, вместо того, чтобы выбирать все столбцы используя в выборезвезду (SELECT *) В этом состоит его отличие от подзапроса который (как вы виделиранее в Главе 10 мог выбрать только один столбец).Однако, в принципе он мало отличается при выборе EXISTS столбцов, или когдавыбираются все столбцы, потому что он просто замечает — выполняется или нет вывод из подзапроса — а не использует выведенные значения.ИСПОЛЬЗОВАНИЕ EXISTS С СООТНЕСЕННЫМИПОДЗАПРОСАМИВ соотнесенном подзапросе предложение EXISTS оценивается отдельно длякаждой строки таблицы, имя которой указано во внешнем запросе, точно также как идругие операторы предиката, когда вы используете соотнесенный подзапрос.
Это дает возможность использовать EXISTS как верный предикат, который генерирует различные ответы для каждой строки таблицы, указанной в основном запросе.Следовательно информация из внутреннего запроса будет сохранена, если выведенанепосредственно, когда вы используете EXISTS таким способом. Например, мы можем вывести продавцов, которые имеют многочисленых заказчиков (вывод для этогозапроса показывается в Рисунке 12.2):SELECT DISTINCT snumFROM Customers outerWHERE EXISTS ( SELECT *FROM Customers innerWHERE inner.snum = outer.snumAND inner.cnum < > outer.cnum );=============== SQL Execution Log ============| SELECT DISTINCT cnum|| FROM Customers outer|| WHERE EXISTS|| (SELECT *|| FROM Customers inner|| WHERE inner.snum = outer.snum|| AND inner.cnum < > outer.cnum);|| ============================================= ||cnum|| ----||1001||1002|=============================================Рисунок 12.2: Использование EXISTS с соотнесенным подзапросомДля каждой строки-кандидата внешнего запроса (представляющей заказчикапроверяемого в настоящее время), внутренний запрос находит строки, которые сов-падают со значением поля snum (которое имел продавец), но не со значением поляcnum (сответствующего другим заказчикам).
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
