VB & SQL

Глава 12

S Q L

ИСПОЛЬЗОВАНИЕ ОПЕРАТОРА 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' );

Внутренний запрос выбирает все данные для всех заказчиков в San Jose. Оператор 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 = 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, покажутся Вам эле- ментарными.

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

РЕЗЮМЕ

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

РАБОТА С SQL

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

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

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

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

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

> НАЗАД <

DDA Software HOME page

ddasoft@narod.ru

Updated: 05.2003

Сайт управляется системой uCoz