Перейти к оглавлению
                  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 для ответов. )
 
 
 
 
 
Перейти к оглавлению
Hosted by uCoz