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

ВЫБОР СТОЛБЦОВ С ПОМОЩЬЮ 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 );

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, покажутся Вам эле- ментарными.

РЕЗЮМЕ

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

РАБОТА С SQL

1. Напишите запрос который бы использовал оператор EXISTS для извлечения всех продавцов которые имеют заказчиков с оценкой 300.
2. Как бы вы решили предыдущую проблему используя объединение ?
3. Напишите запрос использующий оператор EXISTS который выберет всех продавцов с заказчиками размещенными в их городах которые ими не обслуживаются.
4. Напишите запрос который извлекал бы из таблицы Заказчиков каждого заказчика назначенного к продавцу который в данный момент имеет по крайней мере еще одного заказчика ( кроме заказчика которого вы выберете ) с порядками в таблице Порядков ( подсказка: это может быть похоже на структуру в примере с нашим трех-уровневым подзап- росом ).