Связи таблиц и агрегация: JOIN, GROUP BY, HAVING

Связи таблиц и агрегация

До этого мы работали с одной таблицей. Но настоящая база данных состоит из множества таблиц, связанных друг с другом. Студенты — в одной таблице, группы — в другой, оценки — в третьей. И самое интересное начинается, когда мы хотим получить данные из нескольких таблиц сразу.

🔗 JOIN 📊 COUNT ➕ SUM 📈 AVG 📦 GROUP BY 🔍 HAVING

                     Сегодня мы научимся соединять таблицы через JOIN, считать статистику (COUNT, SUM, AVG, MIN, MAX), группировать данные через GROUP BY и фильтровать группы через HAVING. Это основа любых отчётов и аналитики.
                

Формат: лекция + практика в конце. Следующие материалы — практика по созданию базы «Библиотека» и подключение Java к MS SQL Server через JDBC.

План лекции

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

1 Зачем нужны связи между таблицами
2 Что такое JOIN и как он работает
3 INNER JOIN — только совпадения
4 LEFT JOIN и RIGHT JOIN — с сохранением «сирот»
5 FULL JOIN — объединение с двух сторон
6 Сравнение всех JOIN
7 Агрегатные функции: COUNT, SUM, AVG, MIN, MAX
8 GROUP BY — группировка данных
9 HAVING — фильтрация групп
10 Порядок выполнения запроса
11 Типичные ошибки
12 Практика и домашнее задание

Если вы пропустили Лекции 1 и 2 — вернитесь. Без SELECT и внешних ключей эта тема будет непонятна.

Зачем нужны связи между таблицами

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

IdNameGroupName 1ИванИС-21 2МарияИС-21 3ПётрПМ-22

Проблема: название группы повторяется у каждого студента. Если группу переименуют, нужно менять все строки. Если студентов станет 10 000 — это огромный риск ошибок.

                    Решение — разделить данные на две таблицы: «Студенты» и «Группы». В «Студентах» хранить только GroupId — ссылку на группу. Именно для этого существует внешний ключ FOREIGN KEY, который мы изучили на прошлой лекции.
                

Что такое JOIN?

JOIN (по-английски «соединение», «объединение») — это команда SQL, которая объединяет строки из двух и более таблиц в один результат.

Students

→

JOIN

←

Groups

Результат: студенты с названиями групп

Идея простая: мы говорим СУБД (системе управления базами данных), что Students.GroupId — это ссылка на Groups.Id. И СУБД сама находит, какие строки совпадают.

JOIN — это «склейка» данных из нескольких таблиц по общему признаку. Это самая мощная возможность SQL.

Подготовка: две таблицы

Создадим две связанные таблицы и заполним их данными. Это база для всех примеров.

-- Таблица групп CREATE TABLE Groups ( Id INT PRIMARY KEY IDENTITY(1,1), Name NVARCHAR(50) NOT NULL ); INSERT INTO Groups (Name) VALUES (N'ИС-21'), (N'ПМ-22'), (N'ВБ-23'); -- Таблица студентов CREATE TABLE Students ( Id INT PRIMARY KEY IDENTITY(1,1), Name NVARCHAR(100) NOT NULL, Age INT, GroupId INT, FOREIGN KEY (GroupId) REFERENCES Groups(Id) ); INSERT INTO Students (Name, Age, GroupId) VALUES (N'Иван', 25, 1), (N'Мария', 30, 1), (N'Пётр', 22, 2), (N'Ольга', 27, 3), (N'Сергей',20, NULL);

Обратите внимание: у Сергея GroupId = NULL. Он пока не распределён в группу. Это пригодится нам при изучении LEFT JOIN.

INNER JOIN — только совпадения

INNER JOIN (внутреннее соединение) возвращает только те строки, для которых есть совпадение в обеих таблицах.

SELECT s.Name, g.Name AS GroupName FROM Students s INNER JOIN Groups g ON s.GroupId = g.Id;

Результат:

NameGroupName ИванИС-21 МарияИС-21 ПётрПМ-22 ОльгаВБ-23

Сергея в результате нет, потому что у него GroupId = NULL, и совпадения в таблице Groups нет.

Разбор синтаксиса INNER JOIN

Разберём каждую часть запроса по шагам.

SELECT s.Name, g.Name AS GroupName FROM Students s INNER JOIN Groups g ON s.GroupId = g.Id;

ЧастьЧто значит Students sтаблица Students, псевдоним s INNER JOIN Groups gприсоединить Groups, псевдоним g ON s.GroupId = g.Idусловие соединения — по каким столбцам совпадают строки s.Nameстолбец Name из таблицы Students g.Name AS GroupNameстолбец Name из Groups с переименованием

Псевдонимы s и g нужны, потому что у обеих таблиц есть столбец Name. Без псевдонимов СУБД не поймёт, какой Name имеется в виду.

LEFT JOIN — все левые и совпадения

LEFT JOIN (левое соединение) возвращает все строки левой таблицы плюс совпадения из правой. Если совпадения нет — правые столбцы будут NULL.

SELECT s.Name, g.Name AS GroupName FROM Students s LEFT JOIN Groups g ON s.GroupId = g.Id;

Результат:

NameGroupName ИванИС-21 МарияИС-21 ПётрПМ-22 ОльгаВБ-23 СергейNULL

Теперь Сергей есть в результате, но его группа пустая. Это важно: мы видим всех студентов, даже не распределённых.

RIGHT JOIN — все правые и совпадения

RIGHT JOIN (правое соединение) — зеркальное отражение LEFT JOIN. Возвращает все строки правой таблицы плюс совпадения из левой.

SELECT s.Name, g.Name AS GroupName FROM Students s RIGHT JOIN Groups g ON s.GroupId = g.Id;

Результат:

NameGroupName ИванИС-21 МарияИС-21 ПётрПМ-22 ОльгаВБ-23

Сергея здесь нет, потому что у него нет группы. А вот если бы в Groups была группа без студентов — она бы появилась в результате с Name = NULL.

FULL JOIN — все с двух сторон

FULL JOIN (полное соединение) возвращает все строки из обеих таблиц. Там, где нет совпадения, будут NULL.

SELECT s.Name, g.Name AS GroupName FROM Students s FULL JOIN Groups g ON s.GroupId = g.Id;

Результат:

NameGroupName ИванИС-21 МарияИС-21 ПётрПМ-22 ОльгаВБ-23 СергейNULL

Если бы в Groups была группа без студентов, она бы тоже попала в результат с Name = NULL. FULL JOIN используется редко — но полезен для поиска «сирот» с обеих сторон.

Сравнение всех JOIN

Соберём все виды соединений в одну таблицу.

Тип JOINЧто возвращаетКогда использовать INNER JOINтолько совпадениянужны только связанные данные LEFT JOINвсе из левой + совпадениянужны все из основной таблицы RIGHT JOINвсе из правой + совпадениязеркало LEFT JOIN FULL JOINвсе из обеихпоиск «сирот» в обеих таблицах

В 90% случаев используется INNER JOIN или LEFT JOIN. Остальные — реже.

Агрегатные функции — обзор

Агрегатные функции — это функции, которые берут много строк и возвращают одно значение. По-русски: «агрегируют», то есть «собирают» данные.

ФункцияЧто делаетПример COUNT()считает количество строкCOUNT(*) — сколько студентов SUM()сумма значенийSUM(Age) — общий возраст AVG()среднее арифметическоеAVG(Age) — средний возраст MIN()минимумMIN(Age) — самый младший MAX()максимумMAX(Age) — самый старший

Агрегатные функции — это «свёртка» множества значений в одно. Как если бы вы посчитали средний балл по всем оценкам в журнале.

COUNT — сколько строк

COUNT — самая частая агрегатная функция. Считает количество строк.

-- Сколько всего студентов SELECT COUNT(*) FROM Students; -- 5 -- Сколько студентов имеют группу SELECT COUNT(GroupId) FROM Students; -- 4 (у Сергея GroupId = NULL, он не считается)

COUNT(*) считает все строки, включая с NULL. COUNT(столбец) не считает строки, где столбец равен NULL. Это важное различие.

SUM, AVG, MIN, MAX

Остальные агрегатные функции работают с числами.

-- Суммарный возраст всех студентов SELECT SUM(Age) FROM Students; -- 124 -- Средний возраст SELECT AVG(Age) FROM Students; -- 24.8 -- Самый младший и самый старший SELECT MIN(Age), MAX(Age) FROM Students; -- 20, 30

Можно комбинировать несколько агрегатных функций в одном запросе — как в последнем примере.

GROUP BY — группировка данных

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

-- Сколько студентов в каждой группе SELECT GroupId, COUNT(*) AS StudentCount FROM Students GROUP BY GroupId;

Результат:

GroupIdStudentCount 12 21 31 NULL1

Без GROUP BY мы получили бы просто «5 студентов». С GROUP BY — «2 в первой группе, 1 во второй, 1 в третьей, 1 без группы».

GROUP BY + JOIN — самый частый случай

Обычно GROUP BY используется вместе с JOIN, чтобы показать не GroupId, а название группы.

SELECT g.Name AS GroupName, COUNT(s.Id) AS StudentCount FROM Groups g LEFT JOIN Students s ON s.GroupId = g.Id GROUP BY g.Name;

Результат:

GroupNameStudentCount ИС-212 ПМ-221 ВБ-231

Мы использовали LEFT JOIN, чтобы группы без студентов тоже попали в результат (с 0). С INNER JOIN они бы пропали.

HAVING — фильтрация групп

HAVING — это «WHERE для групп». WHERE фильтрует отдельные строки, а HAVING — уже сгруппированные результаты.

-- Показываем только группы, где больше 1 студента SELECT g.Name AS GroupName, COUNT(s.Id) AS StudentCount FROM Groups g LEFT JOIN Students s ON s.GroupId = g.Id GROUP BY g.Name HAVING COUNT(s.Id) > 1;

Результат:

GroupNameStudentCount ИС-212

Обратите внимание: в HAVING используется агрегатная функция COUNT(s.Id), а не просто столбец. Это ключевое отличие HAVING от WHERE.

WHERE vs HAVING

Разница простая: WHERE работает до группировки, HAVING — после.

КритерийWHEREHAVING Когда применяетсядо GROUP BYпосле GROUP BY К чему применяетсяк отдельным строкамк группам Можно ли агрегатные функциинетда ПримерWHERE Age > 20HAVING COUNT(*) > 1

Правило: если фильтр по отдельным значениям — WHERE. Если по результатам агрегации (сумма, среднее, количество) — HAVING.

WHERE и HAVING вместе

Их можно комбинировать: сначала WHERE отфильтрует строки, потом GROUP BY сгруппирует, потом HAVING отфильтрует группы.

-- Найти группы, где есть студенты старше 24 лет, -- и в этих группах больше одного такого студента SELECT g.Name AS GroupName, COUNT(s.Id) AS OlderCount FROM Groups g INNER JOIN Students s ON s.GroupId = g.Id WHERE s.Age > 24 GROUP BY g.Name HAVING COUNT(s.Id) > 1;

                    1. WHERE — оставили только студентов старше 24.

                    2. GROUP BY — сгруппировали по группам.

                    3. HAVING — оставили только те группы, где таких студентов больше одного.

Порядок выполнения запроса

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

1. FROM — откуда брать

↓

2. JOIN — соединение

↓

3. WHERE — фильтр строк

↓

4. GROUP BY — группировка

↓

5. HAVING — фильтр групп

↓

6. SELECT — выбор столбцов

↓

7. ORDER BY — сортировка

Понимание этого порядка помогает понять, почему WHERE не может использовать агрегатные функции, а HAVING может. И почему псевдоним из SELECT иногда недоступен в WHERE.

Практический пример: отчёт по группам

Соберём всё вместе: полный отчёт по группам с количеством и средним возрастом.

SELECT g.Name AS GroupName, COUNT(s.Id) AS StudentCount, AVG(s.Age) AS AvgAge, MIN(s.Age) AS MinAge, MAX(s.Age) AS MaxAge FROM Groups g LEFT JOIN Students s ON s.GroupId = g.Id GROUP BY g.Name ORDER BY StudentCount DESC;

Результат:

GroupNameStudentCountAvgAgeMinAgeMaxAge ИС-21227.52530 ПМ-221222222 ВБ-231272727

Это настоящий отчёт. Именно так работают дашборды и аналитика в реальных компаниях.

Типичные ошибки новичков

1. Забыл условие ON
INNER JOIN Groups g без ON ... — это декартово произведение (все строки со всеми). Результат огромный и бессмысленный.

2. Забыл GROUP BY
Пытаетесь выбрать GroupId и COUNT(*) без GROUP BY — ошибка. Нужно сгруппировать.

3. WHERE вместо HAVING
Пытаетесь отфильтровать группы через WHERE COUNT(*) > 1 — ошибка. Для агрегатов только HAVING.

4. Используете HAVING без GROUP BY
Это разрешено, но HAVING без GROUP BY работает как WHERE с одной группой. Обычно это ошибка.

5. INNER JOIN вместо LEFT JOIN
Теряются «сироты». Если нужны все из основной таблицы — LEFT JOIN.

6. NULL в агрегатах
AVG(Age) не учитывает строки с Age = NULL. Среднее считается по меньшему числу строк.

Практика: 5 задач

Попробуйте выполнить эти запросы в SSMS. Если не получилось — смотрите подсказки на предыдущих слайдах.

Задача 1

Выведите всех студентов с названием их группы (используйте INNER JOIN).

Задача 2

Выведите всех студентов, включая тех, у кого нет группы (используйте LEFT JOIN).

Задача 3

Посчитайте, сколько студентов в каждой группе.

Задача 4

Найдите средний возраст студентов в каждой группе.

Задача 5

Выведите только те группы, где больше одного студента, отсортировав по количеству по убыванию.

Решения разберём на практическом занятии. Попробуйте сначала сами — это важнее готового ответа.

Шпаргалка

Сохраните этот слайд — здесь все команды, которые мы изучили.

ЗадачаSQL-запрос соединить таблицыSELECT * FROM A INNER JOIN B ON A.Id = B.AId; все из левойSELECT * FROM A LEFT JOIN B ON A.Id = B.AId; все из правойSELECT * FROM A RIGHT JOIN B ON A.Id = B.AId; все с двух сторонSELECT * FROM A FULL JOIN B ON A.Id = B.AId; количество строкSELECT COUNT(*) FROM Students; суммаSELECT SUM(Age) FROM Students; среднееSELECT AVG(Age) FROM Students; минимум и максимумSELECT MIN(Age), MAX(Age) FROM Students; группировкаSELECT GroupId, COUNT(*) FROM Students GROUP BY GroupId; фильтр групп... GROUP BY GroupId HAVING COUNT(*) > 1;

Если помните эти команды — вы умеете строить отчёты любой сложности.

Итоги лекции

Подведём итоги. Вот что вы теперь знаете.

✅ JOIN — соединение таблиц по общему признаку.
✅ INNER JOIN — только совпадения.
✅ LEFT JOIN — все из левой + совпадения.
✅ RIGHT JOIN — все из правой + совпадения.
✅ FULL JOIN — все из обеих.
✅ COUNT, SUM, AVG, MIN, MAX — агрегатные функции.
✅ GROUP BY — группировка строк.
✅ HAVING — фильтрация групп (аналог WHERE для агрегатов).
✅ Порядок выполнения: FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.

В следующей практической работе мы создадим базу «Библиотека» в MS SQL Server и применим всё, что изучили: SELECT, JOIN, GROUP BY.

Домашнее задание

Задания для самостоятельной работы. Первые три обязательны, остальные — для оценки «отлично».

Создайте таблицы Authors и Books с внешним ключом (см. Лекцию 2).
Добавьте 3 авторов и 5 книг.
Напишите запрос: «все книги с именами авторов» через INNER JOIN.
Напишите запрос: «все авторы, даже без книг» через LEFT JOIN.
Посчитайте, сколько книг у каждого автора.
Найдите авторов, у которых больше одной книги.

Если задание кажется сложным — разбейте его на части. Сначала таблицы, потом данные, потом запросы.

Что дальше?

Мы прошли всю теоретическую часть по SQL. Дальше — практика.

Практика 1: создание базы «Библиотека» в MS SQL Server через SSMS. Таблицы, ключи, тестовые данные, запросы.
Практика 2: подключение Java к MS SQL Server через JDBC. Драйвер, подключение, чтение и запись данных.

SQL — это язык, на котором говорит весь мир данных. Освоив его, вы получите навык, который используется в каждой крупной компании.

Спасибо за внимание!

🔗

JOIN и агрегация — это то, что делает SQL мощным

Теперь вы умеете соединять таблицы, считать статистику и строить отчёты — как настоящий аналитик данных.

🔗 JOIN 📊 COUNT 📈 AVG 📦 GROUP BY 🚀 практика

Связи таблиц и агрегация: JOIN, GROUP BY, HAVING и агрегатные функции в MS SQL Server