ИСПОЛЬЗОВАНИЕ 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

AND1<

(SELECT CONT (*) FROM Orders

WHERE Orders.cnum

cnum

1001 1002 1007

cname

Peel

Serres

Rifkin

second.cnum)); city

comm

London San Jose Barselona

0.17 0.13 0.15

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

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

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

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

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

РЕЗЮМЕ

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

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

РАБОТА С SQL

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

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

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

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

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