Материал: Graber_Martin_-_SQL_dlya_prostykh_smertnykh_-_2014

Как формировать связанные подзапросы

1с-~-• ~" --~~1

Рис. 11.1. Использование связанного подзапроса

2.Сохранить значения этой строки в алиасе, имя которого указано в предложении

FROM внешнего запроса.

3.Выполнить подзапрос. Всякий раз, когда алиас, заданный для внешнего запроса, найден (в нашем случае "outer"), его значение применяется к текущей строке·

кандидату. Использование в подзапросе значения из строки-кандидата внешнего

запроса называется внешней ссылкой (outer refe1·e11ce).

4.Оценить предикат внешнего запроса на основе результатов подзапроса, выпол­

ненного на шаге 3. Это позволяет определить, будетли строка-кандидат включе­

на в состав выходных данных.

5.Повторять процедуру для следующей строки-кандидата таблицы до тех пор, пока не будут проверены все строки таблицы.

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

1.Извлекается строка Hoffman из таблицы Customers.

2.Эта строка сохраняется как текущая строка-кандидат при алиасе "outer".

3.Затем выполняется подзапрос: просматривается вся таблица Orders с целью най­

ти строки, в которых значение поля cnum совпадает со значением outer.cnum; в

данном случае это значение 2001 (значение поля cnum строки Hoffman). Затем извлекается поле odate из каждой строки таблицы Orders, для которой предикат принимает значение true (истина), и строится множество результирующих значе­

ний odate.

4.После получения множества всех значений odate, для которых cnum равно 2001,

выполняется тестирование предиката основного запроса, с целью выявить, есть

109

Глава 11. Связанные подзапросы

ли в этом множестве 3 октября 1990 года. В случае, если такая дата обнаружена (а это так и есть), строка Hoffman выбирается для включения в состав выходных

данных основного запроса.

5.Процедура повторяется со строкой Giovanni, которая выбирается в качестве строки-кандидата, а затем с каждой строкой до тех пор, пока не будет проверена вся таблица Customers.

Согласно этим простым инструкциям SQL выполняет весьма сложные действия. Эту задачу можно было бы решить и с помощью операции соединения (join), напри­ мер, следующим образом (выходные данные для этого запроса представлены на

рис. 11.2):

SELECT •

FROM Customers first, Orders second WHERE first.cnum = second.cnum

AND second.odate = 10/03/1990;

Здесь Cisneros была выбрана дважды, по одному разу на каждый заказ, который она сделала в указанный день. Этого можно было бы избежать, используя SELECT DISТINCT вместо SELECT. Однако, такое действие не является необходимым при ис­ пользовании версии с подзапросами. Оператор IN, используемый в версии с подзапро­ сами, не делает разницы между значениями, выбираемыми в подзапросе однажды и повторно. Следовательно, в таком варианте запроса DISТINCT не является необходи­

мым.

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

[[_~-~~~~-~~~~--]

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

110

Как формировать связа11ные подзапросы

Рис. 11.3. Поиск продавца для множества покупателей

SELECT snum, sname

FROM Salespeople main

WHERE 1 <

(SELECT COUNT (*)

FROM Customers

WHERE snum = main. snum);

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

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

Иногда полезно выполнить запросы, предназначенные специально для поиска оши­

бок. В базе данных всегда может появиться ошибочная информация, и ее бывает трудно

111

Глава 11. Связанные подзапросы

обнаружить. Следующий запрос не генерирует выходных данных. Он проверяет таблицу Orders с целью установить, соответствует ли соотношение полей snum и cnum каждой ее строки соотношению этих же полей в таблице Customers, и осуществляет вывод любой строки, для которой такое соответствие не обнаружено. Другими словами, он позволяет контролировать корректность связей продавцов с обслуживаемыми ими покупателями (предполагается, что cnum, как первичный ключ таблицы Customers, не имеет повторяю­ щихся значений в этой таблице).

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);

В маленькой таблице, рассматриваемой в качестве примера, где большинство поку­ пателей имеют только один заказ, большинство значений совпадает со средним и, сле­ довательно, не извлекается. Можно ввести команду по-другому (выходные данные представлены на рис. 11.5):

SELECT *

FROM Orders outer WHERE amt >=

(SELECT AVG (amt)

FROM Orders inner

112

Как форАtuровать связанные lloдзallpocы

WHERE inner.cnum = outer.cnum);

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

Рис. 11.4. Связывание таблицы с ее копией

м.1.ща;

3003 767.19 10/03/1990

3002 1900. 10 10/03/1990

3005 5160.45 10/03/1990

3006 1098. 16 10/03/1990

3009 1713.23 10/04/1990

3010 1309.95 10/06/1990

3011 9891.88 10/06/1990

Рис. 11.5. Выбор заказов, величина которых превышает среднее значение для их покупателей или

совпадает с ним

113