УП СУБД ч1

Рис. 10.24. Топология газовых коммуникаций

Запрос 10.28 формирует список коммуникаций между трубами:

SELECT Branch.«Паспорт» || ‘ выходит из ‘ || MainPipe.«Паспорт» «Коммуникации»

FROM «Трубы» MainPipe

RIGHT JOIN «Трубы» Branch

ON MainPipe.»Номер» = Branch.«Отвод»; (10.28)

Коммуникации

ТСД-32И выходит из ТСД-234А1

ТСД-73 выходит из ТВД-12А

ТСД-234А1 выходит из ТВД-12А

ТНД-43В2 выходит из ТСД-32И

ТВД-12А выходит из

Рис. 10.25. Значение запроса 10.28

В этом запросе используемые имена MainPipe и Branch называются синонимами. Любую таблицу можно снабдить синонимом. Обычно это делается, чтобы избежать в выражениях запроса использования длинных имен исходных таблиц. В случае запросов с самосоединением использование синонимов необходимо для избежания неоднозначности.

Список, созданный запросом 10.28, можно улучшить при помощи оператора CASE:

111

SELECT ‘Труба ‘ || Branch.»Паспорт» ||

CASE WHEN MainPipe.»Паспорт» IS NULL THEN ‘ начальная’

ELSE ‘ выходит из ‘ || MainPipe.«Паспорт» END «Коммуникации»

FROM «Трубы» MainPipe RIGHT JOIN «Трубы» Branch

ON MainPipe.»Номер» = Branch.»Отвод»	(10.29)
ORDER BY Branch.»Отвод» DESC;

Коммуникации

Труба ТВД-12А начальная

Труба ТНД-43В2 выходит из ТСД-32И

Труба ТСД-32И выходит из ТСД-234А1

Труба ТСД-73 выходит из ТВД-12А

Труба ТСД-234А1 выходит из ТВД-12А

Рис. 10.26. Значение запроса 10.29

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

create table «Товары»

(«Дата» date, «Количество» float,

«Товарная группа» char(8));

create table «Товарная группа»

(«Товарная группа» char(8), «Описание» char(100), «Ставка налога» float);

Дата	Количество	Товарная группа
01.09.12	126,5	МЕТ
07.10.12	2045,2	ОВЩ
01.06.12	60	МЕТ
24.08.12	210	МЕТ
22.09.12	540,5	ОВЩ
05.05.12	2300	ОВЩ
17.06.12	3680	ОВЩ

Рис. 10.27. Данные таблицы «Товары»

112

Товарная группа	Описание	Ставка налога
МЕТ	Металлы	12,5
ОВЩ	Овощи	2,4
РСТ	Растения	5,4

Рис. 10.28. Данные таблицы «Товарная группа»

Таблица «Товары» содержит сведения о партиях товаров, а таблица «Товарная группа» – сведения о ставках налога на каждую товарную группу. Обратите внимание, что в таблице «Товары» нет записей, соответствующих товарной группе «РСТ».

Таблицы типа «Товарная группа» часто называют табли- цами-классификаторами, поскольку они содержат описания всех возможных категорий (классов), в данном случае – все возможные товарные группы.

Задача состоит в вычислении суммарного налога по каждой товарной группе для всех товаров, включая и те товарные группы, по которым товаров фактически нет.

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

SELECT T.«Дата»,T. «Количество»,T.«Товарная группа», G.«Товарная группа», G.«Ставка налога»

FROM «Товары» T FULL JOIN «Товарная группа» G

ON T.«Товарная группа» = G. «Товарная группа»; (10.30)

	Дата	Количество	Товарная	Товарная	Ставка
			группа	группа	налога
	24.08.12	210	МЕТ	МЕТ	12,5
	01.06.12	60	МЕТ	МЕТ	12,5
	01.09.12	126,5	МЕТ	МЕТ	12,5
	17.06.12	3680	ОВЩ	ОВЩ	2,4
	05.05.12	2300	ОВЩ	ОВЩ	2,4
	22.09.12	540,5	ОВЩ	ОВЩ	2,4
	07.10.12	2045,2	ОВЩ	ОВЩ	2,4
	-	-	-	РСТ	5,4

Рис. 10.29. Значение запроса 10.30

113

Действительно, поля для записи РСТ присутствуют в результирующем множестве, отсутствующие поля из таблицы «Товары» заменены пустыми значениями.

Для вычисления антисоединения (в данном случае определяющего товарные группы, по которым не существует товаров) можно воспользоваться следующим запросом:

SELECT G. «Товарная группа»

FROM «Товары» T FULL JOIN «Товарная группа» G

ON T.«Товарная группа» = G.«Товарная группа»

WHERE T.«Товарная группа» IS NULL;

(10.31)

Товарная группа

РСТ

Рис. 10.30. Значение запроса 10.31

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

Выполним группировку результирующего множества по полю G.«Товарная группа» (прочие выражения должны быть удалены из списка после SELECT):

SELECT G.«Товарная группа», COUNT( T.«Товарная группа» ) FROM «Товары» T FULL JOIN «Товарная группа» G

ON T.«Товарная группа» = G.«Товарная группа»		(10.32)
GROUP BY G.«Товарная группа»;
Товарная группа	COUNT(T.«ТОВАРНАЯГРУППА»)
ОВЩ	4
МЕТ	3
РСТ	0

Рис. 10.31. Значение запроса 10.32

Здесь важно использование для группировки именно поля из таблицы-классификатора (причем можно было бы использовать и поле «Описание»), поскольку группировка по T. «Товарная группа» приведет к совершенно другому результату:

114

SELECT T.«Товарная группа», COUNT( T.«Товарная группа») FROM «Товары» T FULL JOIN «Товарная группа» G

ON T.«Товарная группа» = G.«Товарная группа»

GROUP BY T.«Товарная группа»;		(10.33)
Товарная группа	COUNT(T.«ТОВАРНАЯГРУППА»)
-	0
ОВЩ	4
МЕТ	3

Рис. 10.32. Значение запроса 10.33

Далее при использовании агрегатной функции count() следует использовать любое поле из таблицы «Товары», а не из классификатора, и не символ *:

SELECT G.«Товарная группа», COUNT( T.«Товарная группа») FROM «Товары» T FULL JOIN «Товарная группа» G

ON T.«Товарная группа» = G.«Товарная группа»

GROUP BY G.«Товарная группа»;		(10.34)
Товарная группа	COUNT(T.»ТОВАРНАЯГРУППА»)
ОВЩ		4
МЕТ		3
РСТ		0

Рис. 10.33. Значение запроса 10.34

SELECT G.«Товарная группа», COUNT( G.«Товарная группа») FROM «Товары» T FULL JOIN «Товарная группа» G

ON T.«Товарная группа» = G.«Товарная группа»

GROUP BY G.«Товарная группа»;		(10.35)
	Товарная группа	COUNT(*)
	ОВЩ	4
	МЕТ	3
	РСТ	1

Рис. 10.34. Значение запроса 10.35

Впервом случае значение функции count() применяется

кгруппе, в которой значения T.«Товарная группа» – пустые и, следовательно, игнорируются, а во втором случае значения G.«Товарная группа» пустыми не являются.

115

Для вычисления суммы налога для каждой группы используем агрегатную функцию SUM:

SELECT G.«Товарная группа», COUNT( T.«Товарная группа»), SUM( T.«Количество» *G.«Ставка налога» ) «Сумма»

FROM «Товары» T FULL JOIN «Товарная группа» G ON T.«Товарная группа» = G.«Товарная группа»

GROUP BY G.«Товарная группа»;		(10.36)
Товарная группа	COUNT(T.«ТОВАРНАЯГРУППА»)	«Сумма»
ОВЩ	4	20557,68
МЕТ	3	4956,25
РСТ	0	-

Рис. 10.35. Значение запроса 10.36

Сумма для группы РСТ – пустое значение, поскольку, в отличие от функции count(), функция sum() для группы пустых значений будет так же пустым значением. Используя функцию nvl(), выборке можно придать следующий вид, не содержащий пустых значений:

SELECT G.«Товарная группа»,

COUNT( T.«Товарная группа») «Количество», NVL(SUM(T.«Количество»*G.«Ставка налога»),0) «Сумма налога»

FROM «Товары» T FULL JOIN «Товарная группа» G ON T.«Товарная группа» = G.«Товарная группа»

GROUP BY G.«Товарная группа»;		(10.37)
Товарная группа	Количество	Сумма налога
ОВЩ	4	20557,68
МЕТ	3	4956,25
РСТ	0	0

Рис. 10.36. Значение запроса 10.37

Выводы

•в полях записей может храниться специальное значение, называемое пустым значением, которое записывается при помощи служебного слова NULL;

116

•пустые значения нельзя использовать в операциях сравнения и в арифметических и строковых операциях, значение таких операций – FALSE и NULL соответственно;

•вычисление логических операций с пустым значением имеет значение UNKNOWN;

•для корректного сравнения с пустым значением используются специальные предикаты IS NULL и IS NOT NULL;

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

•соединение можно выполнять по одному полю из пары таблиц или по группе полей;

•для запросов с соединением можно использовать специальные операторы соединения JOIN;

•к операторам соединения относятся операторы внут­ реннего соединения (INNER JOIN), кросс-соединения (CROSS JOIN) и несимметричные соединения LEFT JOIN, RIGHT JOIN и FULL JOIN;

•при вычислении несимметричных соединений значения полей несуществующих записей заменяются пустыми значениями;

•несимметричные соединения полезны для выявления «несвязанных» записей; этот тип запросов с соединением часто называется антисоединением;

•если в запросе с соединением некоторая таблица в конструкции FROM используется два и более раз, такой запрос называется запросом с самосоединением.

Вопросы для контроля

1.Опишите механизм вычисления многотабличного запроса.

2.Объясните, что такое операция эквисоединения таблиц.

3.Перечислите особенности использования пустых значений в выражениях языка SQL.

4.Перечислите виды несимметричных соединений и сформулируйте различия в исполнении подобных операций.

5.Что такое «рекурсивные» запросы?

117

Раздел 11. Язык Oracle SQL. Подзапросы

В данном разделе описаны средства языка SQL Oracle для работы с подзапросами и основные способы их практического применения. В данный раздел также помещены сведения о множественных операциях.

SQL-запросы могут содержать вложенные SELECT-выра­ жения, называемые подзапросами. Подзапросы используются в том числе в логических выражениях в конструкциях WHERE и HAVING и в качестве «динамических» таблиц в конструкции FROM.

11.1. Подзапросы в логических выражениях WHERE и HAVING

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

SELECT name, position FROM Staff				(11.1)
WHERE name IN ( SELECT staff FROM Course );
	NAME	POSITION
	G.Anderson	Senior Lector
	Won Kim	Full Professor

Рис. 11.1. Значение запроса 11.1

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

SELECT name, position FROM Staff
WHERE (name, ‘Graphics’ )	(11.2)
IN ( SELECT staff, title FROM Course );

118

Процесс вычисления запроса 11.1 можно представить следующим образом. Сначала вычисляется подзапрос и образуется список значений, который затем «подставляется» в правую часть предиката IN, после этот предикат проверяется для каждой строки исходной таблицы. Иными словами, можно было бы сначала вычислить запрос SELECT DISTINCT staff, title FROM Course, а затем использовать его значение (‘G.Anderson’ и ‘Won Kim’) в тексте следующего эквивалентного запроса:

SELECT name, position FROM Staff
WHERE name IN	(11.3)
( ‘G.Anderson’, ‘Won Kim’ );

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

SELECT DISTINCT S.name, S.position
FROM Staff S JOIN Course C	(11.4)
ON C.staff = S.name;

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

SELECT DISTINCT S.name, S.position, C.title
FROM Staff S JOIN Course C	(11.5)
ON C.staff = S.name;

Эквивалентный запрос с подзапросами невозможно написать, поскольку не существует таблицы, в которой бы присутствовали все три требуемых поля, а «передать» значения из подзапроса во внешний запрос и поместить это значение в результирующее множество нельзя. Можно сказать, что подзапросы предназначены для того, чтобы проверить наличие данных из внешнего запроса в других таблицах.

Однако подзапросы могут использовать данные из таблиц, используемых во внешнем SELECT-выражении. Рассмотрим запрос, определяющий имя и должность преподавателя, проводящего курс ‘OS’:

119

SELECT name, position FROM Staff
WHERE name IN ( SELECT name FROM Course
WHERE Staff.name = staff				(11.6)
	AND title = ‘OS’);
	NAME	POSITION
	G.Anderson	Senior Lector

Рис. 11.2. Значение запроса 11.6

В запросе 11.6 внутри текста подзапроса используется значение поля Staff.name. Такие подзапросы называются коррелированными. При вычислении внешнего запроса подзапрос должен вычисляться для каждой строки таблицы внешнего запроса, а не один раз для всех строк, как это было для предыдущих подзапросов (называемых соответственно некоррелированными), и значения подзапросов для каждой строки внешней таблицы будут различными. Можно сказать, что коррелированный подзапрос выполняет неявное соединение таблицы подзапроса и таблицы внешнего запроса. Так, запрос 11.6 эквивалентен следующему запросу:

SELECT S.name, S.position FROM Staff S, Course C	(11.7)
WHERE S.name = C.staff AND C.title = ‘OS’;

Запрос 11.6 можно преобразовать в более короткий, хотя и менее очевидный вид:

SELECT name, position FROM Staff
WHERE ‘OS’ IN
( SELECT title FROM Course	(11.8)
WHERE Staff.name = staff );

Несмотря на то что в левой части предиката IN присутствует константа, подзапрос по-прежнему является коррелированным.

Следующий запрос 11.9 определяет студента с максимальной оценкой:

SELECT student, course, result FROM CourseResult
WHERE result =	(11.9)
( SELECT MAX(result) FROM CourseResult )

120