СООТНЕСЕННЫЕ ПОДЗАПРОСЫ

В ЭТОЙ ГЛАВЕ, МЫ ПРЕДСТАВИМ ВАС ТИПУ подзапроса о котором мы не говорили в Главе 10 - посвященной соотнесенному подзапросу. Вы узнаете как использовать соотнесенные подзапросы в предложениях запросов WHERE и HAVING. Сходства и различи между соотнесенными подзапросами и объединениями будут обсуждаться далее, и вы сможете повысить ваше знание псевдонимов и префиксов имени таблицы - когда они необходимы и как их использовать.

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: Нахождение продавцов с многочисленными заказчиками

SELECT * 
 FROM Orders main 
 WHERE NOT snum = 
( SELECT snum 
    FROM Customers 
    WHERE cnum = main.cnum );

При использовании механизма справочной целостности ( обсужденного в Главе 19 ), вы можете быть гарантированы от некоторых ошибок такого вида. Этот механизм не всегда доступен, хотя его использование желательно во всех случаях, причем поиск ошибки запроса описанный выше, может быть еще полезнее.

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  | 
  ================================================

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  | 
  ================================================

Также как предложение 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.

Следующий шаг - описание некоторых SQL специальных операторов. Они берут подзапросы как аргументы, как это делает IN, но в отличие от IN, они могут использоваться только в подзапросах. Первый из их, представленный в Главе 12, - называется EXISTS.

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