Мартин Грубер - Понимание SQL (991940), страница 20
Текст из файла (страница 20)
В этом отношении, они напоминают оператор IN когда тотиспользуется с подзапросами; они берут все значения выведенные подзапросом иобрабатывают их как модуль. Однако, в отличие от IN, они могут использоватьсятолько с подзапросами.СПЕЦИАЛЬНЫЕ ОПЕРАТОРЫ ANY или SOMEОператоры SOME и ANY — взаимозаменяемы везде и там где мы используемANY, SOME будет работать точно так же. Различие в терминологии состоит в том чтобы позволить людям использовать тот термин который наиболее однозначен.
Этоможет создать проблему; потому что, как мы это увидим, наша интуиция может иногдавводить в заблуждение.Имеется новый способ нахождения продавца с заказчиками размещенными в ихгородах (вывод для этого запроса показывается в Рисунке 13.1):SELECT *FROM SalespeopleWHERE city = ANY (SELECT cityFROM Customers );Оператор ANY берет все значения выведенные подзапросом, (для этого случая— это все значения city в таблице Заказчиков), и оценивает их как верные если любой(ANY) из их равняется значению города текущей строки внешнего запроса.=============== SQL Execution Log ============| SELECT *|| FROM Salespeople|| WHERE city = ANY|| (SELECT city|| FROM Customers);|| ============================================= ||cnumcnamecitycomm|| ---------------------||1001PeelLondon0.12||1002SerresSan Jose0.13||1004MotikaLondon0.11|===============================================Рисунок 13.1: Использование оператора ANYЭто означает, что подзапрос должен выбирать значения такого же типа как и те,которые сравниваются в основном предикате.
В этом его отличие от EXISTS, которыйпросто определяет, производит ли подзапрос результаты или нет, и фактически неиспользует эти результаты.ИСПОЛЬЗОВАНИЕ ОПЕРАТОРОВ IN ИЛИ EXISTSВМЕСТО ОПЕРАТОРА ANYМы можем также использовать оператор IN чтобы создать запрос аналогичныйпредыдущему :SELECT *FROM SalespeopleWHERE city IN ( SELECT cityFROM Customers );Этот запрос будет производить вывод показанный в Рисунке 13.2.=============== SQL Execution Log ============| SELECT *|| FROM Salespeople|| WHERE city IN (SELECT city||FROM Customers);|| ============================================= ||cnumcnamecitycomm|| ---------------------||1001PeelLondon0.12||1002SerresSan Jose0.13||1004MotikaLondon0.11|===============================================Рисунок 13.2: Использование IN в качестве альтернативы к ANYОднако, оператор ANY может использовать другие реляционные операторыкроме равняется (=), и таким образом делать сравнения которые являются выше возможностей IN.
Например, мы могли бы найти всех продавцов с их заказчиками, которые следуют в алфавитном порядке (вывод показан на Рисунке 13.3)SELECT *FROM SalespeopleWHERE sname < ANY ( SELECT cnameFROM Customers );=============== SQL Execution Log ============| SELECT *|| FROM Salespeople|| WHERE sname < ANY|| (SELECT cname|| FROM Customers);|| ============================================= ||cnumcnamecitycomm|| ---------------------||1001PeelLondon0.12||1004MotikaLondon0.11||1003AxelrodNew York0.10|===============================================Рисунок 13.3: Использование оператора ANY с оператором "неравно" (<)Все строки были выбраны для Serres и Rifkin, потому что нет других заказчиковчьи имена следовали бы за ими в алфавитном порядке.
Обратите внимание что этоявляется основным эквивалентом следующему запросу с EXISTS, чей вывод показывается в Рисунке 13.4:SELECT *FROM Salespeople outerWHERE EXISTS ( SELECT *FROM Customers innerWHERE outer.sname < inner.cname );=============== SQL Execution Log ============| SELECT *|| FROM Salespeople outer|| WHERE EXISTS|| (SELECT *|| FROM Customers inner|| WHERE outer.sname < inner.cname);|| ============================================= ||cnumcnamecitycomm|| ---------------------||1001PeelLondon0.12||1004MotikaLondon0.11||1003AxelrodNew York0.10|===============================================Рисунок 13.4 Использование EXISTS как альтернатива оператору ANYЛюбой запрос который может быть сформулирован с ANY (или, как мы увидим, сALL), мог быть также сформулирован с EXISTS, хотя наоборот будет неверно. Строгоговоря, вариант с EXISTS не абсолютно идентичен вариантам с ANY или с ALL из-заразличия в том как обрабатываются пустые (NULL) значения (что будет обсуждатьсяпозже в этой главе).
Тем не менее, с технической точки зрения, вы могли бы делатьэто без ANY и ALL если бы вы стали очень находчивы в использовании EXISTS (и ISNULL).Большинство пользователей, однако, находят ANY и ALL более удобными в использовании чем EXISTS, который требует соотнесенных подзапросов. Кроме того, взависимости от реализации, ANY и ALL могут, по крайней мере в теории, быть болееэффективными чем EXISTS.Подзапросы ANY или ALL могут выполняться один раз и иметь вывод используемый чтобы определять предикат для каждой строки основного запроса. EXISTS, сдругой стороны, берет соотнесенный подзапрос, который требует чтобы весь подзапрос повторно выполнялся для каждой строки основного запроса.
SQL пытается найтинаиболее эффективный способ выполнения любой команды, и может попробоватьпреобразовать менее эффективную формулу запроса в более эффективную (но вы неможете всегда рассчитывать на получение самой эффективной формулировки).Основная причина для формулировки EXISTS как альтернативы ANY и ALL в томчто ANY и ALL могут быть несколько неоднозначен, из-за способа использования этого термина в Английском языке, как вы это скоро увидите. С приходом понимания различия способов формулирования данного запроса, вы сможете поработать надпроцедурами которые сейчас кажутся Вам трудными или неудобными.КАК ANY МОЖЕТ СТАТЬ НЕОДНОЗНАЧНЫМКак подразумевалось выше, ANY не полностью однозначен.
Если мы создаемзапрос, чтобы выбрать заказчиков, которые имеют больший рейтинг чем любой заказчик в Риме, мы можем получить, вывод который несколько отличался бы от того, чтомы ожидали (как показано в Рисунке 13.5):SELECT *FROM CustomersWHERE rating > ANY ( SELECT ratingFROM CustomersWHERE city = Rome );В Английском языке, способ которым мы обычно склонны интерпретироватьоценку "больше чем любой (где city = Rome)", должен вам сообщить, что это значениеоценки должно быть выше, чем значение оценки в каждом случае где значение city =Rome. Однако это не так, в случае ANY — используемом в SQL.
ANY оценивает какверно, если подзапрос находит любое значение которое делает условие верным.=============== SQL Execution Log ============| SELECT *|| FROM Customers|| WHERE rating > ANY|| (SELECT rating|| FROM Customers|| WHERE city = 'Rome');|| ============================================= ||cnumcnamecityratingsnum|| -------------------- ------ ||2002GiovanniRome2001003 ||2003LiuSan Jose2001002 ||2004GrassBerlin3001002 ||2008CisnerosSan Jose3001007 |===============================================Рисунок 13.5: Как оператор "больше чем" (>) интерпретируется ANYЕсли мы оценим ANY способом, использующим грамматику Английского Языка,то только заказчики с оценкой 300 будут превышать Giovanni, который находится вРиме и имеет оценку 200.
Однако, подзапрос ANY также находит Periera в Риме соценкой 100. Так как все заказчики с оценкой 200 были выше этой, они будут выбраны, даже если имелся другой заказчик из Рима (Giovanni), чья оценка не была выше(фактически, то что один из выбранных заказчиков также находится в Риме несущественно). Так как подзапрос произвел по крайней мере одно значение, которое сделаетпредикат верным в отношении этих строк, строки были выбраны.Чтобы дать другой пример, предположим что мы должны были выбирать все порядки сумм приоретений которые были больше чем по крайней мере один из порядков на 6-е Октября:SELECT *FROM OrdersWHERE amt > ANY ( SELECT amtFROM OrdersWHERE odate = 10/06/1990 );Вывод для этого запроса показывается в Рисунке 13.6.=============== SQL Execution Log ==============| SELECT *|| FROM Orders|| WHERE amt > ANY|| (SELECT amt|| FROM Orders|| WHERE odate = 10/06/1990);|| =============================================== ||onumamtodatecnumsnum || ------------ ---------- ---------- ||30021900.10 10/03/199020071004 ||30055160.45 10/03/199020031002 ||30091713.23 10/04/199020021003 ||30084723.00 10/05/199020061001 ||30119891.88 10/06/199020061001 |=================================================Рисунок 13.6: Выбранное значение больше чем любое (ANY) на 6-е ОктябряДаже если самая высокая сумма приобретений в таблице (9891.88) — имеласьна 6-е Октября, предыдущая строка имеет более высокое значение суммы чем другаястрока на 6-е Октября, которая имела значение суммы = 1309.95.
Имея реляционныйоператор ">=" вместо просто ">", эта строка будет также выбирана, потому что онаравна самой себе.Конечно, вы можете использовать ANY с другой SQL техникой, например с техникой обьединения. Этот запрос будет находить все порядки со значением суммыменьшей чем значение любой суммы для заказчика в San Jose. (вывод показываетсяв Рисунке 13.7):SELECT *FROM OrdersWHERE amt < ANY ( SELECT amtFROM Orders A, Customers bWHERE a.cnum = b.cnumAND b.city = “San Jose” );Даже если нименьший порядок в таблице был для заказчика из San Jose, то былвторой наибольший; следовательно почти все строки будут выбраны.
Простой способзапомнить, что < ANY значение меньшее чем наибольшее выбранное значение, а >ANY значение большее чем наименьшее выбранное значение.=============== SQL Execution Log ==============| WHERE amt > ANY|| (SELECT amt|| FROM Orders a, Customers b|| WHERE a.cnum = b.cnum|| AND b.city = 'San Jose');|| =============================================== ||onumamtodatecnumsnum || ------------ ---------- ---------- ||300118.69 10/03/199020081007 ||3003767.10 10/03/199020011001 ||30021900.10 10/03/199020071004 ||30061098.10 10/03/199020081007 ||30091713.23 10/04/199020021003 ||300775.10 10/04/199020041002 ||30084723.00 10/05/199020061001 ||30101309.88 10/06/199020041002 |=================================================Рисунок 13.7:Использование ANY с объединениемФактически, вышеуказанные команды весьма похожи на следующее — (выводпоказан на Рисунке 13.8):SELECT *FROM OrdersWHERE amt < ( SELECT MAX amtFROM Orders A, Customers bWHERE a.cnum = b.cnum AND b.city = " San Jose' );=============== SQL Execution Log ==============| WHERE amt <|| (SELECT MAX (amt)|| FROM Orders a, Customers b|| WHERE a.cnum = b.cnum|| AND b.city = 'San Jose');|| =============================================== ||onumamtodatecnumsnum || ------------ ---------- ---------- ||30021900.10 10/03/199020071004 ||30055160.45 10/03/199020031002 ||30091713.23 10/04/199020021003 ||30084723.00 10/05/199020061001 ||30119891.88 10/06/199020061001 |=================================================Рисунок 13.8: Использование агрегатной функции вместо ANYСПЕЦИАЛЬНЫЙ ОПЕРАТОР ALLС помощью ALL, предикат является верным, если каждое значение выбранноеподзапросом удовлетворяет условию в предикате внешнего запроса.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
