Объединения

1.

Объединения
в SQL
Как комбинировать данные из разных запросов и таблиц: EXISTS, ANY и ALL для
подзапросов, вертикальное объединение результатов через UNION и
горизонтальные соединения таблиц через JOIN.

2.

Где мы сейчас
Что уже пройдено в прошлых презентациях — и зачем это нам сегодня.
ПРОЙДЕНО В ПРЕДЫДУЩИХ
ПРЕЗЕНТАЦИЯХ
1
«Проектирование и связи»
2
«Многотабличный SELECT»
3
«Агрегаты и группы»
4
«Подзапросы»
5
«Ловушки»
CollegeDB из шести таблиц в 3НФ, FOREIGN KEY, типы связей и шесть ограничений
целостности
соединение через WHERE и современный INNER JOIN … ON, цепочка Students →
Grades → Subjects, ловушка декартова произведения
COUNT · AVG · SUM · MIN · MAX, GROUP BY и HAVING: «сколько, среднее, максимум»
скалярные через =, множественные через IN, в FROM с псевдонимом, коррелированные
Msg 512 при «скаляр = список», пустой ответ NOT IN со NULL, декартово произведение
без условия
ВОПРОСЫ БАЗЕ ДАННЫХ
Мы умеем сравнивать столбец со
значением (=) и со списком (IN). А если
вопрос другой: есть ли хоть одна строка?
Верно ли для всех? Как склеить
результаты двух независимых запросов?
Сегодня отвечаем на все три — новыми
операторами и объединениями.

3.

EXISTS: есть ли хоть одна строка
Оператор, который проверяет строки, а не сравнивает значения столбцов.
EXISTS возвращает истину, если подзапрос содержит хотя бы одну строку, и ложь — если ни одной. Возвращаемые столбцы не имеют
значения — проверяется сам факт существования.
EXISTS.sql
SELECT FirstName, LastName, BirthDate, Email
FROM Students
WHERE EXISTS (SELECT * FROM Grades WHERE
Grades.StudentID = Students.StudentID);
Lastname
Firstname
Birthdate
Email
Волкова
Елена
2002-08-14
ev@net.eu
Козлов
Михаил
2002-03-17
mk@net.eu
Смирнова
Анна
2003-02-19
as@net.eu
… ещё 4 строки
1
Коррелированный подзапрос
подзапрос связан с внешним по ключу Grades.StudentID =
Students.Id и выполняется для каждой строки Students
2
SELECT * внутри EXISTS
традиционная запись: столбцы не влияют на результат,
важны только строки
С оценками — 7 студентов. Поменяем EXISTS на NOT EXISTS — получим остальных.

4.

NOT EXISTS: студенты без оценок
Тот же коррелированный подзапрос, но с отрицанием — и другой вопрос к базе.
NOT_EXISTS.s ql
SELECT FirstName, LastName, BirthDate, Email
FROM Students
WHERE NOT EXISTS (SELECT * FROM Grades WHERE
Grades.StudentID = Students.StudentID);
NOT EXISTS безопаснее NOT IN
если подзапрос вернёт список со значением NULL, NOT IN даст
пустой ответ — ловушка из прошлого урока (§12); NOT EXISTS
проверяет существование строк, и NULL в списке ему не
страшен.
Lastname
Firstname
Birthdate
Email
Панов
Игорь
2002-04-12
ip@net.eu
Жуков
Павел
2002-01-27
pz@net.eu
Это анти-соединение
вопрос «кого нет в связанной таблице» встречается постоянно —
клиенты без заказов, студенты без оценок, предметы без
расписания; NOT EXISTS отвечает на него без ошибок.
Тот же ответ мы получим через LEFT JOIN … IS NULL.

5.

ANY/SOME: хотя бы с одним
Синонимы: оба проверяют условие хотя бы для одного значения из подзапроса.
НЕ РАВ Е НС ТВ О — ТО, Ч Е Г О IN
НЕ УМЕЕТ
РАВ Е НС ТВ О = ANY — К АК IN
=ANY.sql
SELECT FirstName, LastName
FROM Students
WHERE StudentID = ANY (SELECT StudentID FROM Grades
WHERE Grade = 5);
Lastname
Firstname
Волкова
Елена
Смирнова
Анна
Белова
Мария
Id = ANY (список) даёт тот же ответ, что Id IN (список).
<ANY.sql
SELECT COUNT(*) AS [Count]
FROM Students
WHERE BirthDate < ANY
(SELECT BirthDate FROM Teachers);
8 [Count]
(1 row affected)
Студент старше хотя бы одного преподавателя: дата рождения меньше
самой молодой — 2003-09-01. Все, кроме Беловой.
Оператор сравнения ставится между значением и ANY (= · <> · < · <= · > · >=); ANY не ограничен равенством — в этом его
преимущество перед IN.

6.

ALL: для всех без исключения
Условие должно выполняться для каждого значения, которое вернул подзапрос.
ДАТЫ: МЛАДШЕ ВСЕХ ПРЕПОДАВАТЕЛЕЙ
>ALL_dates.sql
SELECT FirstName, LastName, BirthDate
FROM Students
WHERE BirthDate > ALL
(SELECT BirthDate FROM Teachers);
Lastname
Firstname
Birthdate
Белова
Мария
2003-09-21
Больше каждой из трёх дат преподавателей — значит,
позже самого молодого (2003-09-01).
ОЦЕНКИ: ВЫШЕ ВСЕХ СРЕДНИХ
>ALL_avg.sql
SELECT FirstName, LastName, Grade
FROM Students AS S, Grades AS G
WHERE G.StudentID = S.StudentID
AND Grade > ALL (SELECT AVG(Grade)
FROM Grades
GROUP BY StudentID);
BirthDate < ANY — раньше ХОТЯ БЫ ОДНОГО (= раньше самого старшего);
BirthDate < ALL — раньше КАЖДОГО (= раньше самого пожилого).
Lastname
Firstname
Grade
Волкова
Елена
5
Смирнова
Анна
5
Белова
Мария
5
Средние по студентам: 3.0 … 4.5. Пятёрка выше всех этих средних.
ANY/ALL работают и с HAVING — например, HAVING Grade <> ALL (…):
оценки, не совпадающие ни с одной из оценок группы.

7.

П р и н ц и п ы о б ъ е д и не н и я
Что должно совпадать, чтобы два набора строк стали одним.
Объединять можно результаты любого числа запросов, которые выполняются независимо друг от друга — но только при условии совместимости.
1
Одинаковое число столбцов
в каждом запросе список SELECT должен возвращать одно
и то же количество столбцов
2
Совместимые типы
типы данных соответствующих столбцов должны быть
совместимы: число с числом, строка со строкой, дата с датой
3
Имена — из первого запроса
в результирующем наборе используются имена столбцов,
указанные в первом SELECT
4
ORDER BY — только в конце
сортировать каждый запрос по отдельности нельзя: один
ORDER BY ставится после всего составного запроса
ОБЩАЯ ФОРМА СОСТАВНОГО ЗАПРОСА
ФОРМА
SELECT columnName1, columnName2, ...
FROM tableName
UNION [ALL]
SELECT columnName1, columnName2, ...
FROM tableName;

8.

UNION: летние дни рождения
Студенты и преподаватели, родившиеся летом, — одним отсортированным списком.
UNION.sql
SELECT FirstName + ' ' + LastName AS FullName, BirthDate
FROM Students
WHERE MONTH(BirthDate) > 5 AND MONTH(BirthDate) < 9
UNION
SELECT FirstName + ' ' + LastName, BirthDate
FROM Teachers
WHERE MONTH(BirthDate) > 5 AND MONTH(BirthDate) < 9
ORDER BY BirthDate;
Два независимых запроса
1 студенты и преподаватели, родившиеся в месяцы
6–8, считаются отдельно друг от друга
склеивает и чистит
2 UNION
наборы складываются по вертикали,
повторяющиеся строки исключаются
РЕЗУЛЬТАТ ЗАПРОСА
FullName
BirthDate
Кузнецова Ирина
1975-07-03
Смирнов Алексей
1980-06-12
Волкова Елена
2002-08-14
Рябова Ольга
2003-06-30
3
ORDER BY — общий
сортировка применяется ко всему объединённому
набору, а не к отдельным запросам

9.

UNION ALL: отчёт по временам года
Четыре независимых подсчёта — один сводный отчёт, в котором важен порядок строк.
SEASONS.sql
SELECT 'Весна' AS Season, COUNT(*) AS [Students]
FROM Students WHERE MONTH(BirthDate) BETWEEN
3 AND 5
UNION ALL
SELECT 'Лето', COUNT(*) FROM Students
WHERE MONTH(BirthDate) BETWEEN 6 AND 8
UNION ALL
SELECT 'Осень', COUNT(*) FROM Students
WHERE MONTH(BirthDate) BETWEEN 9 AND 11
UNION ALL
SELECT 'Зима', COUNT(*) FROM Students
WHERE MONTH(BirthDate) IN (1, 2, 12);
РЕЗУЛЬТАТ ЗАПРОСА
Season
Students
Весна
3
Лето
2
Осень
2
Зима
2
Почему UNION ALL
не тратит время на удаление дубликатов и выполняется
быстрее UNION
Почему важен порядок
UNION ALL сохраняет порядок блоков: Весна → Лето →
Осень → Зима; UNION такой гарантии не даёт
Литерал + агрегат
строковый литерал 'Весна' в первом столбце работает,
потому что типы совместимы
Обернув UNION ALL в подзапрос FROM ( … ) AS AllSum, можно добавить итоговую строку SUM(AllSum.Students) — приём сводного отчёта с
общим количеством.

10.

От SQL-89 к SQL-92
Мы соединяли таблицы через WHERE — пора познакомиться со вторым стандартным синтаксисом.
ANSI SQL -92 — через JOIN
ANSI SQL-89 — через WHERE
SELECT LastName, FirstName, GroupName
FROM Groups, Students
WHERE Groups.GroupID = Students.GroupId;
→ использовали до сих пор — соединение через запятую и
фильтр
1
Читабельность
условие соединения после ON отделено от
фактической фильтрации WHERE: в
сложных запросах сразу видно, что чем
связано
SELECT LastName, FirstName, GroupName
FROM Groups INNER JOIN Students
ON Groups.GroupID = Students.GroupId;
→
2
→ условие соединения — после ON
Внешние объединения
именно стандарт SQL-92 ввёл поддержку
LEFT/RIGHT/FULL — через WHERE их
не выразить
UNION — объединение по вертикали (строки под строками).
3
Оба стандарта живы
SQL-89 и SQL-92 повсеместно встречаются
в реальном коде, читать нужно оба, писать
рекомендуем JOIN
JOIN — объединение по горизонтали (столбцы рядом).

11.

INNER JOIN на CollegeDB
Внутреннее объединение: каждая запись первой таблицы сопоставляется со второй по условию после ON.
УЧЕБНЫЙ ПРОСМОТР — SELECT *
SELECT *
FROM Groups INNER JOIN Students
ON Groups.GroupID = Students.GroupId;
РЕЗУЛЬТАТ ЗАПРОСА
LastName
FirstName
Email
GroupName
Волкова
Елена
ev@net.eu
ИС-301
Козлов
Михаил
mk@net.eu
ИС-301
Смирнова
Анна
as@net.eu
ИС-301
… ещё 6 строк
→ 9 строк, но столбцы задвоены — Id, GroupId и GroupName рядом с полным
набором Students; для работы так не пишут
РАБОЧИЙ ВАРИАНТ — ТОЛЬКО НУЖНЫЕ СТОЛБЦЫ
SELECT LastName, FirstName, Email, GroupName
FROM Groups INNER JOIN Students
ON Groups.GroupID = Students.GroupId;
УСЛОВИЕ ПОСЛЕ ON
Groups.Id = Students.GroupId
условие после ON — то же самое равенство ключей, что
мы писали в WHERE
Слово INNER можно опустить — просто JOIN расценивается как внутреннее объединение.

12.

Цепочка из четырёх таблиц
Успеваемость всех студентов: группы, студенты, оценки, предметы — в одном запросе.
Groups
1
∞
Id · GroupName
1
Students
∞
Id · FirstName · LastName
∞
Grades
1
Subjects
Id · Name
StudentID · SubjectID · Grade
связи по ключам: Students.GroupId → Groups.Id · Grades.StudentID → Students.Id · Grades.SubjectID → Subjects.Id
T-SQL
SELECT FirstName, LastName, SubjectName AS Subject,
Grade, GroupName
FROM Groups AS G JOIN Students AS S
ON G.GroupID = S.GroupId
JOIN Grades AS Gd ON S.StudentID = Gd.StudentID
JOIN Subjects AS Sb ON Sb.SubjectID = Gd.SubjectID
WHERE Sb.SubjectName = 'Базы данных'
ORDER BY LastName;
ПСЕВДОНИМЫ В ЦЕПОЧКЕ
G → Groups
S → Students
Gd → Grades
Sb → Subjects
у каждого JOIN — своё условие ON со своей парой
ключей
РЕЗУЛЬТАТ ЗАПРОСА
LastName
FirstName
Subject
Grade
GroupName
Волкова
Елена
SQL Server
5
ИС-301
Козлов
Михаил
SQL Server
4
ИС-301
Смирнова
Анна
SQL Server
5
ИС-301
Новиков
Дмитрий
SQL Server
3
ИС-302
Белова
Мария
SQL Server
4
ИС-303
Лебедев
Сергей
SQL Server
3
ИС-304
ФОРМУЛА ИЗ § 08 РАБОТАЕТ
4 таблицы → 3 JOIN (N − 1)
WHERE и ORDER BY применяются к результату так же,
как раньше.

13.

З а ч е м н у ж ны в н е ш н и е о б ъ е д и н е н и я
Задача: вывести всех студентов и их оценки. INNER JOIN теряет часть ответа.
Что вернул INNER JOIN
INNER JOIN
3 из 5
С Т Р О К В Е Р Н УЛ IN N E R J O IN
SELECT FirstName + ' ' + LastName AS FullName, Grade
FROM Students AS S INNER JOIN Grades AS G
ON S.StudentID = G.StudentID;
3 строки — и это неполный ответ.
−2
Панов и Жуков потерялись: у них нет ни одной оценки,
поэтому пары с Grades не существует.
INNER JOIN помещает в результат только строки, удовлетворяющие условию после ON. Студент без оценок пары не образует — и выпадает из
ответа, хотя вопрос требовал ВСЕХ студентов.
ОБЩАЯ ФОРМА
РЕШЕНИЕ — ВНЕШНИЕ ОБЪЕДИНЕНИЯ
Ключ OUTER можно опустить — LEFT, RIGHT и FULL сами определяют тип
объединения.
SELECT columnName1, columnName2, ...
FROM leftTable LEFT | RIGHT | FULL [OUTER]
JOIN rightTable
ON tableName1.columnName = tableName2.columnName;

14.

LEFT JOIN и трюк IS NULL
Левое внешнее объединение сохраняет ВСЕ строки левой таблицы — даже без пары.
left_join.sql
ТРЮК
SELECT S.FirstName + ' ' + S.LastName AS FullName, G.Grade
FROM Students AS S
LEFT JOIN Grades AS G ON S.StudentID = G.StudentID;
WHERE Grade IS NULL
Отфильтровав NULL, получаем студентов без единой
оценки — Панов и Жуков. Тот же ответ, что NOT
EXISTS, но без подзапроса.
РЕЗУЛЬТАТ ЗАПРОСА — ФРАГМЕНТ
FullName
Grade
Волкова Елена
5
Козлов Михаил
4
Козлов Михаил
3
… ещё 7 пар строк
Панов Игорь
NULL
Жуков Павел
NULL
БОНУС
Почему это удобно
Синтаксис понятнее вложенного запроса, а выполняется
часто эффективнее — каждый JOIN обрабатывается
самостоятельным потоком, тогда как подзапрос
выполняется последовательно с основным запросом.

15.

RIGHT JOIN и комбинация с LEFT
Правое внешнее объединение — зеркало левого: всё сохраняется справа.
ЗЕРКАЛО: ТАБЛИЦЫ ПОМЕНЯНЫ МЕСТАМИ
К ОМ БИНАЦИЯ R IGHT + LE FT — В С Е ПРЕ Д М Е ТЫ И ПРЕ ПОД АВ АТЕ ЛИ
RIGHT JOIN
SELECT S.FirstName + ' ' + S.LastName AS
FullName, G.Grade
FROM Grades AS G
RIGHT JOIN Students AS S ON S.StudentID =
G.StudentID;
RIGHT + LEFT
SELECT Sb.SubjectName AS Subject,
T.LastName, T.FirstName
FROM Schedule AS Sc
RIGHT JOIN Subjects AS Sb ON Sb.SubjectID = Sc.SubjectID
LEFT JOIN Teachers AS T ON T.TeacherID = Sc.TeacherID
ORDER BY Sb.SubjectName;
РЕЗУЛЬТАТ ЗАПРОСА
Результат идентичен LEFT JOIN со слайда 18 — те же 13 строк.
Вывод: RIGHT JOIN (A, B) = LEFT JOIN (B, A).
1 RIGHT JOIN сохранил ВСЕ предметы — даже
Физкультуру, которой нет в расписании
[Subject]
LastName
FirstName
Матанализ
Кузнецова
Ирина
SQL Server
Смирнов
Алексей
Физкультура
NULL
NULL
2 LEFT JOIN затем приклеил преподавателей,
не потеряв результат первого объединения
3 порядок JOIN в цепочке имеет значение

16.

FULL JOIN: всё из обеих таблиц
Полное внешнее объединение — комбинация левого и правого.
FULL JOIN
SELECT FirstName, LastName, GroupName
FROM Students AS S FULL JOIN Groups AS G
ON G.GroupID = S.GroupId
ORDER BY FirstName;
1
Комбинация LEFT + RIGHT
в результат включаются строки из левой таблицы
даже без пары справа и строки из правой таблицы
даже без пары слева; недостающая сторона
дополняется NULL
РЕЗУЛЬТАТ ЗАПРОСА — ФРАГМЕНТ
FirstName
LastName
GroupName
Белова
Мария
ИС-303
Волкова
Елена
ИС-301
… ещё 6 строк
NULL
NULL
ИС-401
2
Наша база подросла
с прошлой презентации появилась группа ИС401: студентов в ней пока нет, и именно FULL
JOIN показывает это одной строкой; LEFT JOIN
её бы потерял

17.

Ш п а р г а л ка : ч е т ы р е J O I N
Что попадает в результат при каждом виде объединения — на одном фрагменте CollegeDB.
INNER JOIN
LEFT JOIN
только пары, совпавшие по ON
все строки левой таблицы + пары, NULL вместо отсутствующих
СТ УДЕНТ Ы
ОЦЕНКИ
СТ УДЕНТ Ы
ОЦЕНКИ
П Р И М Е Р 11 строк: студенты с оценками
П Р И М Е Р 13 строк: + Панов и Жуков с NULL
RIGHT JOIN
FULL JOIN
все строки правой таблицы + пары
всё из обеих таблиц, NULL на пустых местах
СТ УДЕНТ Ы
ОЦЕНКИ
П Р И М Е Р зеркало LEFT: RIGHT JOIN (A, B) = LEFT JOIN (B, A)
СТ УДЕНТ Ы
ОЦЕНКИ
П Р И М Е Р 10 строк: студенты + группа ИС-401
OUTER можно не писать: LEFT = LEFT OUTER JOIN. А чтобы найти строки без пары — LEFT JOIN … WHERE … IS NULL.

18.

Типичные ошибки при объединениях
Пять ситуаций, на которых спотыкаются чаще всего.
1
2
3
ORDER BY в каждом блоке
Столбцы не совпадают
NOT IN со NULL
Нельзя сортировать отдельные запросы
UNION: сортировка одна, на весь
составной запрос.
Разное число или несовместимые типы
столбцов — объединение невозможно.
Если подзапрос вернул NULL, NOT IN
даёт пустой результат — ловушка §12.
✓ ИСПР АВ Л Е НИЕ
✓ ИСПР АВ Л Е НИЕ
Выровнять списки SELECT: одинаковое
число столбцов и совместимые типы
Заменить на NOT EXISTS или LEFT
JOIN … IS NULL
✓ И СП Р АВ Л Е Н И Е
Перенести ORDER BY в самый конец,
после последнего SELECT
4
5
JOIN без условия ON
= против множества
Забытый ON (или WHERE в старом синтаксисе) даёт
декартово произведение: в §08 это было 30 × 8 = 240 строк.
Скалярное равенство с подзапросом из нескольких строк: Msg
512, Subquery returned more than 1 value.
✓ И СП Р АВ Л Е Н И Е
Каждому JOIN — своё условие ON по ключам
✓ И СП Р АВ Л Е Н И Е
Использовать IN, ANY или EXISTS

19.

П р а к т и ч ес ко е з а д а н и е
Три раздела — операторы подзапросов, вертикальное и горизонтальное объединения.
ИТОГИ ЛЕКЦИИ
1
2
EXISTS / NOT EXISTS
▤ Практическое задание — БД АЭРОПОРТА
проверка существования строк: коррелированный подзапрос, SELECT *,
безопасен там, где NOT IN боится NULL
1
создать многотабличную базу данных Airport: рейсы самолётов,
билеты на рейсы (бизнес и эконом класс), пассажиры — со связями по
внешним ключам
ANY / ALL
2
написать запросы:
сравнение «хотя бы с одним» и «со всеми»; ANY = SOME; = ANY работает как
IN, а неравенства — только с ANY/ALL
✓ рейсы в заданный город на дату по времени вылета
✓ рейс с наибольшей длительностью полёта
3
4
5
UNION / UNION ALL
✓ рейсы длительностью больше двух часов
вертикальное объединение независимых запросов: совместимость столбцов, имена
из первого запроса; ALL быстрее и сохраняет порядок блоков
✓ количество рейсов в каждый город
JOIN … ON
✓ количество рейсов по городам и общее число за месяц (UNION ALL!)
горизонтальное объединение, стандарт SQL-92: условие соединения отделено от
фильтрации
INNER / LEFT / RIGHT / FULL
пары, всё слева, всё справа, всё из обеих; LEFT + IS NULL находит строки без
пары
✓ город, куда летают чаще всего (подзапрос!)
✓ рейсы на сегодня со свободными местами бизнес-класса
✓ проданные за день билеты и их сумма
✓ все рейсы и число проданных на них билетов (LEFT JOIN!)
✓ номера рейсов и города вылета
Критерий: в решении должны появиться подзапросы, UNION ALL и разные виды JOIN; каждую строку результата — проверить руками на своих
данных.