Теоретико-множественные операции

В языке SQL можно использовать обычные операции над множествами — объединение, пересечение и вычитание, позволяющие комбинировать результаты выполнения двух и более запросов в единую результирующую таблицу. На таблицы, которые могут комбинироваться с помощью теоретико-множественных операций, накладываются определенные ограничения, упоминавшиеся ранее. В частности, таблицы-операнды должны быть совместимы по типу, т. е. иметь одинаковую структуру. Контроль за соблюдением этого требования возлагается на разработчика запросов.

Запрос на объединение UNION

Операция UNION создает запрос на объединение, который объединяет результаты нескольких независимых запросов или таблиц. В одной операции UNION можно объединить результаты нескольких запросов, таблиц и инструкций SELECT. Наиболее общий формат команды имеет следующий вид:

[TABLE] <запрос1> UNION [ALL] [TABLE] <запрос2>;

Здесь <3anpocN> — инструкция SELECT, имя сохраненного запроса или имя сохраненной таблицы, перед которым стоит зарезервированное слово TABLE.

Все запросы, включенные в операцию UNION, должны отбирать одинаковое число идентичных столбцов; при этом операнды должны быть соразмерны.

Несколькими операциями UNION можно объединить любое число предложений SELECT.

Пример 70. Вывести из таблицы «Сотрудник» номера («таб_ номер»), фамилии, должности и шифры кафедр сотрудников, которые работают на заданной кафедре (например, с шифром «пи») или/и работают на заданной должности (например, «инженер»).

SELECT таб_номер, фамилия, должность, шифр FROM Сотрудник

WHERE шифр= [Введите шифр кафедры]

UNION

SELECT таб_номер, фамилия, должность, шифр FROM Сотрудник

WHERE должность = [Введите должность];

Результат выполнения запроса из примера 70

таб_номер

фамилия

должность

шифр

101

Прохоров П. П.

зав.кафедрой

пи

102

Семенов С. С.

преподаватель

пи

105

Петров П. П.

преподаватель

пи

153

Сидорова С. С.

инженер

пи

241

Глухов Г. Г.

инженер

ис

таб_номер

фамилия

должность

шифр

242

Чернов Ч.Ч.

инженер

ис

435

Лисин Л. Л.

инженер

оф

614

Григорьев Г. Г.

инженер

эф

В этом примере одно множество представлено сотрудниками заданной кафедры («Прикладная информатика» с шифром «пи»), а второе — сотрудниками заданной должности — «инженер». Как видно, в соответствии с определением операции объединения в выборке представлены или сотрудники заданной кафедры, или сотрудники заданной должности, или сотрудники, удовлетворяющие обоим условиям.

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

Пример 71. Вывести из таблицы «Сотрудник» номера («таб_ номер»), фамилии и должности сотрудников, которые работают на заданной кафедре (например, с шифром «пи») или/и являются инженерами (таблица «Инженер») по заданной специальности (например, «программист»).

SELECT Сотрудник.таб_номер, фамилия, должность, шифр, специальность

FROM Сотрудник LEFT JOIN Инженер ON Инженер.таб_номер = Сотрудник.таб_номер

WHERE шифр = [Введите шифр кафедры]

UNION SELECT Сотрудник.таб_номер, фамилия, должность, шифр, специальность

FROM Сотрудник LEFT JOIN Инженер ON Инженер.таб_номер = Сотрудник.таб_номер

WHERE специальность = [Введите специальность];

Результат выполнения запроса из примера 71

таб_номер

фамилия

должность

шифр

специальность

101

Прохоров П. П.

зав.кафедрой

пи

102

Семенов С. С.

преподаватель

пи

105

Петров П.П.

преподаватель

пи

153

Сидорова С. С.

инженер

пи

электроник

таб_номер

фамилия

должность

шифр

специальность

242

Чернов Ч.Ч.

инженер

ис

программист

614

Григорьев Г. Г.

инженер

эф

программист

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

Задание. Выполнить запрос на объединение (на основе команды UNION): вывести данные о дисциплинах (таблица «Дисциплина»), для которых исполнителем является кафедра, заданная ее шифром, или/и о дисциплинах, объем которых превышает заданную величину.

Запрос на пересечение

Не все диалекты SQL (в том числе SQL Microsoft Jet) поддерживают непосредственно операции пересечения и вычитания. Специальной инструкции для выполнения пересечения в рассматриваемой версии SQL нет. Но операция может быть выполнена на основе других инструкций. Запрос на пересечение может быть выполнен несколькими способами. В наиболее распространенном способе используется предикат — квантор EXISTS.

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

Пример 72. Вывести из таблицы «Сотрудник» номера («таб_ номер»), фамилии, должности и шифры кафедр сотрудников, которые работают на заданной кафедре (например, с шифром «пи») и при этом работают на заданной должности (например, «инженер»).

SELECT таб_номер, фамилия, должность, шифр

FROM Сотрудник

WHERE шифр = [Введите шифр кафедры] AND

EXISTS (SELECT * FROM Сотрудник Сотрудник1

WHERE Сотрудник.таб_номер = Сотрудник1.таб_номер

AND должность = [Введите должность]);

Результат выполнения запроса из примера 72

таб_номер

фамилия

должность

шифр

153

Сидорова С. С.

инженер

пи

Подзапрос на основе EXISTS всегда является коррелированным. Если объемлющий запрос и подзапрос обращаются к одной и той же таблице (например, «Сотрудник», как в нашем случае), то для отличия этих ее разных состояний в подчиненном запросе следует использовать псевдоним таблицы (например, «Сотрудник!.») для ссылки на ту же таблицу, указанную в объемлющем запросе по условию связи Сотрудник.таб_номер = Сотрудник!..таб_номер.

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

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

Пример 73. Выполнить запрос на пересечение: вывести из таблицы «Сотрудник» табельные номера («таб_номер»), фамилии, должности, шифры кафедр и специальности сотрудников, которые работают на заданной кафедре (например, с шифром «пи») и являются инженерами (таблица «Инженер») по заданной специальности (например, «электроник»).

SELECT Сотрудник.таб_номер, фамилия, должность, шифр, специальность

FROM Сотрудник INNER JOIN Инженер

ON Инженер.таб_номер = Сотрудник.таб_номер

WHERE шифр= [Введите шифр кафедры] AND EXISTS (SELECT * FROM Инженер

WHERE Инженер.таб_номер = Сотрудник.таб_номер AND специальность = [Введите специальность]);

Результат выполнения запроса из примера 73

таб_номер

фамилия

должность

шифр

специальность

153

Сидорова С. С.

инженер

пи

электроник

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

Задание. Выполнить запрос на пересечение (на основе предиката EXISTS): вывести данные о дисциплинах (таблица «Дисциплина»), для которых заявителем является кафедра, заданная ее шифром и при этом объем которых превышает заданную величину.

Запрос на вычитание

Специальной инструкции для выполнения разности в рассматриваемой версии SQL нет. Но операция может быть выполнена на основе других инструкций. Запрос на вычитание может быть выполнен несколькими способами. Наиболее распространенный использует предикат — квантор NOT EXISTS.

Рассмотрим пример операции вычитания, определенной на множестве сотрудников данной кафедры и множестве сотрудников данной должности.

Пример 74. Выполнить запрос на вычитание: вывести из таблицы «Сотрудник» номера («таб_номер»), фамилии, должности и шифры кафедр сотрудников, которые работают на заданной кафедре (например, с шифром «пи»), но не являются сотрудниками заданной должности (например, «инженер»).

SELECT таб_номер, фамилия, должность, шифр

FROM Сотрудник

WHERE шифр = [Введите шифр кафедры] AND

NOT EXISTS (SELECT * FROM Сотрудник Сотрудник1 WHERE Сотрудник.таб_номер = Сотрудник 1.таб_номер AND должность = [Введите должность]);

Результат выполнения запроса из примера 74

таб_номер

фамилия

должность

шифр

101

Прохоров П.П.

зав.кафедрой

пи

102

Семенов С. С.

преподаватель

пи

105

Петров П. П.

преподаватель

пи

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

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

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

Пример 75. Выполнить запрос на вычитание: вывести из таблицы «Сотрудник» номера («таб_номер»), фамилии, должности, шифры кафедр и специальности сотрудников-инженеров, которые работают на заданной кафедре (например, с шифром «пи»), но не являются инженерами (таблица «Инженер») по заданной специальности (например, «программист»).

SELECT Сотрудник.таб_номер, фамилия, должность, шифр, специальность

FROM Сотрудник INNER JOIN Инженер

ON Инженер.таб_номер=Сотрудник.таб_номер

WHERE шифр= [Введите шифр кафедры] AND NOT EXISTS (SELECT * FROM Инженер WHERE Инженер.таб_номер = Сотрудник.таб_номер

AND специальность = [Введите специальность]);

Результат выполнения запроса из примера 75

таб_номер

фамилия

должность

шифр

специальность

153

Сидорова С. С.

инженер

пи

электроник

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

Задание. Выполнить запрос на вычитание (на основе предиката NOT EXISTS): вывести данные о дисциплинах (таблица «Дисциплина»), для которых исполнителем является кафедра, заданная ее шифром, но при этом объем которых не превышает заданную величину.

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

Пример 76. Выполнить запрос на вычитание: вывести из таблицы «Сотрудник» номера («таб_номер»), фамилии, должности и шифры кафедр сотрудников, которые являются сотрудниками заданной должности (например, «инженер»), но не работают на заданной кафедре (например, с шифром «пи»).

SELECT таб_номер, фамилия, должность, шифр

FROM Сотрудник

WHERE должность = [Введите должность] AND

NOT EXISTS (SELECT * FROM Сотрудник Сотрудник!.

WHERE Сотрудник.таб_номер = Сотрудник!..таб_номер AND шифр = [Введите шифр кафедры]);

Результат выполнения запроса из примера 76

таб_номер

фамилия

должность

шифр

241

Глухов Г. Г.

инженер

ис

242

Чернов Ч.Ч.

инженер

ис

435

Лисин Л. Л.

инженер

оф

614

Григорьев Г. Г.

инженер

эф

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

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

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

Пример 7 7. Выполнить запрос на вычитание: вывести из таблиц «Сотрудник» и «Инженер» номера («таб_номер»), фамилии, должности, шифры кафедр и специальности сотрудников-инжене- ров, которые являются инженерами по заданной специальности (например, «программист»), но не работают на заданной кафедре (например, с шифром «пи»),

SELECT Сотрудник.таб_номер, фамилия, должность, шифр, специальность

FROM Сотрудник INNER JOIN Инженер ON Инженер.таб_номер = Сотрудник.таб_номер

WHERE специальность = [Введите специальность] AND NOT EXISTS (SELECT * FROM Сотрудник

WHERE Инженер.таб_номер = Сотрудник.таб_номер AND шифр= [Введите шифр кафедры]);

Результат выполнения запроса из примера 77

таб_номер

фамилия

должность

шифр

специальность

614

Григорьев Г. Г.

инженер

эф

программист

242

Чернов Ч.Ч.

инженер

ис

программист

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

Задание. Выполнить запрос на вычитание (на основе предиката NOT EXISTS): вывести данные о дисциплинах (таблица «Дисциплина»), объем которых превышает заданную величину, но при этом для которых заявителем не является кафедра, заданная ее шифром.

 
Посмотреть оригинал
< Пред   СОДЕРЖАНИЕ   ОРИГИНАЛ   След >