Как работает соотнесенный подзапрос

В вышеупомянутом примере, "внутренний" (inner) и "внешний" (outer), это псевдонимы, подобно обсужденным в Главе 9. Мы выбрали эти имена для большей ясности; они отсылают к значениям внутренних и внешних запросов, соответственно. Так как значение в поле cnum внешнего запроса меняется, внутренний запрос должен выполняться отдельно для каждой строки внешнего запроса. Строка внешнего запроса, для которого внутренний запрос каждый раз будет выполнен, называется текущей строкой-кандидатом. Следовательно, процедура оценки выполняемой соотнесенным подзапросом — это:

· выбрать строку из таблицы именованной во внешнем запросе. Это будет текущая строка-кандидат;

· сохранить значения из этой строки-кандидата в псевдониме с именем в предложении 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 AND second.odate = 10/03/1990; |
|======================================================================|
|cnum cname city rating snum onum amt odate cnum1 snum1|
|---- -------- -------- ------ ---- ---- ------- ---------- ----- -----| |2001 Hoffman London 100 1001 3003 767,19 10/03/1990 2001 1001|
|1002 Liu San Jose 200 1002 3005 5160,45 10/03/1990 2003 1002|
|2007 Pereira Rome 100 1004 3002 1900,10 10/03/1990 2007 1004|
|2008 Cisneros San Jose 300 1007 3001 18,69 10/03/1990 2008 1007|
|2008 Cisneros San Jose 300 1007 3006 1098,16 10/03/1990 2008 1007|
======================================================================

Рисунок 11.2. Использование объединения вместо соотнесенного подзапроса.

Предположим, что мы хотим видеть имена и номера всех продавцов, которые имеют более одного заказчика. Следующий запрос выполнит это для вас (вывод показывается в Рисунке 11.3):

SELECT snum, sname
FROM Salespeople main
WHERE 1 < (SELECT COUNT (*)
FROM Customers
WHERE snum = main.snum);

Рисунок 11.3. Нахождение продавцов с более, чем одним заказчиком.

Обратите внимание, что предложение FROM подзапроса в этом примере не использует псевдоним. При отсутствии имени таблицы или префикса псевдонима, SQL может для начала принять, что любое поле выводится из таблицы с именем, указанным в предложении FROM текущего запроса. Если поле с этим именем отсутствует (в нашем случае — snum) в той таблице, SQL будет проверять внешние запросы. Именно поэтому, префикс имени таблицы обычно необходим в соотнесенных подзапросах для отмены этого предположения. Псевдонимы также часто запрашиваются, чтобы давать вам возможность ссылаться к той же самой таблице во внутреннем и внешнем запросе без какой-либо неоднозначности.

Использование соотнесенных подзапросов
для нахождения ошибок

Иногда полезно выполнять запросы, которые разработаны специально так, чтобы находить ошибки. Это всегда возможно при дефектной информации, которую можно ввести в вашу базу данных, и, если она введена, бывает трудно ее определить. Следующий запрос не должен производить никакого вывода. Он просматривает таблицу Заказов, чтобы видеть, совпадают ли поля 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 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 |
| ----- -------- ---------- ----- ------ |
| 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. Выбираются Заказы, которые >= средней сумме приобретений для их заказчиков.

Различие, конечно, в том, что реляционный оператор основного предиката включает значения, которые равняются среднему (что обычно означает, что они — единственные Заказы для данных заказчиков).

Примечание. В примерах запросов, приведенных в этом разделе, используются псевдонимы "inner" и "outer", которые в СУБД Interbase (возможно, и в других СУБД) являются ключевыми словами, и, следовательно, должны быть изменены.