Читаем без скачивания Понимание SQL - Мартин Грубер

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

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

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

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

Глава 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) из их равняется значению города текущей строки внешнего запроса.

SELECT * FROM Salespeople WHERE city=ANY

(SELECT city FROM Customers);

cnum

cname

city

comm

1001

Peel

London

0.12

1002

Serres

San Jose

0.13

1004

Motika

London

0.11

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

cnum

cname

city

comm

1001

Peel

London

0.12

1002

Serres

San Jose

0.13

1004

Motika

London

0.11

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

SELECT * FROM Salespeople

WHERE sname < ANY (SELECT cname FROM Customers);

cnum

cname

city

comm

1001

Peel

London

0.12

1004

Motika

London

0.11

1003

Axelrod

New York

0.10

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

cnum

cname

city

comm

1001

Peel

London

0.12

1004

Motika

London

0.11

1003

Axelrod

New York

0.10

Таблица 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 оценивает как верно, если подзапрос находит любое значение которое делает условие верным.

SELECT * FROM Customers WHERE rating > ANY

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

cnum

cname

city

rating

snum

2002

Giovanni

Rome

200

1003

2003

Liu

San Jose

200

1002

2004

Grass

Berlin

300

1002

2008

Cisneros

San Jose

300

1007

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