ИТ.03 - 12 - Создание связанных таблиц. Операции JOIN и UNION

Введение

В предыдущей теме мы научились описывать модель данных на уровне ER-диаграмм. Теперь переходим к её реализации в SQL: создадим несколько таблиц, настроим связи и посмотрим, как извлекать данные сразу из нескольких таблиц с помощью JOIN.

В этой лекции:

• разбираем синтаксис CREATE TABLE с внешними ключами;
• включаем поддержку ограничений целостности в SQLite;
• создаём учебный набор таблиц «преподаватели — курсы — студенты»;
• изучаем базовые варианты JOIN и операции UNION, показывая как объединять выборки.

Базовый синтаксис CREATE TABLE ... FOREIGN KEY

CREATE TABLE дочерняя_таблица (
  поле1 ТИП NOT NULL,
  ...
  внешний_ключ ТИП REFERENCES родительская_таблица(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

Ключевые моменты:

1. Совпадение типов. Поле, на которое ссылается внешний ключ, и сам внешний ключ должны быть одного типа (например, INTEGER).
2. Именование. Часто используют шаблон <таблица>_id, чтобы по названию поля было понятно, на что идёт ссылка.
3. ON DELETE / ON UPDATE. Эти предложения описывают, что случится с дочерними записями, если удалить или изменить родительскую. Для учебных баз удобно использовать CASCADE, чтобы записи автоматически удалялись вместе с родителем.
4. UNIQUE/PRIMARY KEY. Внешний ключ всегда ссылается на уникальное поле родительской таблицы (обычно на PRIMARY KEY).

Учебный пример: преподаватели, курсы, студенты

Код создания и заполнения

PRAGMA foreign_keys = ON;

DROP TABLE IF EXISTS enrollments;
DROP TABLE IF EXISTS students;
DROP TABLE IF EXISTS courses;
DROP TABLE IF EXISTS teachers;

CREATE TABLE teachers (
  id INTEGER PRIMARY KEY,
  full_name TEXT NOT NULL,
  email TEXT
);

CREATE TABLE courses (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  hours INTEGER NOT NULL,
  teacher_id INTEGER NOT NULL REFERENCES teachers(id)
);

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  full_name TEXT NOT NULL,
  group_code TEXT
);

CREATE TABLE enrollments (
  id INTEGER PRIMARY KEY,
  student_id INTEGER NOT NULL REFERENCES students(id) ON DELETE CASCADE,
  course_id INTEGER NOT NULL REFERENCES courses(id) ON DELETE CASCADE,
  enrolled_at TEXT DEFAULT (date('now')),
  UNIQUE(student_id, course_id)
);

INSERT INTO teachers (id, full_name, email) VALUES
  (1, 'Анна Кузнецова', 'anna@skills.example'),
  (2, 'Михаил Орлов', 'm.orlov@skills.example'),
  (3, 'Ирина Гаврилова', 'gavr@skills.example');

INSERT INTO courses (id, title, hours, teacher_id) VALUES
  (1, 'SQL для аналитиков', 32, 1),
  (2, 'Python для аналитиков', 48, 2),
  (3, 'Основы BI', 24, 1),
  (4, 'Проектирование БД', 36, 3);

INSERT INTO students (id, full_name, group_code) VALUES
  (1, 'Сергей Ломов', 'DA-01'),
  (2, 'Елена Морева', 'DA-01'),
  (3, 'Владимир Титов', 'DA-02'),
  (4, 'Александра Соколова', 'DA-02'),
  (5, 'Кирилл Демидов', 'DA-03');

INSERT INTO enrollments (id, student_id, course_id, enrolled_at) VALUES
  (1, 1, 1, '2024-02-10'),
  (2, 1, 2, '2024-02-15'),
  (3, 2, 1, '2024-02-12'),
  (4, 3, 3, '2024-02-18'),
  (5, 4, 4, '2024-02-20'),
  (6, 5, 2, '2024-02-21');

Проверяем структуру

PRAGMA table_info('courses');
PRAGMA foreign_key_list('enrollments');

PRAGMA table_info показывает столбцы и их ограничения, а foreign_key_list — таблицы, на которые ссылается выбранная таблица.

Создание таблиц со связями по шагам

1. Создаём родительскую таблицу. Например, teachers с id как первичным ключом.
2. Создаём дочернюю таблицу и добавляем внешний ключ. courses получает поле teacher_id INTEGER REFERENCES teachers(id).
3. Настраиваем таблицу-связку для M — N. Таблица enrollments содержит два внешних ключа (student_id, course_id) и хранит дополнительные атрибуты (enrolled_at).
4. Включаем PRAGMA foreign_keys. Без этого SQLite проигнорирует ограничения.
5. Заранее продумываем поведение при удалении/обновлении. В учебном примере использованы ON DELETE CASCADE, чтобы не оставались «висящие» записи.

Виды JOIN на практике

JOIN объединяет строки двух и более таблиц по условию соответствия. Сначала каждую таблицу фильтруют отдельно (WHERE), затем механизм сопоставляет пары строк по условию ON. Тип JOIN определяет, какие строки попадут в результат, если совпадений нет.

Обзор

INNER JOIN

Возвращает строки, для которых условие связи выполняется в обеих таблицах. Если ключевое слово JOIN указано без уточнения типа, подразумевается именно INNER JOIN.

SELECT
  students.full_name AS student,
  courses.title AS course,
  teachers.full_name AS teacher
FROM enrollments
INNER JOIN students ON students.id = enrollments.student_id
INNER JOIN courses ON courses.id = enrollments.course_id
INNER JOIN teachers ON teachers.id = courses.teacher_id
ORDER BY student;

LEFT JOIN

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

SELECT
  courses.title,
  students.full_name AS student
FROM courses
LEFT JOIN enrollments ON enrollments.course_id = courses.id
LEFT JOIN students ON students.id = enrollments.student_id
ORDER BY courses.title, student;

Такой запрос позволяет увидеть курсы без слушателей — у них будет NULL в столбце student.

RIGHT JOIN

SQLite не поддерживает RIGHT JOIN. Если нужно получить аналогичный результат, обычно перестраивают запрос (например, меняют местами таблицы в LEFT JOIN или используют UNION). Пример объединения рассмотрим в блоке про UNION.

FULL OUTER JOIN

Возвращает все строки из обеих таблиц, совпадающие и несовпадающие. Если совпадений нет, значения обеих таблиц будут NULL. SQLite не поддерживает FULL OUTER JOIN, но его можно реализовать с помощью LEFT JOIN и RIGHT JOIN. Пример объединения рассмотрим в блоке про UNION.

CROSS JOIN

Формирует декартово произведение. В SQLite используется ключевое слово CROSS JOIN или просто перечисление таблиц через запятую (но лучше явно).

SELECT teachers.full_name, courses.title
FROM teachers
CROSS JOIN courses
WHERE teachers.id = 1;

Мы редко используем CROSS JOIN напрямую, но он помогает, например, при генерации расписаний или комбинаций.

Объединение выборок: UNION и UNION ALL

UNION объединяет несколько запросов SELECT в одну выборку. Требования:

• одинаковое количество столбцов в каждом запросе;
• совместимые типы данных по позициям;
• итоговые имена столбцов берутся из первого SELECT.

UNION по умолчанию убирает дубликаты. Если дубликаты нужны, используйте UNION ALL.

Пример: курсы со слушателями и без них

SELECT
  courses.title AS course,
  'Есть слушатели' AS status
FROM courses
JOIN enrollments ON enrollments.course_id = courses.id
UNION
SELECT
  courses.title,
  'Свободно' AS status
FROM courses
LEFT JOIN enrollments ON enrollments.course_id = courses.id
WHERE enrollments.id IS NULL
ORDER BY course, status;

Эмуляция RIGHT JOIN через UNION ALL

SELECT
  students.full_name AS student,
  courses.title AS course
FROM students
LEFT JOIN enrollments ON enrollments.student_id = students.id
LEFT JOIN courses ON courses.id = enrollments.course_id
UNION ALL
SELECT
  students.full_name,
  courses.title
FROM courses
LEFT JOIN enrollments ON enrollments.course_id = courses.id
LEFT JOIN students ON students.id = enrollments.student_id
WHERE students.id IS NULL;

Первая часть запроса выводит студентов и их курсы (если они есть). Вторая часть добавляет курсы без студентов. Итоговая выборка покрывает обе ситуации — аналогичную поведению RIGHT JOIN.

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

Задание 1 — новая таблица и внешний ключ

CREATE TABLE classrooms (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  capacity INTEGER NOT NULL,
  course_id INTEGER NOT NULL REFERENCES courses(id) ON DELETE CASCADE
);

Задание 2 — INNER JOIN

SELECT
  students.full_name AS student,
  courses.title AS course
FROM enrollments
INNER JOIN students ON students.id = enrollments.student_id
INNER JOIN courses ON courses.id = enrollments.course_id
ORDER BY student;

Задание 3 — Проверяем структуру через PRAGMA

PRAGMA table_info('classrooms');
PRAGMA foreign_key_list('classrooms');