Упорядочение с помощью оператора NULL 3 страница

SELECT *
FROM Orders outer
WHERE amt > (SELECT AVG amt
FROM Orders inter
WHERE inner.cnum = outer.cnum);

=============== SQL Execution Log ==============
| SELECT * |
| FROM Orders outer |
| WHERE amt > |
| (SELECT AVG (amt) |
| FROM Orders inner |
| WHERE inner.cnum = outer.cnum |
| =============================================== |
| onum amt odate cnum snum |
| ----- -------- ---------- ----- ------ |
| 3006 1098.19 10/03/1990 2008 1007 |
| 3010 1309.00 10/06/1990 2004 1002 |
| 3011 9891.88 10/06/1990 2006 1001 |
=================================================

Рисунок 11.4: Соотнесение таблицы с собой

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

SELECT *
FROM Orders outer
WHERE amt >= (SELECT AVG (amt)
FROM Orders inner
WHERE inner.cnum = outer.cnum);

=============== SQL Execution Log ==============
| SELECT * |
| FROM Orders outer |
| WHERE amt > = |
| (SELECT AVG (amt) |
| FROM Orders inner |
| WHERE inner.cnum = outer.cnum); |
| =============================================== |
| onum amt odate cnum snum |
| ----- -------- ---------- ----- ------ |
| 3003 767.19 10/03/1990 2001 1001 |
| 3002 1900.10 10/03/1990 2007 1004 |
| 3005 5160.45 10/03/1990 2003 1002 |
| 3006 1098.19 10/03/1990 2008 1007 |
| 3009 1713.23 10/04/1990 2002 1003 |
| 3010 1309.95 10/06/1990 2004 1002 |
| 3011 9891.88 10/06/1990 2006 1001 |
=================================================

Рисунок 11.5: Выбераются порядки которые >= средней сумме приобретений для их заказчиков.

Различие, конечно, в том, что реляционный оператор основного предиката включает значения которые равняются среднему (что обычно означает что они — единственые порядки для данных заказчиков).

СООТНЕСЕННЫЕ ПОДЗАПРОСЫ В ПРЕДЛОЖЕНИИ HAVING

Также как предложение HAVING может брать подзапросы, он может брать и соотнесенные подзапросы. Когда вы используете соотнесенный подзапрос в предложении HAVING, вы должны ограничивать внешние ссылки к позициям которые могли бы непосредственно использоваться в самом предложении HAVING. Вы можете вспомнить из Главы 6 что предложение HAVING может использовать только агрегатные функции которые указаны в их предложении SELECT или поля используемые в их предложении GROUP BY. Они являются только внешними ссылками, которые вы можете делать. Все это потому, что предикат предложения HAVING оценивается для каждой группы из внешнего запроса, а не для каждой строки. Следовательно, подзапрос будет выполняться один раз для каждой группы выведеной из внешнего запроса, а не для каждой строки.

Предположим, что вы хотите суммировать значения сумм приобретений покупок из таблицы Порядков, сгруппировав их по датам, удалив все даты где бы SUM не был по крайней мере на 2000.00 выше максимальной (MAX) суммы:

SELECT odate, SUM (amt)
FROM Orders a
GROUP BY odate
HAVING SUM (amt) > (SELECT 2000.00 + MAX (amt)
FROM Orders b
WHERE a.odate = b.odate);

Подзапрос вычисляет значение MAX для всех строк с той же самой датой что и у текущей агрегатной группы основного запроса. Это должно быть выполнено, как и ранее, с испошльзованием предложения WHERE. Сам подзапрос не должен использовать предложения GROUP BY или HAVING.

СООТНЕСЕННЫЕ ПОДЗАПРОСЫ И ОБЬЕДИНЕНИЯ

Как вы и могли предположить, соотнесенные подзапросы по природе близки к обьединениям — они оба включают проверку каждой строки одной таблицы с каждой строкой другой (или псевдонимом из той же) таблицы. Вы найдете, что большинство операций, которые могут выполняться с одним из них, будут также работать и с другим.

Однако имеется различие в прикладной программе между ними, такое как вышеупомянутая потребность в использовании DISTINCT с обьединением и его необязательность с подзапросом. Имеются также некоторые вещи, которые каждый может делать так, как этого не может другой. Подзапросы, например, могут использовать агрегатную функцию в предикате, делая возможным выполнение операций типа нашего предыдущего примера, в котором мы извлекли порядки, усредненные для их заказчиков.

Обьединения, с другой стороны, могут выводить строки из обеих сравниваемых таблиц, в то время как вывод подзапросов используется только в предикатах внешних запросов. Как правило, форма запроса которая кажется наиболее интуитивной будет вероятно лучшей в использовании, но при этом хорошо бы знать обе техники для тех ситуаций когда та или иная могут не работать.

РЕЗЮМЕ

Вы можете поздравлять себя с овладением большого куска из рассмотреных понятий в SQL — соотнесенного подзапроса. Вы видели как соотнесенный подзапрос связан с обьединение, а также, как как его можно использовать с агрегатными функциями и в предложении HAVING. В общем, вы теперь узнали все типы подзапросов полностью.

Следующий шаг — описание некоторых специальных операторов SQL. Они берут подзапросы как аргументы, как это делает IN, но в отличие от IN, они могут использоваться только в подзапросах. Первый из их, представленный в Главе 12, — называется EXISTS.

РАБОТА С SQL

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

Напишите два запроса, которые выберут всех продавцов (по их имени и номеру) которые, в своих городах имеют заказчиков, которых они не обслуживают. Один запрос — с использованием обьединения и один — с соотнесенным подзапросом. Которое из решений будет более изящным? (Подсказка: один из способом это сделать, состоит в том, чтобы находить всех заказчиков, не обслуживаемых данным продавцом и определить, находится ли каждый из них в городе продавца.)

(См. Приложение A для ответов.)

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

ТЕПЕРЬ, КОГДА ВЫ ХОРОШО ОЗНАКОМЛЕНЫ С ПОДЗАПРОСАМИ, мы можем говорить о некоторых специальных операторах, которые всегда берут подзапросы как аргументы. Вы узнаете о первом из них в этой главе. Остальные будут описаны в следующей главе.

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

КАК РАБОТАЕТ EXISTS?

EXISTS — это оператор, который производит верное или неверное значение, другими словами, выражение Буля (см. Главу 4 для обзора этого термина). Это означает, что он может работать автономно в предикате или в комбинации с другими выражениями Буля, использующими Булевы операторы AND, OR, и NOT. Он берет подзапрос как аргумент и оценивает его как верный, если тот производит любой вывод или как неверный, если тот не делает этого. Этим он отличается от других операторов предиката, в которых он не может быть неизвестным. Например, мы можем решить, извлекать ли нам некоторые данные из таблицы Заказчиков если, и только если, один или более заказчиков в этой таблице находятсяся в San Jose (вывод для этого запроса показывается в Рисунке 12.1):

SELECT cnum, cname, city
FROM Customers
WHERE EXISTS (SELECT *
FROM Customers
WHERE city = " San Jose');

=============== SQL Execution Log ============
| SELECT snum, sname, city |
| FROM Customers |
| WHERE EXISTS |
| (SELECT * |
| FROM Customers |
| WHERE city = 'San Jose'); |
| ============================================= |
| cnum cname city |
| ----- -------- ---- |
| 2001 Hoffman London |
| 2002 Giovanni Rome |
| 2003 Liu San Jose |
| 2004 Grass Berlin |
| 2006 Clemens London |
| 2008 Cisneros San Jose |
| 2007 Pereira Rome |
===============================================

Рисунок 12.1 Использование оператора EXISTS

Внутренний запрос выбирает все данные для всех заказчиков в San Jose. Оператор EXISTS во внешнем предикате отмечает, что некоторый вывод был произведен подзапросом, и поскольку выражение EXISTS было полным предикатом, делает предикат верным. Подзапрос (не соотнесенный) был выполнен только один раз для всего внешнего запроса, и следовательно имеет одно значение во всех случаях. Поэтому EXISTS, когда используется этим способом, делает предикат верным или неверным для всех строк сразу, что это не так уж полезно для извлечения определенной информации.

ВЫБОР СТОЛБЦОВ С ПОМОЩЬЮ EXISTS

В вышеупомянутом примере, EXISTS должен быть установлен так чтобы легко выбрать один столбец, вместо того, чтобы выбирать все столбцы используя в выборе звезду (SELECT *) В этом состоит его отличие от подзапроса который (как вы видели ранее в Главе 10 мог выбрать только один столбец).

Однако, в принципе он мало отличается при выборе EXISTS столбцов, или когда выбираются все столбцы, потому что он просто замечает — выполняется или нет вывод из подзапроса — а не использует выведенные значения.

ИСПОЛЬЗОВАНИЕ EXISTS С СООТНЕСЕННЫМИ ПОДЗАПРОСАМИ

В соотнесенном подзапросе предложение EXISTS оценивается отдельно для каждой строки таблицы, имя которой указано во внешнем запросе, точно также как и другие операторы предиката, когда вы используете соотнесенный подзапрос. Это дает возможность использовать EXISTS как верный предикат, который генерирует различные ответы для каждой строки таблицы, указанной в основном запросе. Следовательно информация из внутреннего запроса будет сохранена, если выведена непосредственно, когда вы используете EXISTS таким способом. Например, мы можем вывести продавцов, которые имеют многочисленых заказчиков (вывод для этого запроса показывается в Рисунке 12.2):

SELECT DISTINCT snum
FROM Customers outer
WHERE EXISTS (SELECT *
FROM Customers inner
WHERE inner.snum = outer.snum
AND 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 (сответствующего другим заказчикам). Если любые такие строки найдены внутренним запросом, это означает, что имеются два разных заказчика, обслуживаемых текущим продавцом (т.е. продавцом заказчика в текущей строке-кандидата из внешнего запроса). Предикат EXISTS поэтому верен для текущей строки, и номер продавца — поле (snum) таблицы, указанной во внешнем запросе, будет выведен. Если был DISTINCT не указан, каждый из этих продавцов будет выбран один раз для каждого заказчика, к которому он назначен.

КОМБИНАЦИЯ ИЗ EXISTS И ОБЬЕДИНЕНИЯ

Однако для нас может быть полезнее вывести больше информации об этих продавцах, а не только их номера. Мы можем сделать это, объединив таблицу Заказчиков с таблицей Продавцов (вывод для запроса показывается в Рисунке 12.3):

SELECT DISTINCT first.snum, sname, first.city
FROM Salespeople first, Customers second
WHERE EXISTS (SELECT *
FROM Customers third
WHERE second.snum = third.snum
AND second.cnum < > third.cnum)
AND first.snum = second.snum;

=============== SQL Execution Log ============
| SELECT DISTINCT first.snum, sname, first.city |
| FROM Salespeople first, Customers second |
| WHERE EXISTS |
| (SELECT * |
| FROM Customers third |
| WHERE second.snum = third.snum |
| AND second.cnum < > third.cnum) |
| AND first.snum = second.snum; |
| ============================================= |
| cnum cname city |
| ----- -------- ---- |
| 1001 Peel London |
| 1002 Serres San Jose |
===============================================

Рисунок 12.3: Комбинация EXISTS с обьединением

Внутренний запрос здесь — как и в предыдущем варианте, фактически сообщает, что псевдоним был изменен. Внешний запрос — это обьединение таблицы Продавцов с таблицей Заказчиков, наподобии того, что мы видели прежде. Новое предложение основного предиката (AND first.snum = second.snum) естественно оценивается на том же самом уровне, что и предложение EXISTS. Это — функциональный предикат самого обьединения, сравнивающий две таблицы из внешнего запроса в терминах поля snum, которое являются для них общим. Из-за Булева оператора AND, оба условия основного предиката должны быть верны в порядке для верного предиката. Следовательно, результаты подзапроса имеют смысл только в тех случаях когда вторая часть запроса верна, а обьединение — выполняемо. Таким образом комбинация объединения и подзапроса может стать очень мощным способом обработки данных.

ИСПОЛЬЗОВАНИЕ NOT EXISTS

Предыдущий пример дал понять что EXISTS может работать в комбинации с операторами Буля. Конечно, то что является самым простым способом для использования и вероятно наиболее часто используется с EXISTS — это оператор NOT. Один из способов которым мы могли бы найти всех продавцов только с одним заказчиком будет состоять в том, чтобы инвертировать наш предыдущий пример. (Вывод для этого запроса показывается в Рисунке 12.4.)

SELECT DISTINCT snum
FROM Customers outer
WHERE NOT EXISTS (SELECT *
FROM Customers inner
WHERE inner.snum = outer.snum
AND inner.cnum <> outer.cnum);

=============== SQL Execution Log ============
| SELECT DISTINCT snum |
| FROM Salespeople outer |
| WHERE NOT EXISTS |
| (SELECT * |
| FROM Customers inner |
| WHERE inner.snum = outer.snum |
| AND inner.cnum < > outer.cnum); |
| ============================================= |
| cnum |
| ----- |
| 1003 |
| 1004 |
| 1007 |
===============================================

Рисунок 12.4: Использование EXISTS с NOT

EXISTS И АГРЕГАТЫ

Одна вещь, которую EXISTS не может сделать — взять функцию агрегата в подзапросе. Это имеет значение. Если функция агрегата находит любые строки для операций с ними, EXISTS верен, не взирая на то, что это — значение функции; если же агрегатная функция не находит никаких строк, EXISTS неправилен.

Попытка использовать агрегаты с EXISTS таким способом, вероятно покажет что проблема неверно решалась от начала до конца.

Конечно, подзапрос в предикате EXISTS может также использовать один или более из его собственных подзапросов. Они могут иметь любой из различных типов которые мы видели (или который мы будем видеть). Такие подзапросы, и любые другие в них, позволяют использовать агрегаты, если нет другой причины по которой они не могут быть использованы. Следующий раздел приводит этому пример.

В любом случае, вы можете получить тот же самый результат более легко, выбрав поле которое вы использовали в агрегатной функции, вместо использования самой этой функции. Другими словами, предикат — EXISTS (SELECT COUNT (DISTINCT sname) FROM Salespeople) — будет эквивалентен EXISTS (SELECT sname FROM Salespeople) который был позволен выше.

БОЛЕЕ УДАЧНЫЙ ПРИМЕР ПОДЗАПРОСА

Возможные прикладные программы подзапросов могут становиться многократно вкладываемыми.

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

Имеется запрос, который извлекает строки всех продавцов которые имеют заказчиков с больше чем одним текущим порядком. Это не обязательно самое простое решение этой проблемы, но оно предназначено скорее показать улучшеную логику SQL. Вывод этой информации связывает все три наши типовых таблицы:

SELECT *
FROM Salespeople first
WHERE EXISTS (SELECT *
FROM Customers second
WHERE first.snum = second.snum
AND 1 < (SELECT COUNT (*)
FROM Orders
WHERE Orders.cnum = second.cnum));

Вывод для этого запроса показывается в Рисунке 12.5.

=============== SQL Execution Log ============
| FROM Salespeople first |
| WHERE EXISTS |
| (SELECT * |
| FROM Customers second |
| WHERE first.snum = second.snum |
| AND 1 < |
| (SELECT CONT (*) |
| FROM Orders |
| WHERE Orders.cnum = second.cnum)); |
| ============================================= |
| cnum cname city comm |
| ----- -------- ---- -------- |
| 1001 Peel London 0.17 |
| 1002 Serres San Jose 0.13 |
| 1007 Rifkin Barselona 0.15 |
===============================================

Рисунок 12.5: Использование EXISTS с комплексным подзапросом

Мы могли бы разобрать вышеупомянутый запрос примерно так:

Берем каждую строку таблицы Продавцов как строку-кандидат (внешний запрос) и выполняем подзапросы. Для каждой строки-кандидата из внешнего запроса, берем в соответствие каждую строку из таблицы Заказчиков (средний запрос). Если текущая строка заказчиков не совпадает с текущей строкой продавца (т.е. если first.snum < > second.snum), предикат среднего запроса неправилен. Всякий раз, когда мы находим заказчика в среднем запросе который совдает с продавцом во внешнем запросе, мы должны рассматривать сам внутренний запрос чтобы определить, будет ли наш средний предикат запроса верен. Внутренний запрос считает число порядков текущего заказчика (из среднего запроса). Если это число больший чем 1, предикат среднего запроса верен, и строки выбираются. Это делает EXISTS предикат внешнего запроса верным для текущей строки продавца, и означает, что по крайней мере один из текущих заказчиков продавца имеет более чем один порядок.

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

Кроме того, этот запрос, даже если он кажется удобным, довольно извилистый способ извлечения информации и делает много работы. Он связывает три разных таблицы чтобы дать вам эту информацию, а если таблиц больше чем здесь указано, будет трудно получить ее напрямую (хотя это не единственный способ, и не обязательно лучший способ в SQL). Возможно вам нужно увидеть эту информацию относительно регулярной основы — если, например, вы имеете премию в конце недели для продавца который получил многочисленые порядки от одного заказчика. В этом случае, он должен был бы вывести команду, и сохранять ее чтобы использовать снова и снова по мере того как данные будут меняться (лучше всего сделать это с помощью представления, которое мы будем проходить в Главе 20).

РЕЗЮМЕ

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

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

РАБОТА С SQL

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

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

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

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

(См. Приложение A для ответов.)

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

=============== SQL Execution Log ============
| 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.

=============== SQL Execution Log ============
| 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

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

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

=============== SQL Execution Log ============
| 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 с оператором "неравно" (<)

Все строки были выбраны для Serres и Rifkin, потому что нет других заказчиков чьи имена следовали бы за ими в алфавитном порядке. Обратите внимание что это является основным эквивалентом следующему запросу с EXISTS, чей вывод показывается в Рисунке 13.4:

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

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

=============== SQL Execution Log ============
| 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 |
===============================================

Дата добавления: 2015-08-13