Название: Базы данных. Концепция баз данных, реляционная модель данных, языки SQL и XML (Токмаков Г. П.)

Жанр: Информационные системы и технологии

Просмотров: 1402


4.4. вложенные запросы

 

В SQL существует механизм вложенного запроса, позволяющий использо- вать результаты одного запроса в другом запросе. Этот механизм играет важ- ную роль в SQL по следующим причинам:

– вложенные запросы соответствуют словесному описанию запроса и поэто-

му являются самым естественным способом выражения запроса;

– вложенные запросы позволяют структурировать запрос путем разбиения на части (на главный запрос и вложенные запросы);

– существуют ситуации, когда невозможно обойтись без вложенных запро-

сов.

 

 

Вложенным называется запрос, содержащийся в предложении WHERE или

HAVING другого запроса.

Рассмотрим следующую ситуацию. Требуется вывести список офисов, для которых плановый объем продаж (поле TARGET) превышает сумму плановых

объемов продаж всех служащих (поле QUOTA).

Первая часть этого запроса должна выглядеть как

 

SELECT CITY FROM OFFISY

WHERE TARGET > ???

 

Во второй части заказа нужно ответить на вопрос: как определить сумму плановых объемов продаж служащих для отдельного офиса (скажем офиса с идентификатором 22).

Это можно сделать с помощью запроса, использующего агрегатную функ-

цию SUM:

 

SELECT SUM(QUOTA) FROM SLUZHASCHIE WHERE ID_OFC = 22

 

А теперь объединим эти запросы путем замены знаков ??? на второй запрос и получим следующий структурированный запрос

 

SELECT CITY FROM OFFISY

WHERE TARGET > (SELECT SUM(QUOTA) FROM SLUZHASCHIE

WHERE SLUZHASCHIE.ID_OFC = OFFISY.ID_OFC)

 

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

 

4.4.1. О ПРЕДЕЛЕНИЕ  ПО ДЧИНЕН Н ЫХ  ЗАП Р ОСОВ

 

Вложенные запросы всегда входят в предложение WHERE или HAVING и за- ключаются в круглые скобки. В предложении WHERE они отбирают из таблицы отдельные строки, а в предложении HAVING  группы строк. Подчиненные за- просы имеют ту же структуру, что и инструкция SELECT, содержащая предло- жение FROM и необязательные предложения WHERE, GROUP BY и HAVING. Однако между вложенным запросом и инструкцией SELECT имеются отличия:

– таблица результатов вложенного запроса всегда состоит из одного столбца, поэтому в предложении SELECT вложенного запроса всегда следует указывать только один возвращаемый столбец;

– во вложенный запрос не может входить предложение ORDER BY, так как ре- зультаты вложенного запроса используются только внутри главного запроса и для пользователя остаются невидимыми. Поэтому нет смысла их сортировать.

Чаще всего вложенные запросы используются в предложении WHERE и уча-

ствуют в процессе отбора строк. В простейшем случае вложенный запрос воз- вращает значение, позволяющее проверить истинность или ложность условия отбора.

Рассмотрим следующий пример: вывести список служащих, чей плановый

объем продаж составляет менее 10\% от планового объема продаж всей ком-

пании.

 

SELECT NAME

FROM SLUZHASCHIE

WHERE QUOTA < (0.1 * (SELECT SUM(TARGET) FROM OFFISY))

В приведенном запросе вложенный запрос вычисляет одну и ту же сумму плановых объемов продаж всех офисов, которая затем умножается на 0,1 (10\%). Полученное значение используется в условии отбора при сканировании табли- цы SLUZHASCHIE на предмет поиска нужных строк.

Далее рассмотрим более сложный пример, приведенный в предыдущем разделе

 

SELECT CITY FROM OFFISY

WHERE TARGET > (SELECT SUM(QUOTA) FROM SLUZHASCHIE

WHERE SLUZHASCHIE.ID_OFC = OFFISY.ID_OFC)

 

На Рис. 4.1.    приведена схема выполнения этого запроса, в котором вло-

женный запрос возвращает различные результаты для каждого офиса.

1. Главный запрос извлекает данные из таблицы OFFISY.

2. Условие предложения WHERE обеспечивает отбор офисов, включаемых в таблицу результатов запроса. Это условие поочередно применяется ко всем

строкам таблицы OFFISY.

3. В предложении WHERE сравнивается значение текущей строки в столбце

TARGET со значением, возвращаемым вложенным запросом.

4. Для каждой строки результирующей таблицы выполняется свой вложен-

ный запрос, вычисляющий сумму плановых объемов продаж для служащих те-

кущего офиса.

 

Таблица SLUZHASCHIE

 

 

ID_SLZH

ID_OFFICE

 

 

 

 

 

 

 

 

 

 

 
SELECT SUM(QUOTA) FROM СЛУЖАЩИЕ С WHERE С.ID_OFFICE=11

 

>?

 

 

 

ID_OFFICE

CITY

TARGET

22

2111

$575 000.00

11

2102

$675 000.00

12

2103

$800 000.00

 

 

 

 
Таблица OFFISY

 

>?        Таблица SLUZHASCHIE

 

 

ID_SLZH

ID_OFFICE

 

 

 

 

 

 

 

 

 

 

 
SELECT SUM(QUOTA) FROM СЛУЖАЩИЕ С WHERE С.ID_OFFICE=12

 

Рис. 4.1.          Выполнение вложенного запроса в предложении WHERE

5. Результатом выполнения вложенного запроса является одно число, и предложение WHERE сравнивает его значение со значением столбца TARGET, вы- бирая или отбрасывая текущий офис на основании результата сравнения.

 

4.4.2. У СЛОВ ИЯ ОТБОР А В ПОДЧ ИНЕН Н О М ЗАПРО С Е

 

Вложенный запрос всегда является частью условия отбора в предложении WHERE и HAVING. В SQL используются следующие условия отбора вложенного во вложенном запросе:

– сравнение с результатом подчиненного запроса: значение выражения срав-

нивается с одним значением, вычисленным вложенным запросом. Эта проверка представляет собой простое сравнение;

– проверка на принадлежность результатам вложенного запроса: значение выражения проверяется на равенство одному из множества значений, возвращае-

мых вложенным запросом. Эта проверка представляет собой проверку на членст-

во в множестве;

– проверка на существование: проверяется наличие строк в таблице результа-

тов вложенного запроса;

– многократное сравнение: значение выражения сравнивается с каждым из множества значений, возвращаемых вложенным запросом.

 

Ср авнение  с  результатом  по лученно го  за проса

 

В данном условии отбора значение выражения сравнивается со значением, вычисленным вложенным запросом. В случае совпадения значений проверка дает результат TRUE, а в случае несовпадения  FALSE. Этот вид условия отбора используется для сравнения значения из проверяемой строки с одним значени- ем, полученным от вложенного запроса.

Приведем пример использования описываемого вида условия отбора: вы- вести список служащих, у которых плановый объем продаж равен или больше планового объем продаж офиса, расположенного в Инзе.

 

SELECT FAMILY, NAME FROM SLUZHASCHIE

WHERE QUOTA >= (SELECT TARGET FROM OFFISY

WHERE CITY = ‘Инза’)

 

FAMILY

NAME

Филатов

Петр

Полев

Андрей

Пронин

Игорь

 

В описываемом примере вложенный запрос считывает плановый объем продаж для офиса в Инзе. Затем это значение используется для отбора тех слу- жащих, у которых плановый объем продаж выше, чем у этого офиса.

При этом следует иметь в виду, что вложенный запрос должен возвращать единичное значение, т. е. одну строку, содержащую один столбец. Если в ре-

зультате  выполнения  вложенного  запроса  выводится  несколько  строк  или столбцов, то сравнение теряет смысл и СУБД выводит сообщение об ошибке.

В случае возвращения вложенным запросом значения NULL или если не бу-

дет выведено ни одной строки, то операция сравнения вернет значение NULL.

 

Проверка на  принадлежно сть  рез у ль та там  вло ж ен ного запроса

 

Проверка на принадлежность результатам вложенного запроса, включенно- го в состав предиката IN, является аналогом проверки на членство в множестве. Текущее значение проверяемого выражения главного запроса сравнивается со столбцом значений, возвращаемых вложенным запросом. Если это значение равно одному из элементов столбца, то проверка дает результат TRUE и текущая строка включается в результирующую таблицу.

Приведем пример запроса, использующего проверку на принадлежность результатам вложенного запроса: вывести список служащих тех офисов, где фактический объем продаж превышает плановый.

 

SELECT FAMILY, NAME FROM SLUZHASCHIE

WHERE SLUZHASCHIE.ID_OFC IN (SELECT OFFISY.ID_OFC FROM OFFISY

WHERE SALES > TARGET)

 

FAMILY

NAME

Филатов

Петр

Петров

Петр

 

Вложенный запрос возвращает список идентификаторов офисов, где фак- тический объем продаж превышает плановый (в учебной базе данных есть два таких офиса  с идентификаторами 12 и 22). Главный запрос проверяет каждую строку таблицы SLUZHASCHIE, чтобы определить, работает ли данный служащий в одном из отобранных офисов.

В приведенном примере вложенный запрос возвращает в качестве резуль- тата множество значений, а предложение WHERE главного запроса проверяет ра- венство значения из строки таблицы главного запроса одному из значений по- лученного множества. Таким образом, проверка IN с вложенным запросом вы- полняется аналогично проверке IN в инструкции SELECT.

 

Проверка  на  существование

 

С помощью проверки на существование (предикат EXISTS) можно выяс- нить, содержится ли в таблице вложенного запроса хотя бы одна строка, удов- летворяющая условию отбора. Аналогичной проверки в простой инструкции SELECT не существует. Эта проверка возможна только во вложенном запросе.

Допустим, что требуется вывести список товаров, на которые получен за-

каз стоимостью $20 000.00 или больше. Чтобы решить эту задачу путем вы-

полнения вложенного запроса с проверкой на существование, перефразируем эту задачу следующим образом: вывести список товаров, для которых в табли-

це ZAKAZY существует, по крайней мере, один заказ, удовлетворяющий услови-

ям: является заказом на данный товар и имеет стоимость не менее $20 000.00.

Инструкция SELECT, используемая для решения поставленной задачи имеет

вид

 

 

SELECT DESCRIPTION FROM TOVARY

WHERE EXISTS (SELECT ID_ORDER FROM ZAKAZY

WHERE ZAKAZY.ID_PRD = TOVARY.ID_PRD AND ZAKAZY.ID_MFR = TOVARY.ID_MFR AND ZAKAZY.PRICE >= 20000.00)

 

DESCRIPTION

 

Деталь кузова

 

В данном случае главный запрос последовательно просматривает все стро- ки таблицы TOVARY и для каждой строки выполняется вложенный запрос. Ре- зультатом вложенного запроса является набор  данных, содержащий номера всех заказов текущего товара на сумму не меньше чем $20 000.00.

Если такие заказы есть (т. е. набор данных не пустой), то проверка EXISTS

возвращает TRUE. Если вложенный запрос не возвращает ни одной строки, то проверка EXISTS возвращает значение FALSE. Эта проверка никогда не возвра-

щает NULL.

 

Многократное ср авн е н и е

 

При проверке на принадлежность результатам вложенного запроса с помо- щью предиката IN осуществляется многократное сравнение значения таблицы главного запроса с набором значений из таблицы вложенного запроса.

В SQL имеются еще две разновидности множественной проверки, осущест- вляемые с помощью предикатов ANY и ALL. С помощью этих предикатов про- верка осуществляется не только на совпадение, но и на «больше» или «меньше».

Предикат ANY сравнивает проверяемое значение с набором данных, выби-

раемых вложенным запросом, используя операторы «=», «», «», «=», «»,

«=». Проверяемое значение поочередно сравнивается с каждым значением из набора данных. Если любое из этих сравнений дает значение TRUE, то и провер-

ка ANY возвращает значение TRUE.

Приведем пример использования предиката ANY: вывести список служа-

щих, принявших заказ на сумму, большую, чем на 10\% от плана.

 

SELECT FAMILY, NAME FROM SLUZHASCHIE S

WHERE (0.1*QUOTA < ANY (SELECT PRICE_ALL FROM ZAKAZY Z

WHERE S.ID_SLZH = Z.ID_SLZH))

 

FAMILY

NAME

Ганин

Сергей

Петров

Петр

Нилов

Лев

 

Главный запрос последовательно проверяет все строки таблицы SLUZHA‐ SCHIE. Вложенный запрос находит все запросы, принятые текущим служащим, и возвращает набор данных, содержащий стоимости этих заказов. Предложение WHERE главного запроса вычисляет 10\% от плана текущего служащего и исполь- зует это число в качестве проверяемого значения, сравнивая его со стоимостью каждого заказа, выбранного вложенным запросом.

Если есть хотя бы один заказ, стоимость которого превышает вычисленное значение, то проверка   ANY возвращает значение TRUE, а имя служащего зано-

сится в результирующую таблицу. Если таких заказов нет, имя служащего в ре-

зультирующую таблицу не заносится.

Предикат ALL, как и предикат ANY, использует один из шести операторов («=», «», «», «=», «», «=») для сравнения проверяемого значения с набором данных, выбранных вложенным запросом. В данном случае проверяемое значе- ние последовательно сравнивается с каждым значением из набора данных. Если все сравнения дают положительный результат, то предикат ALL возвращает зна- чение TRUE.

Пример. Вывести список офисов с их плановыми объемами продаж, все служащие которых превысили плановый объем продаж на 50\% от плана офиса.

 

SELECT CITY, TARGET FROM OFFICY O

WHERE (0.50*TARGET < ALL (SELECT SALES

FROM SLUZHASCHIE S

WHERE O.ID_OFC = S.ID_OFC))

CITY

TARGET

Инза

$300 000.00

Буинск

$575 000.00

Орел

$350 000.00

 

Главный запрос последовательно проверяет строку за строкой таблицы OFFICY. Вложенный запрос находит всех служащих, работающих в текущем офи- се, и возвращает значение с фактическими объемами продаж для каждого служа- щего.

Предложение WHERE главного запроса вычисляет 50\% от плана продаж офиса и сравнивает полученное значение со всеми объемами продаж, выдаваемыми вло-

женным запросом. Если все объемы продаж превышают вычисленное значение, то предикат   ALL возвращает значение TRUE и текущий офис включается в ре- зультирующую таблицу.

4.4.3. П ОДЧИ Н Е ННЫЕ  ЗАПРОСЫ  В  ПРЕДЛОЖЕНИИ  HAVI NG

 

Вложенные запросы могут использоваться также в предложении HAVING, ко- гда требуется отобрать группу строк. Рассмотрим следующий пример, содержа- щий вложенный запрос в предложении HAVING. Вывести список служащих, у ко- торых средняя стоимость заказов на товары, изготовленные компанией ВАЗ, выше, чем общая средняя стоимость заказов.

 

SELECT FAMILY, NAME, AVG(PRICE_ALL) FROM SLUZHASCHIE S, ZAKAZY Z

WHERE S.ID_SLZH = Z.ID_SLZH AND Z.ID_MFR = `ВАЗ` GROUP BY FAMILY, S.ID_SLZH

HAVING AVG(PRICE_ALL) >= (SELECT AVG(PRICE_ALL)

FROM ZAKAZY Z WHERE S.ID_SLZH = Z.ID_SLZH))

 

FAMILY

NAME

AVG(PRICE_ALL)

Ганин

Сергей

 

Петров

Петр

 

Нилов

Лев

 

 

Алгоритм выполнения запроса

1. Вложенный запрос вычисляет среднюю стоимость по всем заказам.

2. Этот простой вложенный запрос вычисляет среднюю стоимость один раз, а затем многократно используется в предложении HAVING.

3. Главный запрос просматривает строки таблицы ZAKAZY, отыскивая все зака-

зы на товары компании ВАЗ, и группирует их по именам служащих.

4. Предложение HAVING сравнивает среднюю стоимость по каждой группе то-

варов со средней стоимостью по всем заказам, вычисленной ранее.

5. Если средняя стоимость по группе больше, чем общая средняя стоимость, то данная группа строк сохраняется, если нет, то данная группа строк исключается.

6. Предложение SELECT создает для каждой группы итоговую строку, содер-

жащую фамилию, имя и среднюю стоимость принятых ими заказов.

Описанный алгоритм выполнения запроса приведен на Рис. 4.2. .

 

4.4.4.  П РАВИЛА  ВЫПОЛ НЕНИЯ  ВЛ О Ж ЕННЫХ  ЗАПР ОСОВ

 

А теперь подведем итоги изучения вложенных запросов и сформулируем правила, позволяющие использовать результаты одного запроса для получения результатов другого.

 

 

Таблица SLUZHASCHIE

 

ID_SLZH

ID_OFFICE

 

 

 

 

 

 

 

 

 

 

 

 
Таблица ZAKAZY

 

 

ID_ORDER