Глава 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 Использование соотнесённого подзапроса

Следовательно, процедура оценки, выполняемой соотнесённым подзапросом:

Выбрать строку из таблицы, именованной во внешнем запросе. Это будет текущая строка-кандидат.
Сохранить значения из этой строки-кандидата в псевдониме с именем в предложении FROM внешнего запроса.
Выполнить подзапрос. Везде, где псевдоним, заданный для внешнего запроса, найден (в данном случае "внешний"), использовать значение текущей строки-кандидата. Использование значения из строки-кандидата внешнего запроса в подзапросе называется внешней ссылкой.
Оценить предикат внешнего запроса на основе результатов подзапроса, выполняемого в шаге 3. Он определяет, выбирается ли строка-кандидат для вывода.
Повторить процедуру для следующей строки-кандидата таблицы, и так далее, пока все строки таблицы не будут проверены.

В вышеупомянутом примере SQL осуществляет следующую процедуру:

Выбирает строку Hoffman из таблицы Заказчиков.
Сохраняет эту строку как текущую строку-кандидат под псевдонимом "внешний".
Затем он выполняет подзапрос. Подзапрос просматривает всю таблицу Заказов, чтобы найти строки, где значение поля cnum - такое же, как значение outer.cnum, которое в настоящее время равно 2001 - поле cnum строки Hoffman.
Затем он извлекает поле odate из каждой строки таблицы Заказов, для которой это верно, и формирует набор значений поля odate.
Получив набор всех значений поля odate для поля cnum = 2001, он проверяет предикат основного запроса, чтобы увидеть, имеется ли значение на 3 октября в этом наборе.
Если это так (а это так), то он выбирает строку Hoffman для вывода её из основного запроса.
Он повторяет всю процедуру, используя строку 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.)