12. ИСПОЛЬЗОВАНИЕ ОПЕРАТОРА EXISTS.
ТЕПЕРЬ, КОГДА ВЫ ХОРОШО ОЗНАКОМЛЕНЫ С ПОДЗАПРОСАМИ, мы можем гово-
рить о некоторых специальных операторах которые всегда берут подзапро-
сы как аргументы. Вы узнаете о первом из их в этой главе. Остальные
будут описан в следующей главе.
Оператор EXISTS используется чтобы указать предикату, - производить
ли подзапросу вывод или нет. В этой главе, вы узнаете как использовать
этот оператор со стандартными и ( обычно ) соотнесенными подзапросами.
Мы будем также обсуждать специальные расмышления которые перейдут в
игру когда вы будете использовать этот оператор как относительный аг-
регат, как пустой указатель NULL, и как оператор Буля. Кроме того, вы
можете повысить ваш профессиональный уровень относительно подзапросов
исследуя их в более сложных прикладных программах чем те которые мы
видели до сих пор.
========== КАК РАБОТАЕТ EXISTS? ==========
EXISTS - это оператор, который производит верное или неверное значе-
ние, другими словами, выражение Буля ( см. Главу 4 для обзора этого
термина ). Это означает что он может работать автономно в предикате
или в комбинации с другими выражениями Буля использующими Булевы опе-
раторы AND, OR, и NOT. Он берет подзапрос как аргумент и оценивает его
как верный если тот производит любой вывод или как неверный если тот
не делает этого. Этим он отличается от других операторов предиката, в
которых1 он не может быть неизвестным. Например, мы можем решить, изв-
лекать ли нам некоторые данные из таблицы Заказчиков если, и только
если, один или более заказчиков в этой таблице находятсяся в San Jose
( вывод для этого запроса показывается в Рисунке 12.1 ):
SELECT cnum, cname, city
FROM Customers
WHERE EXISTS
( SELECT *
FROM Customers
WHERE city = " San Jose' );
Внутренний запрос выбирает все данные для всех заказчиков в San Jo-
se. Оператор EXISTS во внешнем предикате отмечает, что некоторый вывод
был произведен подзапросом, и поскольку выражение EXISTS было полным
предикатом, делает предикат верным. Подзапрос( не соотнесенный ) был
выполнен только один раз для всего внешнего запроса, и следовательно,
=============== 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
имеет одно значение во всех случаях. Поэтому 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 = se-
cond.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 );
EXISTS И АГРЕГАТЫ
Одна вещь которую EXISTS не может сделать - взять функцию агрегата в
подзапросе. Это имеет значение. Если функция агрегата находит любые
строки для операций с ними, EXISTS верен, не взирая на то, что это -
зна-| чение функции ; если же агрегатная функция не находит никаких
строк, EXISTS неправилен.
=============== 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
(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 **************
1. Напишите запрос который бы использовал оператор EXISTS для извлече-
ния всех продавцов которые имеют заказчиков с оценкой 300.
2. Как бы вы решили предыдущую проблему используя обьединение ?
3. Напишите запрос использующий оператор EXISTS который выберет всех
продавцов с заказчиками размещенными в их городах которые ими не
обслуживаются.
4. Напишите запрос который извлекал бы из таблицы Заказчиков каждого
заказчика назначенного к продавцу который в данный момент имеет по
крайней мере еще одного заказчика ( кроме заказчика которого вы вы-
берете ) с порядками в таблице Порядков ( подсказка: это может быть
похоже на структуру в примере с нашим трех-уровневым подзапросом ).
( См. Приложение A для ответов. )
|
