Понимание SQL - Мартин Грубер

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