Построение нетривиальных запросов

2.2. Построение нетривиальных запросов

Дается определение подзапроса. Приводятся примеры формирования вложенных подзапросов. Показывается способ построения подзапросов, возвращающих множественные и единичные значения с использованием операторов EXISTS, ALL, ANY.

2.2.1. Понятие подзапроса

Часто невозможно решить поставленную задачу путем одного запроса. Это особенно актуально, когда при использовании условия поиска в предложении WHERE значение, с которым надо сравнивать, заранее не определено и должно быть вычислено в момент выполнения оператора SELECT. В таком случае приходят на помощь законченные операторы SELECT, внедренные в тело другого оператора SELECT. Внутренний подзапрос представляет собой также оператор SELECT, а кодирование его предложений подчиняется тем же правилам, что и основного оператора SELECT. Внешний оператор SELECT использует результат выполнения внутреннего оператора для определения содержания окончательного результата всей операции. Внутренние запросы могут быть помещены непосредственно после оператора сравнения (=, <, >, <=, >=, <>) в предложения WHERE и HAVING внешнего оператора SELECT   –   они получают название подзапросов или вложенных запросов. Кроме того, внутренние операторы SELECT могут применяться в операторах INSERT, UPDATE и DELETE.

Подзапрос   –   это инструмент создания временной таблицы, содержимое которой извлекается и обрабатывается внешним оператором. Текст подзапроса должен быть заключен в скобки. К подзапросам применяются следующие правила и ограничения:

- фраза ORDER BY не используется, хотя и может присутствовать во внешнем подзапросе',

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

- по умолчанию имена столбцов в подзапросе относятся к таблице, имя которой указано в предложении FROM. Однако допускается ссылка и на столбцы таблицы, указанной во фразе FROM внешнего запроса, для чего применяются квалифицированные имена столбцов (т.е. с указанием таблицы);

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

Рекомендуемые материалы

Существует два типа подзапросов:

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

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

2.2.2. Использование подзапросов, возвращающих единичное значение

Пример 55. Определить дату продажи максимальной партии товара.

SELECT Data, Kolichestvo

FROM Sdelka

WHERE Kolichestvo=(SELЕСТ Мах (Kolichestvo) FROM Sdelka)

Во вложенном подзапросе определяется максимальное количество товара. Во внешнем подзапросе   –   Data, для которой количество товара оказалось равным максимальному. Необходимо отметить, что нельзя прямо использовать предложение WHERE

Kolichestvo=Мах(Kolichestvo), поскольку применять обобщающие функции в предложениях WHERE запрещено. Для достижения желаемого результата следует создать подзапрос, вычисляющий максимальное значение количества, а затем использовать его во внешнем операторе SELECT, предназначенном для выборки дат сделок, где количество товара совпало с максимальным значением.

Пример 56. Определить даты сделок, превысивших по количеству товара среднее значение и указать для этих сделок превышение над средним уровнем.

SELECT Data, Kolichestvo,

Kolichestvo –  (SELЕСТ Аvg(Kolichestvo)

FROM Sdelka) AS Prewishenie

FROM Sdelka

WHERE Kolichestvo >

(SELECT Avg(Kolichestvo)

FROM Sdelka)

В приведенном примере результат подзапроса, представляющий собой среднее значение количества товара по всем сделкам вообще, используется во внешнем операторе SELECT как для вычисления отклонения количества от среднего уровня, так и для отбора сведений о Dataх

Пример 57. Определить клиентов, совершивших сделки с максимальным количеством товара.

SELECT Klient.Familiya

FROM Klient INNER JOIN Sdelka

ON Klient.KodKlientа=Sdelka.KodKlientа

WHERE Sdelka. Kolichestvo ^

(SELECT Мах(Sdelka. Kolichestvo)

FROM Sdelka)

Здесь показан пример использования подзапроса при выборке данных из разных таблиц.

Пример 58. Определить клиентов, в сделках которых количество товара отличается от максимального не более чем на 10%.

SELECT Klient.Familiya,

Sdelka.Kolichestvo

FROM Klient

INNER JOIN Sdelka

ON Klient.KodKlientа=Sdelka. Kod Klientа

WHERE Sdelka.Kolichestvo >=0.9*

(SELECT Мах(Sdelka.Kolichestvo)

FROM Sdelka)

Пример 59. Определить фирмы, которые покупают только товары, произведенные в своем городе, и никакие другие.

SELECT DISTINCT Klient.Firma,

Klient.GorodKlientа,

Tovar. Gorod То вара FROM Tovar INNER JOIN

(Klient INNER JOIN Sdelka

ON Klient.KodК лиента=Sdelka.KodKlientа)

ON Tovar.KodTovarа=Sdelka.KodTovarа

WHERE Klient.GorodKlientа NOT IN

(SELECT DISTINCT Klient.GorodKlientа

FROM Tovar INNER JOIN

(Klient INNER JOIN Sdelka

ON Klient.KodKlientа=Sdelka.KodKlientа)

ON Tovar.KodTovarа=Sdelka.KodTovarа

WHERE Klient.GorodKlientао

Tovar. Gorod Tovarа)

Покажем, как применяются подзапросы в предложении HAVING.

Пример 60. Определить даты, когда среднее кол  –  во проданного за день товара оказалось больше 20 единиц.

SELECT Sdelka.Data, АvgSdelka. Kolichestvo) AS

Srednee za den

FROM Sdelka

GROUP BY Sdelka.Data

HAVING Аvg(Sdelka. Kolichestvo)>20

За каждый день определяется среднее кол  –  во товара, которое сравнивается с числом 20. Добавим в запрос подзапрос.

Пример 61. Определить даты, когда среднее кол  –  во проданного за день товара оказалось больше среднего показателя по всем сделкам вообще.

SELECT Sdelka.Data, Аvg(Sdelka. Kolichestvo)

AS Srednee za den

FROM Sdelka

GROUP BY Sdelka.Data

HAVING Аvg(Sdelka. Kolichestvo)>(SELECT А^(Sdelka. Kolichestvo) FROM Sdelka)

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

Если Вам понравилась эта лекция, то понравится и эта - 4. Сглаживающие фильтры.

2.2.3. Использование подзапросов, возвращающих множество значений

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

-  { WHERE | HAVING } выражение [ NOT ] IN (подзапрос);

-  { WHERE | HAVING } выражение оператор сравнения { ALL | SOME | ANY } (подзапрос);

-  (WHERE | HAVING } [ NOT ] EXISTS (подзапрос);