Понимание SQL (775442), страница 12
Текст из файла (страница 12)
В вышеупомянутом примере, 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 для ответов. )
12. ИСПОЛЬЗОВАНИЕ ОПЕРАТОРА EXISTS
Теперь, когда вы хорошо ознакомлены с подзапросами, мы можем говорить о некоторых специальных операторах которые всегда берут подзапросы как аргументы. Вы узнаете о первом из их в этой главе.
Остальные будут описан в следующей главе.
Оператор EXISTS используется чтобы указать предикату, - производить ли подзапросу вывод или нет. В этой главе, вы узнаете как использовать этот оператор со стандартными и (обычно) соотнесенными подзапросами. Мы будем также обсуждать специальные размышления которые перейдут в игру когда вы будете использовать этот оператор как относительный агрегат, как пустой указатель NULL, и как оператор Буля. Кроме того, вы можете повысить ваш профессиональный уровень относительно подзапросов, исследуя их в более сложных прикладных программах, чем те которые мы видели до сих пор.
КАК РАБОТАЕТ EXISTS?
EXISTS - это оператор, который производит верное или неверное значение, другими словами, выражение Буля (см. Главу 4 ИСПОЛЬЗОВАНИЕ РЕЛЯЦИОННЫХ И БУЛЕВЫХ ОПЕРАТОРОВ ДЛЯ СОЗДАНИЯ БОЛЕЕ ИЗОЩРЕННЫХ ПРЕДИКАТОВ для обзора этого термина). Это означает, что он может работать автономно в предикате или в комбинации с другими выражениями Буля использующими Булевы операторы AND, OR, и NOT. Он берет подзапрос как аргумент и оценивает его как верный, если тот производит любой вывод или как неверный, если тот не делает этого.
Этим он отличается от других операторов предиката, в которых он не может быть неизвестным. Например, мы можем решить, извлекать ли нам некоторые данные из таблицы Заказчиков если, и только если, один или более заказчиков в этой таблице находятся в San Jose (вывод для этого запроса показывается в Рисунке 12.1):
SELECT cnum, cname, city
FROM Customers
WHERE EXISTS
(SELECT *
FROM Customers
WHERE city = " San Jose' );
Внутренний запрос выбирает все данные для всех заказчиков в San Jose. Оператор EXISTS во внешнем предикате отмечает, что некоторый вывод был произведен подзапросом, и поскольку выражение EXISTS было полным предикатом, делает предикат верным. Подзапрос(не соотнесенный) был выполнен только один раз для всего внешнего запроса, и следова-
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
