Операторы работы с множествами

Язык SQL включает три оператора работы с множествами, позволяющие осуществлять всевозможные операции над множествами. Кроме того, у каждого из этих операторов есть две разновидности: первая включает дублирующие данные, а вторая удаляет их (но необязательно все).

Оператор union

Операторы union (объединить) и union all (объединить все) позволяют комбинировать несколько таблиц. Разница в том, что если требуется объединить две таблицы, включая в окончательный результат все их строки, даже дублирующие значения, нужно использовать оператор union all. Благодаря оператору union all в конечной таблице всегда будет столько строк, сколько во всех исходных таблицах в сумме. Эта операция – самая простая из всех операций работы с множествами (с точки зрения сервера), поскольку серверу не приходится проверять перекрывающиеся данные. Следующий пример демонстрирует применение оператора:

Добавим к нашей базе Lessons таблицу Студенты:

CREATE TABLE IF NOT EXISTS `Lessons`.`students` (

`idstudent` INT NOT NULL,

`FIO` VARCHAR(100) NULL,

`DATA` DATE NULL,

PRIMARY KEY (`idstudent`))

Добавим данные:

INSERT INTO `lessons`.`students` (`idstudent`, `FIO`, `DATA`) VALUES ('1', 'Фандорин Эраст Петрович', '1999-10-12');

INSERT INTO `lessons`.`students` (`idstudent`, `FIO`, `DATA`) VALUES ('2', 'Куприн Александр Иванович', '1999-12-12');

INSERT INTO `lessons`.`students` (`idstudent`, `FIO`, `DATA`) VALUES ('3', 'Солдатов Петр Игоревич', '1999-08-08');

INSERT INTO `lessons`.`students` (`idstudent`, `FIO`, `DATA`) VALUES ('4', 'Степанов Глеб Иванович', '1999-01-13');

 

Теперь объединим таблицы teachers и students, предположим, нужен список всех посетителей техникума для выдачи пропусков.

Запрос:

SELECT FIO

FROM teachers

UNION

SELECT FIO

FROM students;

Предположим, что у нас есть студент 4-го курса, который читает лекции на первом курсе. Этот студент будет находится и в таблице teachers и в таблице students. Что тогда произойдет?

Добавим:

INSERT INTO `lessons`.`students` (`idstudent`, `FIO`, `DATA`) VALUES ('5', 'Чуркин Петр Иванович', '1991-01-13');

 

И посмотрим на результат объединения. Видим, что наш студент-преподаватель встречается в результате только один раз. Таким образом, оператор UNION автоматически исключает повторяющиеся записи. Если же нам надо получить все записи, используем оператор UNION ALL

SELECT FIO

FROM teachers

UNION ALL

SELECT FIO

FROM students;

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

SELECT FIO

FROM teachers

UNION ALL

SELECT FIO

FROM students

UNION ALL

SELECT title

FROM courses

Операции пересечения (INTERSECT) и операции разности (except) к сожалению MySQL не поддерживает.

Сортировка результатов запроса.

Если требуется отсортировать результат запроса, то это можно сделать в блоке ORDER BY команды SELECT. Этот блок добавляется в самом конце. Если у вас объединение двух запросов в один, то сортировать результат необходимо только в конце объединения:

SELECT FIO

FROM teachers

ORDER BY FIO;

Сортировка по убыванию desc, по возрастанию asc

SELECT FIO

FROM teachers

ORDER BY FIO desc;

Сортировка в объединении:

SELECT FIO

FROM teachers

UNION ALL

SELECT FIO

FROM students

ORDER BY FIO;

 

Подзапросы.

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

• Одной строки с одним столбцом

• Нескольких строк с одним столбцом

• Нескольких строк и столбцов

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

Примеры посмотрим на БД Поставки.

Таблица Поставки

ID	Дата	Город	Клиент	Вес	Объем	Водитель
	2016-10-28
	2016-10-31				1,3
	2016-10-31
	2016-10-31				2,5
	2016-10-31
	2016-10-29				3,3
	2016-10-30
	2016-10-28				0,2
	2016-10-30
	2016-10-29

 

Таблица Города Таблица Клиенты

ID	Наименование
	Москва
	Челябинск
	Копейск

ID	Наименование
	Авангард
	Барс
	Транс-сити

 

Таблица Водители

ID	Наименование	МаксимальныйВес
	Иванов
	Петров
	Сидоров
	Лосев
	Чуркин

МаксимальныйВес – это максимальный вес, который позволяет возить автомобиль этого водителя.

 

 

1. Подзапрос может быть размещен в блоке WHERE:

Предположим мы знаем водителя, но не знаем его ID, тогда мы можем получить информацию о поставках с помощью подзапроса:

SELECT * FROM ПоставкиWHERE Водитель = (SELECT ID FROM Водители WHERE Водители.Наименование = 'Сидоров');

2. Подзапрос с агрегатными функциями:

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

SELECT * FROM ПоставкиWHERE Поставки.Объем >= (SELECT AVG(Поставки.Объем) FROM Поставки);

Если возникают какие-нибудь вопросы по поводу того, что делает подзапрос, можно выполнить его отдельно (без скобок) и посмотреть, что он возвращает.

3. Использование оператора IN в подзапросе. Что если подзапрос возвращает несколько строк в результате?

Оператор IN определяет допустимый набор значений.

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

SELECT * FROM ПоставкиWHERE Водитель IN (SELECT ID FROM Водители WHERE Водители.МаксимальныйВес >= 150);

4. Подзапрос можно поместить в блок HAVING

5. Подзапрос в предложении SELECT

SELECT Водители.Наименование, (SELECT AVG(Поставки.Вес) FROM Поставки) FROM Водители;

 

6. Соотнесенные подзапросы.

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

SELECT Водители.Наименование FROM Водители
WHERE Водители.id in (SELECT Поставки.Водитель FROM Поставки
WHERE Водители.id = Поставки.Водитель);

 

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

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

Например рассмотрим отношения Отделы(id, наименование) и Сотрудники (id, ФИО, ДатаРождения, id_Отдел). Как получить количество сотрудников для каждого отдела?

SELECT Отделы.id, Отделы.Наименование, e_cnt.how_many

FROM Отделы INNER JOIN

(SELECT id_Отдел, COUNT(*) how_many

FROM Сотрудники

GROUP BY id_Отдел) e_cnt

ON Отделы.id = e_cnt.id_Отдел;