Мартин Грубер - Понимание SQL

Следующим шагом будет овладение тремя другими специальными операторами которые берут подзапросы как аргументы, это - ANY, ALL, и SOME. Как вы увидете в Главе 13, это - альтернативные формулировки некоторых вещей которые вы уже использовали, но которые в некоторых случаях, могут оказаться более предпочтительными.

Напишите запрос который бы использовал оператор EXISTS для извлечения всех продавцов которые имеют заказчиков с оценкой 300.

* Как бы вы решили предыдущую проблему используя обьединение?

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

* Напишите запрос который извлекал бы из таблицы Заказчиков каждого заказчика назначенного к продавцу который в данный момент имеет по крайней мере еще одного заказчика (кроме заказчика которого вы выберете ) с порядками в таблице Порядков (подсказка: это может быть похоже на структуру в примере с нашим трех-уровневым подзапросом ).

ТЕПЕРЬ, КОГДА ВЫ ОВЛАДЕЛИ ОПЕРАТОРОМ 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) из их равняется значению города текущей строки внешнего запроса.

SELECT * FROM Salespeople WHERE city=ANY

(SELECT city FROM Customers);

Таблица 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);

SELECT * FROM Salespeople

WHERE city IN (SELECT city FROM Customers);

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

SELECT * FROM Salespeople

WHERE sname < ANY (SELECT cname FROM Customers);

Таблица 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 );

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 пытается найти наиболее эффективный способ выполнения любой команды, и может попробовать преобразовать менее эффективную формулу запроса в более эффективную (но вы не можете всегда рассчитывать на получение самой эффективной формулировки ).