Перейти к оглавлению
                      11. СООТНЕСЕННЫЕ ПОДЗАПРОСЫ.
 
 
 
 
   В ЭТОЙ ГЛАВЕ,  МЫ ПРЕДСТАВИМ ВАС ТИПУ подзапроса о котором мы не го-
 ворили в Главе 10 - посвященной соотнесенному подзапросу.  Вы  узнаете
 как использовать соотнесенные подзапросы в предложениях запросов WHERE
 и HAVING.  Сходства и различия между соотнесенными подзапросами и обь-
 единениями будут обсуждаться далее,  и вы сможете повысить ваше знание
 псевдонимов и префиксов имени таблицы - когда они необходимы и как  их
 использовать.
 
 
                            КАК СФОРМИРОВАТЬ
             =========  СООТНЕСЕННЫЙ ПОДЗАПРОС ===========
 
 
   Когда вы используете подзапросы в SQL,  вы можете обратиться к внут-
 реннему запросу таблицы в предложении внешнего запроса FROM ,  сформи-
 ровав - соотнесенный подзапрос. Когда вы делаете это, подзапрос выпол-
 няется неоднократно, по одному разу для каждой строки таблицы основно-
 го запроса.  Соотнесенный подзапрос - один из большого количества тон-
 ких понятий в SQL из-за сложности в его оценке.  Если вы сумеете овла-
 деть им,  вы найдете что он очень мощный,  потому что может  выполнять
 сложные функции с помощью очень лаконичных указаний.
 
 
   Например, имеется  один  способ  найти всех заказчиков в порядках на
 3-е Октября ( вывод показывается в Рисунке 11.1 ):
 
 
              SELECT *
                 FROM Customers outer
                 WHERE 10/03/1990 IN
                   ( SELECT odate
                        FROM Orders inner
                        WHERE outer.cnum = inner.cnum );
 
 
                  КАК РАБОТАЕТ СООТНЕСЕННЫЙ ПОДЗАПРОС
 
 
   В вышеупомянутом  примере,  "внутренний"(inner)  и "внешний"(outer),
 это псевдонимы,  подобно обсужденным в Главе 9.  Мы выбрали эти  имена
 для  большей  ясности;  они  отсылают к значениям внутренних и внешних
 запросов, соответственно. Так как значение в поле cnum внешнего запро-
 са меняется,  внутренний запрос должен выполняться отдельно для каждой
 строки внешнего запроса. Строка внешнего запроса для которого внутрен-
 
 
                ===============  SQL Execution Log ============
               |                                               |
               | SELECT *                                      |
               | FROM  Customers outer                         |
               | WHERE 10/03/1990 IN                           |
               | (SELECT odate                                 |
               | FROM Orders inner                             |
               | WHERE outer.cnum = inner.cnum);               |
               | ============================================= |
               |   cnum     cname     city    rating    snum   |
               |  -----    --------   ----    ------   -----   |
               |   2001    Hoffman    London     100    1001   |
               |   2003    Liu        San Jose   200    1002   |
               |   2008    Cisneros   San Jose   300    1007   |
               |   2007    Pereira    Rome       100    1004   |
                 =============================================
 Рисунок 11.1: Использование соотнесенного подзапроса
 
 
 ний запрос каждый раз  будет  выполнен,  называется  -  текущей  стро-
 кой-кандидатом. Следовательно, процедура оценки выполняемой соотнесен-
 ным подзапросом - это:
 
 
 1. Выбрать строку из таблицы именованной в внешнем запросе.  Это будет
    текущая строка-кандидат.
 2. Сохранить значения из этой строки-кандидата в псевдониме с именем в
    предложении FROM внешнего запроса.
 3. Выполнить подзапрос.  Везде, где псевдоним данный для внешнего зап-
    роса найден ( в этом случае "внешний" ),  использовать значение для
    текущей строки-кандидата. Использование значения из строки-кандида-
    та внешнего запроса в подзапросе называется - внешней ссылкой.
 4. Оценить предикат внешнего запроса на основе результатов  подзапроса
    выполняемого в шаге 3. Он определяеть - выбирается ли строка-канди-
    дат для вывода.
 5. Повторить  процедуру для следующей строки-кандидата таблицы,  и так
    далее пока все строки таблицы не будут проверены.
 
 
   В вышеупомянутом примере, SQL осуществляет следующую процедуру:
 
 
 1. Он выбирает строку Hoffman из таблицы Заказчиков.
 2. Сохраняет  эту строку как текущую строку-кандидат под псевдонимом -
    "внешним".
 3. Затем  он выполняет подзапрос.  Подзапрос просматривает всю таблицу
    Порядков чтобы найти строки где значение cnum поле - такое  же  как
    значение outer.cnum,  которое в настоящее время равно 2001,  - поле
    cnum строки Hoffmanа. Затем он извлекает поле odate из каждой стро-
    ки таблицы Порядков для которой это верно, и формирует набор значе-
    ний поля odate.
 4. Получив  набор всех значений поля odate,  для поля cnum = 2001,  он
    проверяет предикат основного запроса чтобы видеть имеется ли значе-
    ние на 3 Октября в этом наборе.  Если это так(а это так), то он вы-
    бирает строку Hoffmanа для вывода ее из основного запроса.
 5. Он  повторяет  всю  процедуру,  используя строку Giovanni как стро-
    ку-кандидата, и затем сохраняет повторно пока каждая строка таблицы
    Заказчиков не будет проверена.
 
 
   Как вы  можете  видеть,  вычисления  которые SQL выполняет с помощью
 этих простых инструкций - это полный комплекс.  Конечно,  вы могли  бы
 решить  ту же самую проблему используя обьединение,  следующего вида (
 вывод для этого запроса показывается в Рисунке 11.2 ):
 
 
               SELECT *
                  FROM Customers first, Orders second
                  WHERE first.cnum = second.cnum
                     AND second.odate = 10/03/1990;
 
 
   Обратите внимание что Cisneros был выбран дважды, по одному разу для
 каждого порядка который он имел для данной даты. Мы могли бы устранить
 это  используя  SELECT DISTINCT вместо просто SELECT.  Но это необяза-
 тельно в варианте подзапроса.  Оператор IN,  используемый  в  варианте
 подзапроса, не делает никакого различия между значениями которые выби-
 раются подзапросом один раз и значениями которые  выбираются  неоднок-
 ратно. Следовательно DISTINCT необязателен.
 
 
                ===============  SQL Execution Log ============
               |                                               |
               | SELECT *                                      |
               | FROM  Customers first, Orders second          |
               | WHERE first.cnum = second.cnum                |
               | (SELECT COUNT (*)                             |
               | FROM Customers                                |
               | WHERE snum = main.snum;                       |
               | ============================================= |
               |   cnum     cname                              |
               |  -----    --------                            |
               |   1001     Peel                               |
               |   1002     Serres                             |
                 =============================================
 
 
 Рисунок 11. 2   Использование обьединения вместо соотнесенного
                 подзапроса
 
 
   Предположим что  мы хотим видеть имена и номера всех продавцов кото-
 рые имеют более одного заказчика.  Следующий запрос выполнит  это  для
 вас ( вывод показывается в Рисунке 11.3 ):
 
 
              SELECT snum, sname
                 FROM Salespeople main
                 WHERE 1 <
                     ( SELECT COUNT (*)
                          FROM Customers
                          WHERE snum = main.snum );
 
 
   Обратите внимание что предложение FROM подзапроса в этом примере  не
 использует псевдоним.  При отсутствии имени таблицы или префикса псев-
 донима, SQL может для начала принять, что любое поле выводится из таб-
 лицы с именем указанным в предложении FROM текущего запроса. Если поле
 с этим именем отсутствует( в нашем случае - snum ) в той таблице,  SQL
 будет проверять внешние запросы. Именно поэтому, префикс имени таблицы
 обычно необходим в соотнесенных подзапросах - для отмены этого предпо-
 ложения. Псевдонимы также часто запрашиваются чтобы давать вам возмож-
 ность ссылаться к той же самой таблице во внутреннем и внешнем запросе
 без какой-либо неоднозначности.
 
 
                ===============  SQL Execution Log ============
               |                                               |
               | SELECT snum sname                             |
               | FROM  Salespeople main                        |
               | WHERE 1 <                                     |
               | AND second.odate = 10/03/1990;                |
               | ============================================= |
               |   cnum     cname     city    rating    snum   |
               |  -----    --------   ----    ------   -----   |
               |   2001    Hoffman    London     100    1001   |
               |   2003    Liu        San Jose   200    1002   |
               |   2008    Cisneros   San Jose   300    1007   |
               |   2007    Pereira    Rome       100    1004   |
                 =============================================
 
 
 Рисунок 11.3: Нахождение продавцов с многочислеными заказчиками
 
 
                 ИСПОЛЬЗОВАНИЕ СООТНЕСЕННЫХ ПОДЗАПРОСОВ
                         ДЛЯ НАХОЖДЕНИЯ ОШИБОК
 
 
   Иногда полезно  выполнять запросы которые разработаны специально так
 чтобы находить ошибки.  Это всегда возможно при  дефектной  информации
 которую можно ввести в вашу базу данных,  и,  если она введена, бывает
 трудно ее определить.  Следующий запрос не должен производить никакого
 вывода.  Он  просматривает  таблицу Порядков чтобы видеть совпадают ли
 поля snum и cnum в каждой строке таблицы Заказчиков и  выводит  каждую
 строку где этого совпадения нет. Другими словами, запрос выясняет, тот
 ли продавец кредитовал каждую продажу ( он воспринимает поле cnum, как
 первичный  ключ  таблицы  Заказчиков,  который  не будет иметь никаких
 двойных значений в этой таблице ).
 
 
         SELECT *
            FROM Orders main
            WHERE NOT snum =
               ( SELECT snum
                   FROM Customers
                   WHERE cnum = main.cnum );
 
 
   При использовании  механизма справочной целостности ( обсужденного в
 Главе 19 ),  вы можете быть гарантированы от некоторых  ошибок  такого
 вида.  Этот механизм не всегда доступен,  хотя его использование жела-
 тельно во всех случаях,  причем поиск ошибки запроса  описанный  выше,
 может быть еще полезнее.
 
 
                       СРАВНЕНИЕ ТАБЛИЦЫ С СОБОЙ
 
 
   Вы можете  также  использовать  соотнесенный подзапрос основанный на
 той же самой таблице что и основной запрос.  Это даст вам  возможность
 извлечть  определенные сложные формы произведенной информации.  Напри-
 мер,  мы можем найти все порядки со значениями сумм приобретений  выше
 среднего для их заказчиков ( вывод показан в Рисунке 11.4 ):
 
 
            SELECT *
               FROM Orders outer
               WHERE amt >
                   ( SELECT AVG amt
                        FROM Orders inter
                        WHERE inner.cnum = outer.cnum );
 
 
                ===============  SQL Execution Log ==============
               |                                                 |
               | SELECT *                                        |
               | FROM  Orders outer                              |
               | WHERE amt >                                     |
               | (SELECT AVG (amt)                               |
               | FROM Orders inner                               |
               | WHERE inner.cnum = outer.cnum                   |
               | =============================================== |
               |   onum       amt      odate      cnum     snum  |
               |  -----    --------  ----------  -----   ------  |
               |   3006     1098.19  10/03/1990   2008     1007  |
               |   3010     1309.00  10/06/1990   2004     1002  |
               |   3011     9891.88  10/06/1990   2006     1001  |
                 ================================================
 
 
 Рисунок 11.4: Соотнесение таблицы с собой
 
 
   Конечно, в нашей маленькой типовой таблице,  где большиство заказчи-
 ков имеют только один порядок,  большинство значений являются одновре-
 менно средними и следовательно не выбираются.  Давайте введем  команду
 другим способом ( вывод показывается в Рисунке 11.5 ):
 
 
               SELECT *
                   FROM Orders outer
                   WHERE amt > =
                       ( SELECT AVG (amt)
                           FROM Orders inner
                           WHERE inner.cnum = outer.cnum );
 
 
                ===============  SQL Execution Log ==============
               |                                                 |
               | SELECT *                                        |
               | FROM  Orders outer                              |
               | WHERE amt > =                                   |
               | (SELECT AVG (amt)                               |
               | FROM Orders inner                               |
               | WHERE inner.cnum = outer.cnum);                 |
               | =============================================== |
               |   onum       amt      odate      cnum     snum  |
               |  -----    --------  ----------  -----   ------  |
               |   3003      767.19  10/03/1990   2001     1001  |
               |   3002     1900.10  10/03/1990   2007     1004  |
               |   3005     5160.45  10/03/1990   2003     1002  |
               |   3006     1098.19  10/03/1990   2008     1007  |
               |   3009     1713.23  10/04/1990   2002     1003  |
               |   3010     1309.95  10/06/1990   2004     1002  |
               |   3011     9891.88  10/06/1990   2006     1001  |
                 ================================================
 
 
 Рисунок 11.5:  Выбераются порядки которые > = средней сумме приобрете-
                ний для их заказчиков.
 
 
   Различие, конечно,  в том, что реляционный оператор основного преди-
 ката включает значения которые равняются среднему ( что обычно означа-
 ет что они - единственые порядки для данных заказчиков ).
 
 
              СООТНЕСЕННЫЕ ПОДЗАПРОСЫ В ПРЕДЛОЖЕНИИ HAVING
 
 
   Также как предложение HAVING может брать подзапросы,  он может брать
 и соотнесенные подзапросы. Когда вы используете соотнесенный подзапрос
 в предложении HAVING, вы должны ограничивать внешние ссылки к позициям
 которые могли бы непосредственно использоваться  в  самом  предложении
 HAVING.  Вы  можете  вспомнить из Главы 6 что предложение HAVING может
 использовать только агрегатные функции которые указаны в их  предложе-
 нии SELECT или поля используемые в их предложении GROUP BY.  Они явля-
 ются только внешними ссылками, которые вы можете делать. Все это пото-
 му,  что  предикат предложения HAVING оценивается для каждой группы из
 внешнего запроса, а не для каждой строки. Следовательно, подзапрос бу-
 дет  выполняться один раз для каждой группы выведеной из внешнего зап-
 роса, а не для каждой строки.
   Предположим что вы хотите суммировать значения сумм приобретений по-
 купок из таблицы Порядков,  сгруппировав их по датам,  удалив все даты
 где бы SUM не был по крайней мере на 2000.00 выше максимальной ( MAX )
 суммы:
 
 
            SELECT odate, SUM (amt)
               FROM Orders a
               GROUP BY odate
               HAVING SUM (amt) >
                   ( SELECT 2000.00 + MAX (amt)
                        FROM Orders b
                        WHERE a.odate = b.odate );
 
 
   Подзапрос вычисляет значение MAX для всех строк с той же самой датой
 что  и у текущей агрегатной группы основного запроса.  Это должно быть
 выполнено,  как и ранее, с испошльзованием предложения WHERE. Сам под-
 запрос не должен использовать предложения GROUP BY или HAVING.
 
 
                 СООТНЕСЕННЫЕ ПОДЗАПРОСЫ И ОБЬЕДИНЕНИЯ
 
 
   Как вы  и  могли  предположить,  соотнесенные  подзапросы по природе
 близки к обьединениям - они оба включают проверку каждой строки  одной
 таблицы с каждой строкой другой ( или псевдонимом из той же ) таблицы.
 Вы найдете что большинство операций которые могут выполняться с  одним
 из них будут также работать и с другим.
   Однако имеется различие в прикладной программе между ними, такое как
 вышеупомянутая  потребность  в использовании DISTINCT с обьединением и
 его необязательность с подзапросом. Имеются также некоторые вещи кото-
 рые  каждый может делать так,  как этого не может другой.  Подзапросы,
 например,  могут использовать агрегатную функцию  в  предикате,  делая
 возможным  выполнение операций типа нашего предыдущего примера в кото-
 ром мы извлекли порядки усредненные для их заказчиков.  Обьединения, с
 другой стороны,  могут выводить строки из обеих сравниваемых таблиц, в
 то время как вывод подзапросов используется только в предикатах  внеш-
 них запросов.  Как правило, форма запроса которая кажется наиболее ин-
 туитивной будет вероятно лучшей в использовании, но при этом хорошо бы
 знать  обе  техники  для тех ситуаций когда та или иная могут не рабо-
 тать.
 
 
               ================  РЕЗЮМЕ  ================
 
 
   Вы можете поздравлять себя с овладением большого куска из рассмотре-
 ных понятий в SQL - соотнесенного подзапроса. Вы видели как соотнесен-
 ный подзапрос связан с обьединение, а также, как как его можно исполь-
 зовать с агрегатными функциями и в предложении HAVING. В общем, вы те-
 перь узнали все типы подзапросов полностью.
   Следующий шаг  - описание некоторых SQL специальных операторов.  Они
 берут подзапросы как аргументы, как это делает IN, но в отличие от IN,
 они могут использоваться только в подзапросах. Первый из их, представ-
 ленный в Главе 12, - называется EXISTS.
 
 
               *************** РАБОТА С SQL *************
 
 
 1. Напишите команду SELECT использующую соотнесенный подзапрос,  кото-
    рая  выберет  имена и номера всех заказчиков с максимальными для их
    городов оценками.
 2. Напишите два запроса которые выберут всех продавцов ( по их имени и
    номеру ) которые в своих городах имеют заказчиков  которых  они  не
    обслуживают.  Один запрос - с использованием обьединения и один - с
    соотнесенным подзапросом. Которое из решений будет более изящным?
    ( Подсказка: один из способом это сделать, состоит в том, чтобы на-
    ходить всех заказчиков не обслуживаемых данным продавцом и  опреде-
    лить, находится ли каждый из них в городе продавца. )
 
 
 ( См. Приложение A для ответов. )
 
 
 
Перейти к оглавлению
Hosted by uCoz