Виды соединений - Лабораторные для студентов Прикладной Информатики

Виды соединений (JOIN'ов)

Почти любой аналитический запрос включает в себя объединение данных из нескольких различных сущностей, что вызывает логичный вопрос «каким связать эти таблицы между собой». В SQL для этого предназначены соединения (JOIN) решают эту задачу. Всего их есть несколько видов и у каждый тип обладает своей собственной логикой:

Тип JOIN	Что возвращает	Когда использовать
`INNER JOIN`	Только строки, для которых условие соединения выполняется в обеих таблицах.	«Пересечение» данных: показать только совпадающие сущности.
`LEFT (OUTER) JOIN`	Все строки из левой таблицы + совпадения из правой. Отсутствующие значения заполняются `NULL`.	«Обогащение» главной (левой) таблицы дополнительной информацией, не теряя ни одной записи.
`RIGHT (OUTER) JOIN`	Симметрично `LEFT`: всё из правой + совпадения из левой.	Редко нужен, но полезен, если «главной» является правая таблица.
`FULL (OUTER) JOIN`	Объединяет результаты `LEFT` и `RIGHT`: все строки обеих таблиц.	Сводные отчёты, когда важно увидеть и «висящие» записи в обеих таблицах.
`CROSS JOIN`	Декартово произведение: каждая строка левой таблицы сочетается с каждой строкой правой.	Генерация всех возможных комбинаций.
`SELF JOIN`	Соединение таблицы с самой собой (требует псевдонимов).	Иерархии (сотрудник-начальник), поиск «пар» внутри одного набора данных.

Прим. в некоторых СУБД есть ещё NATURAL JOIN и USING (… ), но на данном этапе мы сосредоточимся на шести базовых типах.

Проследим различия между ними на примере "сказки".

«DataUni»: мини-информационная система учебного центра

Учебный центр проводит курсы; студенты обращаются к расписанию, а менеджеры следят за заполняемостью. Схема данных сводится к четырём сущностям:

Students(student_id PK, full_name, city)
Courses(course_id PK, title, ects)
Professors(professor_id PK, full_name, department)
Enrollments(enrollment_id PK, student_id FK, course_id FK)

Содержимое таблиц

Students

id	ФИО	Город
1	Алиса Миронова	Москва
2	Борис Егоров	Казань
3	Вера Чан	Томск
4	Григорий Левин	Самара

Courses

id	Название	ECTS
10	SQL-аналитика	5
11	Python для Data Science	6
12	Power BI с нуля	4

Professors

id	ФИО	Кафедра
100	Дмитрий Белый	Факультет данных
101	Евгений Корнеев	Факультет данных

Enrollments

id	student_id	course_id
1001	1	10
1002	1	11
1003	2	10
1004	3	12

Демонстрация JOIN-ов «на доске»

1️⃣ INNER: увидеть только существующие пары студент – курс.

SELECT s.full_name, c.title
FROM Students AS s
JOIN Enrollments AS e  ON e.student_id = s.student_id
JOIN Courses     AS c  ON c.course_id  = e.course_id;

full_name	title
Алиса Миронова	SQL-аналитика
Алиса Миронова	Python для DS
Борис Егоров	SQL-аналитика
Вера Чан	Power BI с нуля

2️⃣ LEFT: показать всех студентов плюс их курсы (у Григория курсов нет).

SELECT s.full_name,
       COALESCE(c.title,'(нет курса)') AS course
FROM Students AS s
LEFT JOIN Enrollments AS e ON e.student_id = s.student_id
LEFT JOIN Courses     AS c ON c.course_id  = e.course_id
ORDER BY s.student_id;

full_name	course
Алиса Миронова	SQL-аналитика
…	Python …
Борис Егоров	SQL-аналитика
Вера Чан	Power BI …
Григорий Левин	(нет курса)

3️⃣ RIGHT: курсы без назначенных преподавателей.

INSERT INTO CourseTeachers(course_id, professor_id) VALUES (10,100);
SELECT c.title,
       p.full_name AS professor
FROM CourseTeachers AS ct
RIGHT JOIN Courses   AS c ON c.course_id = ct.course_id
LEFT JOIN Professors AS p ON p.professor_id = ct.professor_id;

title	professor
SQL-аналитика	Дмитрий Белый
Python для DS	NULL
Power BI с нуля	NULL

4️⃣ FULL: сводная картина «одиноких» студентов и курсов.

SELECT c.title, s.full_name
FROM Courses  AS c
FULL JOIN Enrollments AS e ON e.course_id  = c.course_id
FULL JOIN Students   AS s ON s.student_id = e.student_id;

title	full_name
SQL-аналитика	Алиса Миронова
SQL-аналитика	Борис Егоров
Python для Data Science	Алиса Миронова
Power BI с нуля	Вера Чан
NULL	Григорий Левин

5️⃣ CROSS: маркетинг хочет список «город × курс» для e-mail-рассылки.

SELECT DISTINCT s.city, c.title
FROM Students AS s
CROSS JOIN Courses  AS c
ORDER BY s.city, c.title;

city	title
Казань	Power BI с нуля
Казань	Python для Data Science
Казань	SQL-аналитика
Москва	Power BI с нуля
Москва	Python для Data Science
Москва	SQL-аналитика
Самара	Power BI с нуля
Самара	Python для Data Science
Самара	SQL-аналитика
Томск	Power BI с нуля
Томск	Python для Data Science
Томск	SQL-аналитика

6️⃣ SELF: если бы в Professors была колонка mentor_id, запрос выглядел бы так:

SELECT p.full_name   AS professor,
       m.full_name   AS mentor
FROM Professors AS p
LEFT JOIN Professors AS m ON m.professor_id = p.mentor_id;

professor	mentor
Дмитрий Белый	NULL
Евгений Корнеев	NULL

Практическое задание

Ваша предметная область - сеть пунктов выдачи онлайн-заказов. Ниже — схема, которую нужно создать вручную (имена полей можно копировать, но значения придумайте сами).

Branches(branch_id PK, city, address)
Couriers(courier_id PK, full_name, vehicle_id FK NULLABLE)
Vehicles(vehicle_id PK, type, capacity)
Parcels(parcel_id PK, weight, branch_id FK NULLABLE, courier_id FK NULLABLE, dispatched_at DATE)

Условие перед заполнением

хотя бы один филиал не должен иметь посылок;

в Parcels должны быть строки без курьера и/или без филиала;

минимум один курьер остаётся без транспорта, а минимум одна машина – без курьера.

После вставки 10-15 строк выполните (и сохраните результаты):

Пример скрипта для заполнения

/* ============================================================
   1. Создаём (или пересоздаём) базу
   ============================================================ */
IF DB_ID(N'ParcelOffice') IS NOT NULL
    DROP DATABASE ParcelOffice;
GO
CREATE DATABASE ParcelOffice;
GO
USE ParcelOffice;
GO

/* ============================================================
   2. Справочники и основной журнал
   ============================================================ */
-- ─── Branches ───
IF OBJECT_ID(N'dbo.Branches', N'U') IS NOT NULL
    DROP TABLE dbo.Branches;
GO
CREATE TABLE dbo.Branches (
    branch_id INT         IDENTITY(1,1) PRIMARY KEY,
    city      NVARCHAR(50) NOT NULL,
    address   NVARCHAR(100) NOT NULL
);

-- ─── Vehicles ───
IF OBJECT_ID(N'dbo.Vehicles', N'U') IS NOT NULL
    DROP TABLE dbo.Vehicles;
GO
CREATE TABLE dbo.Vehicles (
    vehicle_id INT           IDENTITY(1,1) PRIMARY KEY,
    type       NVARCHAR(50)  NOT NULL,
    capacity   DECIMAL(10,2) NOT NULL      -- кг
);

-- ─── Couriers ───
IF OBJECT_ID(N'dbo.Couriers', N'U') IS NOT NULL
    DROP TABLE dbo.Couriers;
GO
CREATE TABLE dbo.Couriers (
    courier_id INT            IDENTITY(1,1) PRIMARY KEY,
    full_name  NVARCHAR(100)  NOT NULL,
    vehicle_id INT            NULL
        CONSTRAINT FK_Couriers_Vehicles
        REFERENCES dbo.Vehicles(vehicle_id)
);

-- ─── Parcels ───
IF OBJECT_ID(N'dbo.Parcels', N'U') IS NOT NULL
    DROP TABLE dbo.Parcels;
GO
CREATE TABLE dbo.Parcels (
    parcel_id    INT            IDENTITY(1,1) PRIMARY KEY,
    weight       DECIMAL(10,2)  NOT NULL,        -- кг
    branch_id    INT            NULL
        CONSTRAINT FK_Parcels_Branches
        REFERENCES dbo.Branches(branch_id),
    courier_id   INT            NULL
        CONSTRAINT FK_Parcels_Couriers
        REFERENCES dbo.Couriers(courier_id),
    dispatched_at DATE          NOT NULL
);

/* ============================================================
   3. Демонстрационные данные (12 посылок, 4 курьера, 4 машины)
     • один филиал без посылок (branch_id = 3);
     • посылки без курьера и/или без филиала;
     • курьер без транспорта, транспорт без курьера.
   ============================================================ */
SET NOCOUNT ON;

-- ─── Branches ───
INSERT INTO dbo.Branches (city, address) VALUES
(N'Москва', N'ул. Тверская, 1'),   -- id = 1
(N'Казань', N'пр. Победы, 10'),    -- id = 2
(N'Сочи',  N'ул. Курортная, 5');   -- id = 3 (без посылок)

-- ─── Vehicles ───
INSERT INTO dbo.Vehicles (type, capacity) VALUES
(N'Фургон',    100),   -- id = 1
(N'Каблук',     50),   -- id = 2
(N'Минивэн',    80),   -- id = 3
(N'Грузовик',  200);   -- id = 4 (без курьера)

-- ─── Couriers ───
INSERT INTO dbo.Couriers (full_name, vehicle_id) VALUES
(N'Алексей Петров', 1),
(N'Никита Смирнов', NULL),  -- без транспорта
(N'Ольга Иванова',  2),
(N'Сергей Козлов',  3);

-- ─── Parcels ───
DECLARE @today DATE = CONVERT(date, GETDATE());          -- 2025-05-27
DECLARE @yday  DATE = DATEADD(day,-1,@today);            -- 2025-05-26
DECLARE @yyday DATE = DATEADD(day,-2,@today);            -- 2025-05-25

INSERT INTO dbo.Parcels (weight, branch_id, courier_id, dispatched_at) VALUES
( 5, 1, 1, @today),
(12, 1, 1, @today),
( 2, 1, NULL, @today),          -- филиал есть, курьера нет
(10, 2, 3, @today),
(15, 2, NULL, @today),          -- филиал есть, курьера нет
( 7, NULL, 2, @today),          -- курьер без филиала
( 9, NULL, NULL, @today),       -- ни филиала, ни курьера
(20, 1, 1, @yday),
( 4, 2, 3, @yday),
( 6, 1, 1, @yday),
(40, 2, 3, @yyday),
( 3, NULL, NULL, @yyday);       -- ни филиала, ни курьера

1. список всех посылок с именами курьеров, если они назначены (LEFT JOIN).
2. курьеры, у которых нет ни одной посылки на сегодняшнюю дату (LEFT JOIN + WHERE p.parcel_id IS NULL).
3. филиалы и число отправленных посылок, включая нулевую активность (LEFT JOIN + агрегат).
4. посылки, у которых и филиал, и курьер не заданы (FULL JOIN + фильтр по IS NULL).
5. «цепной» запрос Branches → Parcels → Couriers → Vehicles: вычислите нагрузку weight / capacity и отсортируйте по убыванию, чтобы увидеть, где транспорт перегружают.