13. ИСПОЛЬЗОВАНИЕ ОПЕРАТОРОВ ANY, ALL, И SOME

ТЕПЕРЬ, КОГДА ВЫ ОВЛАДЕЛИ ОПЕРАТОРОМ EXISTS, Вы узнаете приблизительно три специальных оператора ориентируемых на подзапросы. (Фактически, имеются только два, так как ANY и SOME - одно и то же.) Если вы поймете работу этих операторов, вы будете понимать все типы подзапросов предиката используемых в SQL . Кроме того, вы будете представлены различным способам где данный запрос может быть сформирован используя различные типы подзапросов предиката, и вы поймете преимущества и недостатки каждого из этих подходов.

ANY, ALL, и SOME напоминают EXISTS который воспринимает подзапросы как аргументы; однако они отличаются от EXISTS тем, что используются совместно с реляционными операторами. В этом отношении, они напоминают оператор IN когда тот используется с подзапросами; они берут все значения выведенные подзапросом и обрабатывают их как модуль. Однако, в отличие от IN, они могут использоваться только с подзапросами.

=========СПЕЦИАЛЬНЫЕ ОПЕРАТОРЫ ============

ANY или SOME

Операторы SOME и ANY - взаимозаменяемы везде и там где мы используем ANY, SOME будет работать точно так же. Различие в терминологии состоит в том чтобы позволить людям использовать тот термин который наиболее однозначен. Это может создать проблему; потому что, как мы это увидим, наша интуиция может иногда вводить в заблуждение.

Имеется новый способ нахождения продавца с заказчиками размещенными в их городах ( вывод для этого запроса показывается в Таблице 13.1 ):

SELECT *
FROM Salespeople
WHERE city=ANY
(SELECT city
FROM Customers );

Оператор ANY берет все значения выведенные подзапросом, ( для этого случая - это все значения city в таблице Заказчиков ), и оценивает их как верные если любой(ANY) из их равняется значению города текущей строки внешнего запроса.

Таблица 13. 1: Использование оператора ANY

Это означает, что подзапрос должен выбирать значения такого же типа как и те, которые сравниваются в основном предикате. В этом его отличие от EXISTS, который просто определяет, производит ли подзапрос результаты или нет, и фактически не использует эти результаты.

ИСПОЛЬЗОВАНИЕ ОПЕРАТОРОВ IN ИЛИ EXISTS ВМЕСТО ОПЕРАТОРА ANY

Мы можем также использовать оператор IN чтобы создать запрос анало-
гичный предыдущему :

SELECT *
FROM Salespeople
WHERE city IN
( SELECT city
FROM Customers );

Этот запрос будет производить вывод показанный в Таблице 13.2.

Однако, оператор ANY может использовать другие реляционные операторы кроме равняется (=), и таким образом делать сравнения которые являются выше возможностей IN. Например, мы могли бы найти всех продавцов с их заказчиками которые следуют им в алфавитном порядке ( вывод показан в Таблице 13.3)

SELECT *
FROM Salespeople
WHERE sname < ANY
( SELECT cname
FROM Customers);

Таблица 13. 2: Использование IN в качестве альтернативы к ANY

Таблица 13. 3: Использование оператора ANY с оператором "неравно" (<)

продавцов для их заказчиков которые упорядоченны в алфавитном порядке
( вывод показан в Таблице 13.3)

SELECT *
FROM Salespeople
WHERE sname < ANY
( SELECT cname
FROM Customers);

Все строки были выбраны для Serres и Rifkin, потому что нет других заказчиков чьи имена следовали бы за ими в алфавитном порядке.

Обратите внимание что это является d основнjм эквивалентом следующему запросу с EXISTS, чей вывод показывается в Таблице 13.4:

SELECT *
FROM Salespeople outer
WHERE EXISTS
( SELECT *
FROM Customers inner
WHERE outer.sname < inner.cname );

Таблица 13.4 Использование EXISTS как альтернатива оператору ANY

Любой запрос который может быть сформулирован с ANY ( или, как мы увидим, с ALL ), мог быть также сформулирован с EXISTS, хотя наоборот будет неверно. Строго говоря, вариант с EXISTS не абсолютно идентичен вариантам с ANY или с ALL из-за различия в том как обрабатываются пустые( NULL ) значения ( что будет обсуждаться позже в этой главе ). Тем ни менее, с технической точки зрения, вы могли бы делать это без ANY и ALL если бы вы стали очень находчивы в использовании EXISTS ( и IS NULL ).

Большинство пользователей, однако, находят ANY и ALL более удобными в использовании чем EXISTS, который требует соотнесенных подзапросов.

Кроме того, в зависимости от реализации, ANY и ALL могут, по крайней мере в теории, быть более эффективными чем EXISTS. Подзапросы ANY или ALL могут выполняться один раз и иметь вывод используемый чтобы определять предикат для каждой строки основного запроса. EXISTS, с другой стороны, берет соотнесенный подзапрос, который требует чтобы весь подзапрос повторно выполнялся для каждой строки основного запроса. SQL пытается найти наиболее эффективный способ выполнения любой команды, и может попробовать преобразовать менее эффективную формулу запроса в более эффективную (но вы не можете всегда рассчитывать на получение самой эффективной формулировки ).

Основная причина для формулировки EXISTS как альтернативы ANY и ALL в том что ANY и ALL могут быть несколько неоднозначен, из-за способа использования этого термина в Английском языке, как вы это скоро увидите. С приходом понимания различия способов формулирования данного запроса, вы сможете поработать над процедурами которые сейчас кажутся Вам трудными или неудобными.

КАК ANY МОЖЕТ СТАТЬ НЕОДНОЗНАЧНЫМ

Как подразумевалось выше, ANY не полностью однозначен. Если мы создаем запрос чтобы выбрать заказчиков которые имеют больший рейтинг чем любой заказчик в Риме, мы можем получить вывод который несколько отличался бы от того что мы ожидали ( как показано в Таблице 13.5 ):

SELECT *
FROM Customers
WHERE rating > ANY
( SELECT rating
FROM Customers
WHERE city=Rome );

В английском языке, способ которым мы обычно склонны интерпретировать оценку " больше чем любой ( где city=Rome ) " , должен вам сообщить что это значение оценки должно быть выше чем значение оценки в каждом случае где значение city=Rome. Однако это не так, в случае ANY - используемом в SQL . ANY оценивает как верно, если подзапрос находит любое значение которое делает условие верным.

Таблица 13.5 Как оператор "больше чем" (>) интерпретируется ANY

Если мы оценим ANY способом использующим грамматику Английского Языка, то только заказчики с оценкой 300 будут превышать Giovanni, который находится в Риме и имеет оценку 200. Однако, подзапрос ANY также находит Periera в Риме с оценкой 100. Так как все заказчики с оценкой 200 были выше этой, они будут выбраны, даже если имелся другой заказчик из Рима(Giovanni) чья оценка не была выше ( фактически, то что один из выбранных заказчиков также находится в Риме несущественно).

Так как подзапрос произвел по крайней мере одно значение которое сделает предикат верным в отношении этих строк, строки были выбраны. Чтобы дать другой пример, предположим что мы должны были выбирать все порядки сумм приоретений которые были больше чем по крайней мере один из порядков на 6-е Октября:

SELECT *
FROM Orders
WHERE amt > ANY
( SELECT amt
FROM Orders
WHERE odate=10/06/1990 );

Вывод для этого запроса показывается в Таблице 13.6.

Даже если самая высокая сумма приобретений в таблице (9891.88) - имелась на 6-е Октября, предыдущая строка имеет более высокое значение суммы чем другая строка на 6-е Октября, которая имела значение суммы=1309.95. Имея реляционный оператор ">=" вместо просто " > ", эта строка будет также выбирана, потому что она равна самой себе.

Конечно, вы можете использовать ANY с другой SQL техникой, например с техникой обьединения. Этот запрос будет находить все порядки со значением суммы меньшей чем значение любой суммы для заказчика в San Jose (вывод показывается в Таблице 13.7):

SELECT *
FROM Orders
WHERE amt < ANY
( SELECT amt
FROM Orders A, Customers b
WHERE a.cnum=b.cnum
AND b.city=" San Jose' );

Даже если нименьший порядок в таблице был для заказчика из San Jose, то был второй наибольший; следовательно почти все строки будут выбраны. Простой способ запомнить, что < ANY значение меньшее чем наибольшее выбранное значение, а > ANY значение большее чем наименьшее выбранное значение.

Таблица 13. 6: Выбранное значение больше чем любое(ANY) на 6-е Октября

Таблица 13. 7: Использование ANY с объединением

Фактически, вышеуказанные команды весьма похожи на следующее - (вывод показан в Таблице 13.8) :

SELECT *
FROM Orders
WHERE amt <
( SELECT MAX amt
FROM Orders A, Customers b
WHERE a.cnum=b.cnum
AND b.city=" San Jose' );

Таблица 13.8: Использование агрегатной функции вместо ANY

=========СПЕЦИАЛЬНЫЙ ОПЕРАТОР ALL ==========

С помощью ALL, предикат является верным, если каждое значение выбранное подзапросом удовлетворяет условию в предикате внешнего запроса.

Если мы хотим пересмотреть наш предыдущий пример чтобы вывести только тех заказчиков чьи оценки, фактически, выше чем у каждого заказчика в Париже, мы можем ввести следующее чтобы произвести вывод показанный в Таблтце 13.9:

SELECT *
FROM Customers
WHERE rating > ALL
(SELECT rating
FROM Customers
WHERE city=Rome ):

Таблица 13.9: Использование оператора ALL

Этот оператор проверяет значения оценки всех заказчиков в Риме. Затем он находит заказчиков с оценкой большей чем у любого из заказчиков в Риме. Самая высокая оценка в Риме - у Giovanni( 200 ). Следовательно, выбираются только значения выше этих 200.

Как и в случае с ANY, мы можем использовать EXISTS для производства альтернативной формулировки такого же запроса - ( вывод показан в Таблице 13.10 ):

SELECT *
FROM Customers outer
WHERE NOT EXISTS
( SELECT *
FROM Customers inner
WHERE outer.rating <=inner.rating
AND inner.city=|Rome| );

Таблица 13.10: Использование EXISTS в качестве альтернативы к ALL

РАВЕНСТВА И НЕРАВЕНСТВА

ALL используется в основном с неравенствами чем с равенствами, так как значение может быть "равным для всех" результатом подзапроса только если все результаты, фактически, идентичны. Посмотрите следующий запрос:

SELECT *
FROM Customers
WHERE rating=ALL
( SELECT rating
FROM Customers
WHERE city=" San Jose' );

Эта команда допустима, но , c этими данными, мы не получим никакого вывода. Только в единственом случае вывод будет выдан этим запросом - если все значения оценки в San Jose окажутся идентичными. В этом случае, можно сказать следующее :

SELECT *
FROM Customers
WHERE rating=
( SELECT DISTINCT rating
FROM Customers
WHERE city=" San Jose' );

Основное различие в том, что эта последняя команда должна потерпеть неудачу если подзапрос выведет много значений, в то время как вариант с ALL просто не даст никакого вывода. В общем, не самая удачная идея использовать запросы которые работают только в определенных ситуациях подобно этой. Так как ваша база данных будет постоянно меняться, это еудачный способ, чтобы узнать о ее содержании. Однако, ALL может более эффективно использоваться с неравенствами, то есть с оператором "< >". Но учтите что сказанное в SQL что - значение которое не равняется всем результатам подзапроса, - будет отличаться от того же но сказанного с учетом граматики Английского языка. Очевидно, если подзапрос возвращает много различных значений, как это обычно бывает, ни одно
отдельное значение не может быть равно им всем в обычном смысле. В SQL, выражение - < > ALL - в действительности соответствует " не равен любому " результату подзапроса. Другими словами, предикат верен, если данное значение не найдено среди результатов подзапроса. Следовательно, наш предыдущий пример противоположен по смыслу этому примеру (с выводом показанным в Таблице 13.11):

SELECT *
FROM Customers
WHERE rating < > ALL
( SELECT rating
FROM Customers
WHERE city=" San Jose' );

Таблица 13.11: Использование ALL с < >

Вышеупомянутый подзапрос выберает все оценки для города San Jose. Он выводит набор из двух значений: 200 ( для Liu ) и 300 (для Cisneros).

атем, основной запрос, выбирает все строки, с оценкой не совпадающей ни с одной из них - другими словами все строки с оценкой 100. Вы можете сформулировать тот же самый запрос используя оператор NOT IN:

SELECT*
FROM Customers
WHERE rating NOT IN
( SELECT rating
FROM Customers
WHERE city=" San Jose' );

Вы могли бы также использовать оператор ANY:

SELECT *
FROM Customers
WHERE NOT rating=ANY
( SELECT rating
FROM Customers
WHERE city=" San Jose' );

Вывод будет одинаков для всех трех условий.

ПРАВИЛЬНОЕ ПОНИМАНИЕ ANY И ALL

В SQL, сказать что - значение больше( или меньше ) чем любое(ANY) из набора значений - тоже самое что сказать, что оно больше( или меньше ) чем любое одно отдельное из этих значений. И наоборот, сказать что значение не равно всему(ALL) набору значений, тоже что сказать, что нет такого значения в наборе которому оно равно.

КАК ANY, ALL, И EXIST ПОСТУПАЮТM С ОТСУТСТВУЮЩИМИ И НЕИЗВЕСТНЫМИ ДАННЫМИ

Как было сказано, имеются некоторые различия между EXISTS и операторами представленными в этой главе относительно того как они обрабатывают оператор NULL. ANY и ALL также отличаются друг от друга тем как они реагируют если подзапрос не произвел никаких значений чтобы использовать их в сравнении. Эти различия могут привести к непредвиденным результатам на Ваши запросы если вы не будете их учитывать.

КОГДА ПОДЗАПРОС ВОЗВРАЩАЕТСЯ ПУСТЫМ

Одно значительное различие между ALL и ANY - способ действия в cитуации когда подзапрос не возвращает никаких значений. В принципе, всякий раз, когда допустимый подзапрос не в состоянии сделать вывод, ALL - автоматически верен, а ANY автоматически неправилен. Это означает, что следующий запрос

SELECT *
FROM Customers
WHERE rating > ANY
( SELECT rating
FROM Customers
WHERE city=Boston );

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

SELECT
FROM Customers
WHERE rating > ALL
( SELECT rating
FROM Customers
WHERE city='Boston' );

выведет всю таблицу Заказчиков. Когда нет никаких заказчиков в Boston,
естественно, ни одно из этих сравнений не имеет значення.

ANY И ALL ВМЕСТО EXISTS С ПУСТЫМ УКАЗАТЕЛЕМ( NULL )

Значения NULL также имеют некоторые проблемы с операторами наподобие этих. Когда SQL сравнивает два значения в предикате, одно из которых пустое (NULL), то результат неизвестен ( смотрите Главу 5 ). Неизвестный предикат, подобен неверному и является причиной того что строка не выбирается, но работать он будет иначе в некоторых похожих запросах, в зависимости от того, используют они ALL или ANY вместо EXISTS. Рассмотрим наш предыдущий пример:

SELECT *
FROM Customers
WHERE rating > ANY
( SELECT rating
FROM Customers
WHERE city='Rome' );

и еще один пример:

SELECT *
FROM Customers outer
WHERE EXISTS
( SELECT *
FROM Customers inner
WHERE outer.rating > inner.rating
AND inner.city='Rome' );

В общем, эти два запроса будут вести себя одинаково. Но предположим, что появилось пустое(NULL) значение в столбце rating таблицы Заказчиков:

CNUM CNAME CITY RATING SNUM 2003 Liu SanJose NULL 1002

В варианте с ANY, где оценка Liu выбрана основным запросом, значение NULL делает предикат неизвестным а строка Liu не выбирается для вывода. Однако, в варианте с NOT EXISTS когда эта строка выбрана основным запросом, значение NULL используется в предикате подзапроса, делая его неизвестным в каждом случае. Это означает что подзапрос не будет производить никаких значений, и EXISTS будет неправилен. Это, естественно, делает оператор NOT EXISTS верным. Следовательно, строка Liu будет выбрана для вывода. Это основное расхождение, в отличие от других типов предикатов, где значение EXISTS независимо от того верно оно или нет - всегда неизвестно. Все это является аргументом в пользу использования варианта формулировки с ANY. Мы не считаем что значение NULL является выше чем допустимое значение. Более того, результат будет тот же, если мы будем проверять для более низкого значения.

ИСПОЛЬЗОВАНИЕ COUNT ВМЕСТО EXISTS

Подчеркнем, что все формулировки с ANY и ALL могут быть в точности выполнены с EXISTS, в то время как наоборот будет неверно. Хотя в этом случае, также верно и то что EXISTS и NOT EXISTS подзапросы могут обманывать при выполнении тех же самых подзапросов с COUNT(*) в предложения SELECT подзапроса. Если больше чем ноль строк выводе будет подсчитано, это эквивалентно EXISTS; в противном случае это работает также как NOT EXISTS. Следующее является этому примером (вывод показывается в Таблице 13.12 ):

SELECT *
FROM Customers outer
WHERE NOT EXISTS
( SELECT *
FROM Customers inner
WHERE outer.rating <=inner.rating
AND inner.city='Rome' );

Таблица 13.12: Использование EXISTS с соотнесенным подзапросом

Это должно также быть выполнено как

SELECT *
FROM Customers outer
WHERE 1 >
( SELECT COUNT (*)
FROM Customers inner
WHERE outer.rating <=inner.rating
AND inner.city='Rome' );

Вывод к этому запросу показывается в Таблице 13.13.

Теперь Вы начинаете понимать сколько способов имеется в SQL. Если это все кажется несколько путанным на этой стадии, нет причины волноваться. Вы обучаетесь чтобы использовать ту технику которая лучше всего отвечает вашим требованиям и наиболее понятна для вас. Начиная с этого места, мы хотим показать Вам большое количество возможностей, что бы вы могли найти ваш собственный стиль.

Таблица 13.13: Использование COUNT вместо EXISTS

==============РЕЗЮМЕ================

Итак, вы прошли много чего в этой главе. Подзапросы не простая тема, и мы потратили много время чтобы показать их разновидности и неоднозначности. То чему Вы теперь научились, вещи достаточно глубокие. Вы знаете несколько технических решений одной проблемы, и поэтому вы можете выбрать то которое более подходит вашим целям. Кроме того, вы поняли, как различные формулировки будет обрабатывать пустые значения (NULL) и ошибки.

Теперь, когда вы полностью изучили запросы, наиболее важный, и вероятно наиболее сложный, аспект SQL, объем другого материала будет относительно прост для понимания.

Мы имеем еще одну главу о запросах, которая покажет вам как объединить выводы любого числа запросов в единое тело, с помощью формирования объединения многочисленых запросов используя оператор UNION.

************** РАБОТА С SQL **************

1. Напишите запрос который бы выбирал всех заказчиков чьи оценки равны или больше чем любая( ANY ) оценка заказчика Serres.
2. Что будет выведено вышеупомянутой командой?
3. Напишите запрос использующий ANY или ALL, который бы находил всех продавцов которые не имеют никаких заказчиков размещенных в их городе.
4. Напишите запрос который бы выбирал все порядки с суммой больше чем любая ( в обычном смысле ) для заказчиков в Лондоне.
5. Напишите предыдущий запрос с использованием - MAX.