Глушаков С.В., Ломотько Д.В. Базы данных

Подождите немного. Документ загружается.

160 Часть II. Структурированный язык запросов SQL

Костыркин Олег

Кааанко Виталий

Позднякова Любовь

Загарийчук Игорь

Федченко Светлана

Владимирович

Алексеевна

Дмитриевич

Геннадиевна

и запроса

SELECT TEACHERS.TFAM, PREDMET.PNAME

FROM TEACHERS, PREDMET

WHERE TEACHERS.TNUM = PREDMET.TNUM;

Вывод этого запроса такой:

TFAM

PNAME

Никулина Физика

Костыркин Химия

Казанко Математика

Позднякова Экономика

Загарийчук Философия

• Казалось бы, что эти два запроса должны давать одинаковое

количество строк, но результаты первого запроса насчитывают

шесть строк, а второго - только пять. Это связано с тем, что пре-

подаватель Федченко в настоящий момент еще не получила но-

мера и соответствующая запись имеет значение NULL в поле

TNUM, следовательно, это значение не совпадает ни с одним

идентификатором учебного предмета в таблице PREDMET. Зна-

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

просто исчезает из объединения. Таким образом, стандартное

SQL-объединение может привести к потере информации, если

объединяемые таблицы содержат несвязанные строки.

Второй запрос можно модифицировать следующим образом:

SELECT TEACHERS.TFAM, PREDMET.PNAME

. FROM TEACHERS, PREDMET

WHERE TEACHERS. TNUM *= PREDMET. TNUM;

и его вывод будет более полный:

Глава 2.2. Выборка, или чтение данных

161

TFAM

PNAME

Викулина Физика

Костыркин Химия

Казанко Математика

Позднякова Экономика

Загарийчук Философия

Федченко NULL

Такие результаты запроса получаются с помощью другой опе-

рации объединения, называемой внешним объединением таблиц,

которое в предложении WHERE обозначается символом *=.

Внешнее объединение является расширением стандартного объе-

динения, описанного выше и иногда называемого внутренним

объединением таблиц. Имейте в виду, что в стандарте SQL1 дано

определение только внутреннего объединения, а понятие внеш-

него объединения в нем отсутствует.

Рассмотрим полное внешнее объединение таблиц на примере

следующего запроса:

SELECT STUDENTS.SFAM, STUDENTS.SNUM,

USP.PNUM, USP.SNUM

FROM STUDENTS, USP

WHERE STUDENTS.SNUM = USP.SNUM;

Вывод этого запроса приведен ниже:

SFAM SNUM PNUM SNUM

Поляков 3412 2001 3412

Поляков 3412 2003 3412

Старова 3413 2003 3413

Гриценко 3414 2005 3414

Нагорный 3416 2004 3416

Этот запрос формирует информацию о студентах и кодах

учебных предметов, которые ими сданы. Это внутреннее объеди-

нение дает пять строк вывода, показывая соответствующие пары

студент - код предмета. Для студента Котенко никакого вывода

нет. поскольку он никаких дисциплин не сдавал.

Теперь предположим, что необходимо вывести ту же инфор-

мацию, но с учетом студентов, которым не соответствует ни один

6 «'Б;

•азы данных>.-

162

Часть II. Структурированный язык запросов SQL

учебный предмет. Такой запрос, построенный по правилам пол-

ного внешнего объединения, будет выглядеть так:

SELECT STUDENTS.SFAM, STUDENTS.SNUM,

USP.PNUM, USP.SNUM

FROM STUDENTS, USP

WHERE STUDENTS.SNUM *=* USP.SNUM;

Результат этого запроса такой:

SFAM SNUM PNUM SNUM

Поляков 3412

Старова 3413

Гриценко 3414

Котенко 3415

Нагорный 3416

2001 3412

2003 3412

2003 3413

2005 3414

NULL NULL

2004 3416

В этот вывод поп&та строки со значениями NULL, т.е. запись

для студента Котенко была как бы привязана к несуществующей

строке таблицы USP, содержащей только значения NULL, и до-

бавлена в результат запроса. Как видно из этого примера, внеш-

нее объединение является сохраняющим информацию и каждая

строка таблицы - операнда представлена в выводе результатов

запроса. Следовательно, полное внешнее объединение таблиц

получается из внутреннего объединения двух таблиц обычным

способом, при этом каждая запись первой таблицы, которая не

имеет связи ни с одной строкой во второй таблице, дополняется в

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

строка второй таблицы, которая не имеет связи ни с одной стро-

кой первой таблицы, дополняется в результатах запроса значе-

ниями NULL.

Существует еще так называемые левое и правое внешние объ-

единения. Если полное внешнее объединение симметрично по

отношению к обеим таблицам-операндам, то эти два типа внеш-

них объединений не симметричны относительно двух таблиц.

Левое внешнее объединение двух таблиц записывается в ко-

манде WHERE в виде *= и получается в результате выполнения

внутреннего объединения таблиц. При этом происходят действия,

аналогичные полному объединению, однако без замены NULL

Глава 2.2. Выборка, или чтение данных

163

значениями информации, взятой из второй таблицы. Пример ле-

вого внешнего объединения фактически нами уже был рассмот-

рен выше в примере для преподавателей и предметов.

Правое внешнее объединение двух таблиц записывается в ко-

манде WHERE в виде =* и получается из обыкновенного внут-

реннего объединения таблиц в последовательности реализации

полного объединения, при этом замена на NULL значения в пер-

вой таблице не производится.

На практике левое и правое внешние объединения более по-

лезны, чем полное внешнее объединение, особенно при связи

таблиц через ключи. Система записи внешнего объединения по-

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

отношений. Например, допустим следующий запрос, представ-

ляющий, по сути, левое внешнее объединение:

SELECT STUDENTS.SFAM, STUDENTS.SNUM,

USP.PNUM, USP.SNUM

FROM STUDENTS, USP

WHERE STUDENTS.SNUM *> USP.SNUM;

Результат этого запроса, приведенный не полностью из-за его

значительных размеров, будет следующий:

SFAM

SNUM PNUM SNUM

Поляков

Старова

Гриценко

3412 NULL NULL

3413 2001 3412

3413 2003 3412

3414 2001 3412

3414 2003 3412

3414 2003 3413

Данный запрос выводит информацию о студентах и о сданных

учебных предметах, при этом данные о предметах выбираются

для студентов, имеющих номер меньше, чем текущий. В случае,

если таковых не обнаруживается, SQL формирует строку из пер-

вой таблицы, заполняя NULL значениями данные, которые долж-

ны были быть получены из второй.

164

Часть II. Структурированный язык запросов SQL

Хотя система записи внешнего объединения достаточно удоб-

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

сью внешних объединений возникают при расширении объеди-

нения до трех или более таблиц. Например, при записи

TABLE1 *=* TABLE2 *=* TABLE3

фактически выполняется внешнее объединение

(TABLE1 *=* TABLE2) *=* TABLE3

что, вообще говоря, приведет к различным результатам.

Для создателей стандарта SQL2 внешние объединения были

серьезной проблемой, т.к. внешние объединения являются порой

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

необходимых запросов. Поэтому в стандарте SQL2 был опреде-

лен совершенно новый метод поддержки внешних объединений:

в спецификации стандарта SQL2 поддержка внешних объедине-

ний осуществляется в предложении FROM со специальным син-

таксисом, позволяющим пользователю точно определить, как

исходные таблицы должны быть объединены в запросе.

Например, уже рассмотренный нами запрос внутреннего объ-

единения в стандарте SQL1 выглядит так:

SELECT STUDENTS.SFAM, STUDENTS.SNUM,

USP.PNUM, USP.SNUM

FROM STUDENTS, USP

WHERE STUDENTS.SNUM = USP.SNUM;

а в стандарте SQL2 - так (хотя использование первого вариан-

та допускается):

SELECT STUDENTS.SFAM, STUDENTS.SNUM,

USP.PNUM, USP.SNUM

FROM STUDENTS INNER JOIN USP

ON STUDENTS.SNUM = USP.SNUM;

Обратите внимание на то, что в последнем случае две объеди-

няемые таблицы соединяются явно посредством операции JOIN,

а условие поиска, описывающее объединение, находится теперь в

предложении ON внутри предложения FROM. В условии поиска,

следующем за ключевым словом ON, могут быть заданы любые

критерии сравнения строк двух объединяемых таблиц, в том чис-

ле с использованием булевских операторов.

Глава 2.2. Выборка, или чтение данных

165

Наконец, приведем пример полного внешнего объединения

(аналогичный запрос был уже рассмотрен):

SELECT STUDENTS.SFAM, STUDENTS.SNUM,

USP.PNUM, USP.SNUM

FROM STUDENTS FULL OUTER JOIN USP

ON STUDENTS.SNUM = USP.SNUM;

Его результаты приведены ниже:

SFAM SNUM PNUM SNUM

3412 2001 3412

3412 2003 3412

3413 2003 3413

Поляков

Сатарова

Гриценко 3414 2005 3414

Котенке 3415 NULL NULL

Нагорный 3416 2004 3416

Таким образом, в этих двухтабличных объединениях все со-

держимое предложения WHERE просто перешло в предложение

ON, следовательно, ничего принципиально нового ON не добав-

ляет в язык SQL. Однако такая структура позволяет более точно

определить условие объединения.

Использование вложенных запросов

В самом общем случае, запросы могут управлять другими за-

просами - это делается путем размещения запроса внутрь преди-

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

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

пример, требуется информация об успеваемости студента с фа-

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

номер этого студента. Тогда необходимо извлечь этот номер из

таблицы с данными о студентах, и после этого применять резуль-

тат к таблице успеваемости. Это можно реализовать путем сле-

дующей конструкции:

SELECT *

FROM USP

166

Часть II. Структурированный язык запросов SQL

WHERE SNUM =

(SELECT SNUM

FROM STUDENTS

WHERE SFAM = 'Поляков');

Результат этого запроса будет следующий:

UNUM OCENKA UDATE SNUM PNUM

1001 5 10/06/1999 3412 2001

1004 4 12/06/1999 3412 2003

Чтобы выполнить основной запрос, SQL сначала должен оце-

нить внутренний запрос (его называют подзапросом) внутри

предложения WHERE. Происходит это традиционным образом,

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

для определения значения предиката данные, а только затем -

основной. Разумеется, подзапрос должен выбрать только одно

поле, а тип данных этого поля должен совпадать с тем значением,

с которым он будет сравниваться в предикате.

С другой стороны, возможна ситуация, когда подзапрос выда-

ет в качестве результата несколько различных значений, что мо-

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

и команда выдаст ошибку. При использовании подзапросов в

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

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

отображать только одну строку вывода. Кроме того, при исполь-

зовании подзапроса, который вообще не выводит никаких значе-

ний, основной запрос не выведет никаких значений: его предикат

будет иметь неизвестное значение.

В некоторых случаях стоит использовать DISTINCT для того,

чтобы в подзапросе получить одиночное значение. Предположим,

что преподаватели могут вести занятия по разным дисциплинам.

Тогда для получения ответа на вопрос о том, какие дисциплины

ведет преподаватель Викулина, можно воспользоваться запросом:

SELECT *

FROM PREDMET

WHERE TNUM =

(SELECT DISTINCT TNUM

FROM TEACHERS

WHERE TFAM = 'Викулина');

Глава 2.2. Выборка, или чтение данных

167

В результате будет получено:

PNUM PNAME TNUM HOURS COURS

2001 Физика 4001 34 1

Подзапрос установил, что значение поля TNUM совпало с

фамилией Викулина при значении 4001, а затем основной запрос

выделил все записи с этим значением TNUM из таблицы предме-

тов. Т.к., вообще говоря, могло получиться, что преподаватель

ведет несколько предметов, то фраза DISTINCT в данном случае

обязательна - если подзапрос возвратил бы более одного значе-

ния, то это вызвало бы ошибку.

Следует иметь в виду, что предикаты с подзапросами являют-

ся необратимыми, т.е. предикаты, включающие подзапросы, ис-

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

<ВЫРАЖЕНИЕ> <ОПЕРАТОР> <ПОДЗАПРОС>,

и ни в коем случае не

<ПОДЗАПРОС> <ОПЕРАТОР> <ВЫРАЖЕНИЕ>,

или

<ПОДЗАПРОС> <ОПЕРАТОР> <ПОДЗАПРОС>,

Иначе говоря, предыдущий пример, записанный следующим

образом:

SELECT *

FROM PREDMET

WHERE (SELECT DISTINCT TNUM

FROM TEACHERS

WHERE TFAM = 'Викулина') = TNUM;

является неверным.

Разумеется, в подзапросах допускается использование агре-

гатных функций - это удобно и по той причине, что они автома-

тически производят одиночное значение для любого числа строк,

которое может быть использовано в основном предикате. Напри-

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

168

Часть II. Структурированный язык запросов SQL

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

этого воспользуемся запросом:

SELECT *

FROM USP

WHERE OCENKA >

(SELECT AVG (OCENKA)

FROM USP) ;

Вывод такого запроса следующий:

• UNUM OCENKA UDATE SNUM PNUM

1001 5 10/06/1999 3412 2001

1005 5 12/06/1999 3416 2004

Средняя оценка по имеющимся данным составляет 4.2, следо-

вательно, основной запрос выбирает только те записи, в которых

значение поля OCENKA больше, чем 4.2.

Не стоит забывать, что сгруппированные агрегатные функции,

определенные в терминах предложения GROUP BY, могут выда-

вать многочисленные значения, а значит, не допускаются в под-

запросах такого характера. Даже если GROUP BY или HAVING

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

помощью подзапроса, все равно команда будет отклонена. Кстати

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

любое число строк, если используется специальный оператор IN

(операторы BETWEEN, LIKE, и IS NULL не могут использовать-

ся с подзапросами). IN определяет набор значений предиката,

одно из которых должно совпадать с другим по порядку. При

использовании IN с подзапросом, SQL просто формирует этот

набор из вывода подзапроса, а значит, допускается использование

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

запрос

SELECT *

FROM PREDMET

WHERE PREDMET.TNUM IN

(SELECT TEACHERS.TNUM

FROM TEACHERS

WHERE TEACHERS.TFAM

BETWEEN •И• AND ' С ' ) ;

Глава 2.2. Выборка, или чтение данных 169

Вывод этого запроса такой:

PNUM PNAME TNUM HOURS COURS

2002 Химия 4002

2003 Математика 4003

2005 Экономика 4004

Такой запрос гораздо понятнее, чем выполненный с помощью

объединения, хотя дающий такой же результат:

SELECT PREDMET.PNUM, PREDMET.PNAME,

TEACHERS.TNUM, PREDMET.HOURS,

PREDMET.COURS

FROM PREDMET, TEACHERS

WHERE TEACHERS.TNUM = PREDMET.TNUM

AND TEACHERS.TFAM BETWEEN 'И

AND "C;

Вообще говоря скорость выполнения того или иного запроса,

например, как в рассмотренном случае, зависит от реализации

той СУБД, которая для этого используется. Выше уже поднима-

лась проблематика, связанная с работой оптимизатора запросов,

который пытается найти наиболее эффективный способ выпол-

нения. Традиционно хороший оптимизатор чаще всего преобра-

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

няется быстрее.

И еще один: интересный момент: в любой ситуации, где при-

меняется реляционный оператор равенства (=), можно использо-

вать IN. В отличие от первого, IN не может заставить запрос по-

терпеть неудачу, если подзапросом выбрано больше чем одно

значение. Это может быть или преимуществом или недостатком.

Например, уже рассмотренный нами выше запрос можно перепи-

сать следующим образом:

SELECT *

FROM PREDMET

WHERE TNUM IN

(SELECT TNUM

FROM TEACHERS

WHERE TFAM = 'Викулияа');

Таким образом, подзапросы всегда определяют одиночные

столбцы - это обязательно, поскольку выбранный вывод сравни-

вается с одиночным значением. Подтверждением этому является

170

Часть II. Структурированный язык запросов SQL

то. что команда SELECT * не может использоваться в подзапро-

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

ное на поле, а не просто само поле, в предложении SELECT. Это

может быть выполнено с помощью реляционных операторов или

при использовании IN. Примером может служить следующий

запрос:

SELECT *

FROM PREDMET

WHERE PNUM =

(SELECT PNUM-1

FROM PREDMET

WHERE PNAME =

Философия');

Вывод для этого запроса такой:

PNUM PNAME TNUM

HOURS COURS

2003 Математика 4003 68

Этот запрос находит информацию об учебном предмете, код

которого на 1 меньше кода философии. Разумеется, поле PNUM

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

вызовет ошибку.

Можно также использовать подзапросы внутри предложения

HAVING. Эти подзапросы могут использовать свои собственные

агрегатные функции, если они не производят многочисленных

значений, или использовать GROUP BY или HAVING. Например,

рассмотрим запрос:

SELECT OCENKA, COUNT (DISTINCT SNUM)

FROM USP

GROUP BY OCENKA

HAVING OCENKA > =

(SELECT AVG (OCENKA)

FROM USP

WHERE PNUM = 2003);

Вывод для этого запроса следующий:

OQENKA

Глава 2.2. Выборка, или чтение данных

171

Данный запрос подсчитывает количество студентов с оценка-

ми выше средней, чем по дисциплине с PNUM = 2003.

Теперь несколько слов об объединении запросов и использо-

вании при этом подзапросов. Операция, которая бывает часто

полезна - это объединение из двух запросов, в котором второй

запрос выбирает строки, исключенные первым. Наиболее часто

это делают для того, чтобы не исключать строки, которые не

удовлетворили предикату при объединении таблиц. Этот процесс

принято называть внешним объединением.

Предположим, что некоторые из студентов еще не получили

оценку, однако уже внесены в таблицу USP. Например, в эту таб-

лицу добавлена запись {1006, NULL. NULL, 3416, NULL}. Если

возникает необходимость в просмотре успеваемости студентов

по дисциплине, не учитывая тех, кто еще не получил оценку.

Этого можно достичь, формируя объединение из двух запросов,

один из которых выполняет объединение, а другой выбирает сту-

дентов с NULL значениями поля OCENKA. Этот последний за-

прос должен вставлять сообщение в поля, соответствующие полю

PNAME, и значение 0 в поле OCENKA в первом запросе. Как

было рассмотрено ранее, можно вставлять текстовые строки в

вывод, чтобы идентифицировать запрос, который вывел данную

строку. Использование этой методики во внешнем объединении

дает возможность применять предикаты для классификации, а не

для исключения. Следующий запрос выполняет эти действия:

SELECT USP.SNUM, STUDENTS.SFAM,

PREDMET.PNAME, USP.OCENKA

FROM USP, STUDENTS, PREDMET

WHERE USP.SNUM = STUDENTS.SNUM

AND USP.PNUM = PREDMET.PNUM

UNION

SELECT USP.SNUM, STUDENTS.SFAM,

'НЕТ ', 0

FROM USP, STUDENTS

WHERE USP.SNUM = STUDENTS.SNUM

AND NOT USP.OCENKA = ANY

(SELECT OCENKA

FROM USP)

ORDER BY 2 ASC;

Вывод этого запроса следующий:

172

Часть II. Структурированный язык запросов SQL

3414 Гриценко Экономика 3

3416 Нагорный Философия 5

3416 Нагорный НЕТ О

3412 Поляков Физика 5

3412 Поляков Математика 4

3413 Старова Математика 4

Обратите внимание на то, что строка 'НЕТ ' была дополнена

пробелами, чтобы получить совпадение поля PNAME по длине.

Второй запрос выбирает даже те строки, которые были исключе-

ны первым.

Несколько слов об использованном операторе ANY, более

подробно о котором будем говорить ниже. Оператор ANY берет

все значения, выведенные подзапросом (для нашего случая - это

все значения, OCENKA в таблице USP), и оценивает их как вер-

ные, если любое из их равняется значению оценки текущей запи-

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

В заключение разговора о вложенных запросах, поговорим о

так называемых соотнесенных подзапросах. Когда используются

подзапросы, в SQL имеется возможность обратиться к внутрен-

нему запросу таблицы в предложении внешнего запроса FROM, с

помощью соотнесенного подзапроса. При этом подзапрос выпол-

няется неоднократно, по одному разу для каждой записи таблицы

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

можно найти данные на всех студентов, которые получали оцен-

ки 10/06/1999:

SELECT *

FROM STUDENTS FIRST

WHERE 10/06/1999 IN

(SELECT UDATE

FROM USP SECOND

WHERE FIRST.SNUM

= SECOND.SNUM);

Вывод этого запроса таков:

SNUM SFAM SIMA

SOTCH

STIP

3412 Поляков

3413 Огарева

Анатолий Алексеевич 25,50

Любовь Михайловна 17.00

Глава 2.2. Выборка, или чтение данных

173

В этом примере FIRST и SECOND - псевдонимы таблиц, при

этом получается, что значение в поле SNUM внешнего запроса

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

дельно для каждой строки внешнего запроса.

Строку внешнего запроса, для которой внутренний запрос ка-

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

учетом этого, процедура оценки, выполняемой соотнесенным

подзапросом:

• выбор строки из таблицы во внешнем запросе - это теку-

щая строка;

• сохранение значения текущей строки в псевдониме,

имя которого определено в предложении FROM внеш-

него запроса;

• выполнение подзапроса, при этом везде, где найден псев-

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

кущей строки (это принято называть внешней ссылкой);

• оценка предиката внешнего запроса на основе результа-

тов подзапроса;

• описанная выше последовательность повторяется для

следующей строки из таблицы внешнего запроса, и так до

тех пор, пока все строки не будут проверены.

Вообще говоря, последний пример мог быть реализован, ис-

пользуя объединение следующего вида:

SELECT STUDENTS.SNUM, STUDENTS.SFAM,

STUDENTS.SIMA, STUDENTS.SOTCH,

STUDENTS.STIP

FROM STUDENTS, USP

WHERE STUDENTS.SNUM = USP.SNUM

AND USP.UDATE = 10/06/1999;

Предположим, что есть необходимость в выводе фамилии и

номера всех студентов, которые получили более одной оценки.

Это реализуется следующим запросом:

SELECT SNUM, SFAM

FROM STUDENTS FIRST

WHERE 1 <

(SELECT COUNT(*)

FROM USP

WHERE SNUM = FIRST.SNUM) ;

174

Часть II. Структурированный язык запросов SQL

Вывод этого запроса приведен ниже:

SNUM SFAM

3412 Поляков

Стоит обратить внимание на то, что предложение FROM под-

запроса в этом примере не использует псевдоним. При отсутст-

вии имени таблицы или префикса псевдонима, SQL может для

начала принять, что любое поле выводится из таблицы с именем,

указанным в предложении FROM текущего запроса. Если поле с

этим именем отсутствует в этой таблице, SQL будет проверять

внешние запросы. Именно по этой причине префикс имени таб-

лицы обычно необходим в соотнесенных подзапросах - для отме-

ны этого предположения. Псевдонимы также часто необходимы

для того, чтобы дать возможность ссылаться к той же самой таб-

лице во внутреннем и внешнем запросе без какой-либо неодно-

значности.

Часто соотнесенный подзапрос используют на основе той же

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

извлечь сложные формы информации. Например, с помощью

следующего запроса можно найти все оценки по дисциплине со

значениями, выше средней по этой же дисциплине:

SELECT *

FROM USP FIRST

WHERE OCENKA >

(SELECT AVG (OCENKA)

FROM USP SECOND

WHERE SECOND.PNUM = FIRST.PNUM);

В данном случае вывода у запроса не будет, т.к. в таблице

USP нет записей для студентов, имеющих по какому-либо учеб-

ному предмету оценку выше средней. Если же условие внешнего

предиката изменить на >=, то в качестве результата будут выве-

дены все данные таблицы успеваемости.

Предложение HAVING может также работать и с соотнесен-

ными подзапросами. При использовании соотнесенного подза-

проса в предложении HAVING необходимо помнить, что преди-

кат оценивается для каждой группы из внешнего запроса, а не для

каждой строки. Следовательно, подзапрос будет выполняться

Глава 2.2. Выборка, или чтение данных

175

один раз для каждой группы, выведенной из внешнего запроса, а

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

чения оценок за каждый день, причем такой, что это среднее

должно быть больше или равно хотя бы на половину балла, чем

минимальное значение оценки в этот день, можно воспользовать-

ся запросом:

SELECT UDATE, AVG (OCENKA)

FROM USP FIRST

GROUP BY UDATE

HAVING AVG (OCENKA) >=

(SELECT MIN(OCENKA) +0.5

FROM USP SECOND

WHERE FIRST.UDATE = SECOND.UDATE);

Результат запроса будет такой:

UDATE

10/06/1999 4.5

12/06/1999 4.5

Подзапрос вычисляет значение MIN для всех записей с той

же самой датой, что и у текущей агрегатной группы основного

запроса.

Соотнесенные подзапросы по своей сути близки к объедине-

ниям - обе конструкции включают проверку каждой записи одной

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

таблицы, при этом большинство операций у них похожи. По этой

причине подробно останавливаться на этом материале не будем.

Таким образом, применение вложенных запросов в целях ис-

пользования их результата для управления другим запросом рас-

ширяет возможности SQL, позволяя выполнить большее количе-

ство функций.

Использование операторов EXISTS,

ANY, ALL, И SOME

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

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

берут подзапросы в качестве аргументов - это EXISTS, ANY.

ALL, и SOME.

176

Часть II. Структурированный язык запросов SQL

Оператор EXISTS используется для указания предикату на то,

чтобы производить или не производить вывод в подзапросе, при

этом EXISTS дает в качестве результата значение ИСТИНА или

ЛОЖЬ. Это в свою очередь означает, что он может работать в

предикате или в комбинации с другими булевскими выражения-

ми - AND, OR, и NOT. Другими словами, EXISTS берет подза-

прос как аргумент и оценивает его как истинный, если он осуще-

ствляет любой вывод, или как ложный, если он не делает этого.

Например, можно решить, извлекать ли данные из таблицы успе-

ваемости, если в ней присутствуют отличные оценки. Это реали-

зуется следующим образом:

SELECT *

FROM USP

WHERE USP.OCENKA = 5

AND EXISTS

(SELECT *

FROM USP

WHERE USP.OCENKA = 5);

Результаты запроса будут следующие:

UNUM OCENKA UDATE SNUM PNUM

1001 5 10/06/1999 3412 2001

1005 5 12/06/1999 3416 2004

При этом внутренний запрос выбирает все данные для всех

студентов, получивших оценку 5. Оператор EXISTS во внешнем

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

сто, значит, это делает предикат верным. Подзапрос был выпол-

нен только один раз для всего внешнего запроса, и, следователь-

но, имеет одно значение во всех случаях - по этой причине

EXISTS делает предикат верным или неверным для всех строк

сразу. В последнем примере, если строго говорить, EXISTS дол-

жен быть установлен так, чтобы выбрать только один столбец.

Однако он выбирает все столбцы во вложенном предложении

SELECT.*. Это не вызывает ошибки, поскольку при выборе

EXISTS как одного столбца, так и всех столбцов, он просто заме-

чает выполнение вывода из подзапроса, а полученные значения

не использует.

Глава 2.2. Выборка, или чтение данных

177

В соотнесенном подзапросе, предложение EXISTS оценивает-

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

внешнем запросе, точно так же, как и другие операторы предика-

та, когда используется соотнесенный подзапрос. Это дает воз-

можность использовать EXISTS как предикат, который генериру-

ет различные значения для каждой записи таблицы, указанной в

основном запросе. Следовательно, информация из внутреннего

запроса будет сохраняться. Например, с помощью следующего

запроса выведем информацию о студентах, которые имеют не-

сколько оценок:

SELECT DISTINCT SNUM

FROM USP FIRST

WHERE EXISTS

(SELECT *

FROM USP SECOND

WHERE SECOND.SNUM = FIRST.SNUM

AND SECOND.PNUM < > FIRST. PNUM) ;

Вывод запроса следующий:

SNUM

3412

Здесь для каждой текущей строки внешнего запроса внут-

ренний запрос находит записи, которые совпадают со значени-

ем поля номера студента SNUM, но не совпадают со значени-

ем кода предмета PNUM. Если любые такие строки будут най-

дены подзапросом, это означает, что имеются, по крайней ме-

ре, две оценки, полученные текущим студентом. Если бы во

внешнем запросе DISTINCT не был указан, то каждый из сту-

дентов, имеющий несколько оценок, был бы выбран столько

раз. сколько у него оценок.

Для иллюстрации возможности использования комбинации из

EXISTS и объединения, усовершенствуем последний пример та-

ким образом, чтобы выводилась более подробная информация о

студентах:

SELECT DISTINCT FIRST.SNUM, FIRST.SFAM,

FIRST.SIMA, FIRST.SOTCH

178

Часть II, Структурированный язык запросов SQL

FROM STUDENTS FIRST, USP SECOND

WHERE EXISTS

(SELECT *

FROM USP THIRD

WHERE SECOND.SNUM = THIRD.SNUM

AND SECOND. PNUM < > THIRD. PNUM)

AND FIRST.SNUM = SECOND.SNUM;

В результате будет получено:

SNUM SFAM SIMA. SOTCH

3412 Поляков Анатолий Алексеевич

В этом примере внутренний запрос, как и в предыдущем ва-

рианте, сообщает, что студент получил несколько оценок. Внеш-

ний запрос - это объединение таблиц успеваемости и студентов.

Появившееся новое предложение основного предиката AND

FIRST.SNUM = SECOND.SNUM оценивается на том же самом

уровне, что и предложение EXISTS, т.к. это элемент предиката

самого объединения двух таблиц из внешнего запроса.

С учетом того, что EXISTS может работать в комбинации с

булевскими операторами, наиболее очевидным способом его ис-

пользования является сочетание с оператором NOT. Например,

для получения информации о студентах, имеющих только одну

оценку, можно воспользоваться следующим запросом:

SELECT DISTINCT FIRST.SNUM, FIRST.SFAM,

FIRST.SIMA, FIRST.SOTCH

FROM STUDENTS FIRST, USP SECOND

WHERE NOT EXISTS

(SELECT *

FROM USP THIRD

WHERE SECOND.SNUM = THIRD.SNUM

AND SECOND.PNUM < > THIRD. PNUM)

AND FIRST.SNUM = SECOND.SNUM;

Вывод этого запроса приведен ниже:

SNUM SFAM SIMA SOTCH

3413 Огарева Любовь Михайловна

Глава 2.2. Выборка, или чтение данных

179

3414 Гриценко Владимир Николаевич

3416 Нагорный Евгений Васильевич

Важным свойством EXISTS является то, что он не может

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

функция нашла какие-либо записи для операций с ними, то

EXISTS будет верен в любом случае. Выходом из такой ситуации

является использование вложенного подзапроса в подзапросе, в

предикате которого присутствует EXISTS. Это позволяет тща-

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

Возвращаясь к примеру с нахождением информации о студентах,

имеющих более одной оценки, можно предложить такой вариант

запроса:

SELECT *

FROM STUDENTS FIRST

WHERE EXISTS

(SELECT *

FROM USP SECOND

WHERE FIRST.SNUM

AND 1 <

(SELECT COUNT (*)

FROM USP

WHERE USP.SNUM

В результате будет получено:

SNUM SFAM SIMA

SECOND.SNUM

SECOND.SNUM));

SOTCH STIP

3412 Поляков Анатолий Алексеевич 25.50

Такая конструкция работает следующим образом: внешний

запрос выбирает из таблицы студентов STUDENTS текущую

строку и выполняет подзапросы. Для текущей строки средним

запросом берется в соответствие каждая строка из таблицы успе-

ваемости USP. Всякий раз, когда обнаруживается информация в

среднем запросе, который совпадает по номеру студенческого

билета с текущей строкой во внешнем запросе, SQL должен рас-

сматривать внутренний подзапрос для того, чтобы определить,

будет ли предикат среднего запроса верен. Внутренний запрос

считает количество оценок текущего студента, и, если это число