Skip to content

lesson06.md

Latest commit

 

History

History
480 lines (351 loc) · 26.8 KB

lesson06.md

File metadata and controls

480 lines (351 loc) · 26.8 KB

Урок 6. Связи таблиц и JOIN

Почему данные разбиваются на таблицы

Представьте что мы храним все данные о заказах в одной таблице:

┌────┬──────────────────┬───────────────────┬──────────────────┬──────────┬──────────┐
│ id │ user_name        │ user_email        │ product_name     │ price    │ quantity │
├────┼──────────────────┼───────────────────┼──────────────────┼──────────┼──────────┤
│  1 │ Алексей Смирнов  │ alexey@gmail.com  │ Ноутбук Lenovo   │ 75000.00 │        1 │
│  2 │ Алексей Смирнов  │ alexey@gmail.com  │ Мышь Logitech    │  5500.00 │        1 │
│  3 │ Мария Иванова    │ maria@yandex.ru   │ Смартфон Samsung │ 45000.00 │        1 │
│  4 │ Алексей Смирнов  │ alexey@gmail.com  │ Чистый код       │  1200.00 │        2 │
└────┴──────────────────┴───────────────────┴──────────────────┴──────────┴──────────┘

Проблемы очевидны:

  • Дублирование данных — имя и email Алексея повторяются в каждой строке
  • Аномалия обновления — если Алексей сменит email, придётся обновить все его строки. Пропустили одну — данные противоречат друг другу
  • Аномалия удаления — удалили все заказы пользователя, потеряли информацию о нём
  • Аномалия вставки — не можем добавить пользователя без заказа

Решение — нормализация: разбить данные на отдельные таблицы так, чтобы каждый факт хранился ровно в одном месте. Пользователи — в users, товары — в products, заказы — в orders. Это та структура которую вы уже видели в нашей базе данных.

Foreign Key — связь между таблицами

После разбиения на таблицы возникает вопрос: как связать заказ с пользователем? Через внешний ключ (Foreign Key, FK).

Внешний ключ — это столбец в одной таблице, который ссылается на первичный ключ другой таблицы:

users                          orders
┌────┬──────────────────┐      ┌────┬─────────┬────────────┐
│ id │ name             │      │ id │ user_id │ status     │
├────┼──────────────────┤      ├────┼─────────┼────────────┤
│  1 │ Алексей Смирнов  │◄─────│  1 │    1    │ delivered  │
│  2 │ Мария Иванова    │◄─────│  3 │    2    │ delivered  │
│  3 │ Дмитрий Козлов   │      │  4 │    1    │ delivered  │
└────┴──────────────────┘      └────┴─────────┴────────────┘

orders.user_id — это внешний ключ. Значение 1 в этом столбце означает "этот заказ принадлежит пользователю с id = 1". Данные пользователя хранятся один раз в users, а заказы просто ссылаются на него по номеру.

Внешний ключ объявляется при создании таблицы:

CREATE TABLE orders (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id    INTEGER NOT NULL,
    status     TEXT    NOT NULL,
    created_at TEXT    NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

FOREIGN KEY (user_id) REFERENCES users(id) говорит СУБД: значение в user_id должно существовать в столбце id таблицы users. Это гарантия целостности — нельзя создать заказ для несуществующего пользователя.

В нашем seed.sql внешние ключи объявлены именно так. Вы уже работаете с базой где таблицы связаны через FK — теперь понятно почему.

JOIN — соединение таблиц в запросе

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

Базовый синтаксис:

SELECT столбцы
FROM таблица_а
JOIN таблица_б ON таблица_а.столбец = таблица_б.столбец;

INNER JOIN

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

-- Заказы с именами пользователей
SELECT
    orders.id          AS order_id,
    users.name         AS user_name,
    orders.status,
    orders.created_at
FROM orders
INNER JOIN users ON orders.user_id = users.id;
┌──────────┬──────────────────┬───────────┬────────────┐
│ order_id │ user_name        │ status    │ created_at │
├──────────┼──────────────────┼───────────┼────────────┤
│        1 │ Алексей Смирнов  │ delivered │ 2024-02-01 │
│        2 │ Алексей Смирнов  │ delivered │ 2024-03-15 │
│        3 │ Мария Иванова    │ delivered │ 2024-02-20 │
│        4 │ Дмитрий Козлов   │ delivered │ 2024-03-01 │
│ ...      │ ...              │ ...       │ ...        │
└──────────┴──────────────────┴───────────┴────────────┘

Вместо числа user_id = 1 мы видим имя Алексей Смирнов. JOIN "подтянул" данные из второй таблицы.

Псевдонимы таблиц в JOIN

Уже в этом примере видно что orders.id, users.name — это длинно. Псевдонимы делают запрос компактнее:

SELECT
    o.id     AS order_id,
    u.name   AS user_name,
    o.status,
    o.created_at
FROM orders AS o
INNER JOIN users AS u ON o.user_id = u.id;

Результат идентичен. Псевдонимы o и u — стандартная практика при работе с JOIN. Ключевое слово AS для таблиц можно опускать: FROM orders o.

Как работает INNER JOIN

Можно представить это как операцию попарного сравнения: для каждой строки из orders ищем строку в users где users.id = orders.user_id. Если совпадение найдено — строки объединяются в одну. Если нет — строка не попадает в результат.

Именно поэтому INNER JOIN называют "пересечением" — в результате только те строки которые есть в обеих таблицах:

orders имеет user_id = 1  →  users имеет id = 1  →  СОВПАДЕНИЕ → в результат
orders имеет user_id = 99 →  users не имеет id = 99 → НЕТ СОВПАДЕНИЯ → не в результат

LEFT JOIN

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

-- Все пользователи и их заказы (если есть)
SELECT
    u.name        AS user_name,
    o.id          AS order_id,
    o.status
FROM users AS u
LEFT JOIN orders AS o ON u.id = o.user_id
ORDER BY u.name;
┌──────────────────┬──────────┬───────────┐
│ user_name        │ order_id │ status    │
├──────────────────┼──────────┼───────────┤
│ Алексей Смирнов  │        1 │ delivered │
│ Алексей Смирнов  │        2 │ delivered │
│ Андрей Борисов   │       14 │ delivered │
│ Анна Волкова     │        9 │ delivered │
│ Дмитрий Козлов   │        4 │ delivered │
│ Дмитрий Козлов   │        5 │ cancelled │
│ Елена Новикова   │        6 │ delivered │
│ ...              │ ...      │ ...       │
└──────────────────┴──────────┴───────────┘

В нашей базе у каждого пользователя есть хотя бы один заказ. Но LEFT JOIN становится незаменимым когда нужно найти строки без совпадений — например пользователей без заказов:

-- Пользователи у которых нет ни одного заказа
SELECT
    u.name,
    u.email
FROM users AS u
LEFT JOIN orders AS o ON u.id = o.user_id
WHERE o.id IS NULL;

Логика: LEFT JOIN гарантирует что все пользователи попадут в результат. Те у кого нет заказов получат NULL в столбцах из orders. WHERE o.id IS NULL оставляет только таких пользователей. В нашей базе таких нет — запрос вернёт пустой результат, что тоже является корректным ответом.

RIGHT JOIN

RIGHT JOIN — зеркало LEFT JOIN: возвращает все строки из правой таблицы и совпадающие из левой.

-- Все товары и позиции заказов где они встречаются
SELECT
    p.name        AS product_name,
    oi.order_id,
    oi.quantity
FROM order_items AS oi
RIGHT JOIN products AS p ON oi.product_id = p.id;

Важный момент: RIGHT JOIN в SQLite поддерживается начиная с версии 3.39 (2022). В более ранних версиях он не работает. На практике RIGHT JOIN всегда можно заменить LEFT JOIN просто поменяв таблицы местами. Поэтому в реальном коде RIGHT JOIN встречается редко — большинство разработчиков предпочитают LEFT JOIN для единообразия.

Пример замены RIGHT JOIN на LEFT JOIN:

-- Эти два запроса дают одинаковый результат:

-- RIGHT JOIN
SELECT p.name, oi.order_id
FROM order_items AS oi
RIGHT JOIN products AS p ON oi.product_id = p.id;

-- LEFT JOIN (таблицы поменяли местами)
SELECT p.name, oi.order_id
FROM products AS p
LEFT JOIN order_items AS oi ON p.id = oi.product_id;

Соединение трёх и более таблиц

JOIN можно цеплять последовательно — каждый новый JOIN добавляет ещё одну таблицу к уже соединённому результату:

-- Позиции заказов с именами пользователей и названиями товаров
SELECT
    u.name          AS user_name,
    o.id            AS order_id,
    p.name          AS product_name,
    oi.quantity,
    oi.price_at_time
FROM order_items AS oi
INNER JOIN orders   AS o  ON oi.order_id   = o.id
INNER JOIN users    AS u  ON o.user_id     = u.id
INNER JOIN products AS p  ON oi.product_id = p.id
ORDER BY o.id, p.name;
┌──────────────────┬──────────┬──────────────────────────────┬──────────┬──────────────────┐
│ user_name        │ order_id │ product_name                 │ quantity │ price_at_time    │
├──────────────────┼──────────┼──────────────────────────────┼──────────┼──────────────────┤
│ Алексей Смирнов  │        1 │ Мышь Logitech MX Master      │        1 │          5500.00 │
│ Алексей Смирнов  │        1 │ Ноутбук Lenovo IdeaPad       │        1 │         75000.00 │
│ Алексей Смирнов  │        2 │ Паттерны проектирования      │        1 │          1400.00 │
│ Алексей Смирнов  │        2 │ Чистый код. Мартин           │        2 │          1200.00 │
│ Мария Иванова    │        3 │ Веб-камера Logitech C920     │        1 │          7500.00 │
│ ...              │ ...      │ ...                          │ ...      │ ...              │
└──────────────────┴──────────┴──────────────────────────────┴──────────┴──────────────────┘

Каждый JOIN добавляет одну таблицу. Порядок JOIN имеет значение для читаемости, но не для результата — СУБД сама оптимизирует порядок соединений.

JOIN с GROUP BY

JOIN и агрегаты отлично работают вместе. Типичный пример — итоги по заказу:

-- Сумма каждого заказа с именем пользователя
SELECT
    u.name                              AS user_name,
    o.id                                AS order_id,
    o.status,
    ROUND(SUM(oi.price_at_time * oi.quantity), 2) AS order_total
FROM orders AS o
INNER JOIN users       AS u  ON o.user_id     = u.id
INNER JOIN order_items AS oi ON o.id          = oi.order_id
GROUP BY o.id, u.name, o.status
ORDER BY order_total DESC;
┌──────────────────┬──────────┬───────────┬─────────────┐
│ user_name        │ order_id │ status    │ order_total │
├──────────────────┼──────────┼───────────┼─────────────┤
│ Алексей Смирнов  │        1 │ delivered │    80500.00 │
│ Наталья Зайцева  │       12 │ shipped   │    83900.00 │
│ Иван Попов       │       10 │ shipped   │    55000.00 │
│ ...              │ ...      │ ...       │ ...         │
└──────────────────┴──────────┴───────────┴─────────────┘

Порядок клаузул в запросе с JOIN

После изучения соединений структура SQL-запроса расширяется:

SELECT [DISTINCT] столбцы
FROM таблица_а
JOIN таблица_б ON условие
WHERE условие_по_строкам
GROUP BY столбец
HAVING условие_по_группам
ORDER BY столбец [ASC|DESC]
LIMIT n OFFSET m;

JOIN записывается сразу после FROM, потому что соединение таблиц — это часть этапа получения исходных данных. Если используется несколько соединений, они перечисляются последовательно:

FROM users
JOIN orders ON orders.user_id = users.id
JOIN products ON products.id = orders.product_id

Каждый следующий JOIN добавляет к запросу ещё одну таблицу.

Как и раньше, важно различать:

  • порядок написания SQL-запроса
  • и логический порядок его выполнения внутри СУБД

Хотя SELECT в синтаксисе пишется первым, СУБД сначала формирует набор данных из таблиц и соединений, а уже потом применяет фильтрацию, группировку и формирует итоговый результат.

Логический порядок выполнения SQL-запроса с JOIN:

1. FROM       — выбирается первая таблица
2. JOIN / ON  — выполняются соединения таблиц
3. WHERE      — фильтруются строки результата соединения
4. GROUP BY   — строки объединяются в группы
5. HAVING     — фильтруются группы
6. SELECT     — формируются итоговые столбцы и псевдонимы
7. DISTINCT   — удаляются дубликаты
8. ORDER BY   — сортировка результата
9. LIMIT/OFFSET — ограничение количества строк

Из-за этого условия в ON и WHERE работают на разных этапах запроса. ON участвует в самом соединении таблиц, а WHERE фильтрует уже готовый результат после выполнения JOIN. Позже это станет особенно важно при изучении LEFT JOIN и других видов соединений.

Типичные ошибки при JOIN

1. Забыть условие ON — декартово произведение

-- Ошибка: нет ON — каждая строка из orders соединится с каждой из users
SELECT * FROM orders
INNER JOIN users;  -- в SQLite это ошибка синтаксиса

-- В некоторых СУБД допустима запись через запятую — и это опасно:
SELECT * FROM orders, users;  -- 18 заказов × 15 пользователей = 270 строк мусора

Всегда пишите условие ON. Декартово произведение — распространённая ошибка которая возвращает огромное количество бессмысленных данных.

2. Неоднозначный столбец без указания таблицы

-- Ошибка: столбец id есть и в orders и в users — какой имеется в виду?
SELECT id, name FROM orders
INNER JOIN users ON orders.user_id = users.id;
-- → ошибка: ambiguous column name: id

-- Правильно: указываем таблицу явно
SELECT o.id, u.name FROM orders AS o
INNER JOIN users AS u ON o.user_id = u.id;

Когда в запросе несколько таблиц — всегда указывайте таблицу для каждого столбца. Псевдонимы делают это удобным.

3. Перепутать LEFT и INNER — потерять данные

-- Если нужны ВСЕ товары включая те что никто не заказывал:
-- INNER JOIN потеряет товары без заказов
SELECT p.name, COUNT(oi.id) AS times_ordered
FROM products AS p
INNER JOIN order_items AS oi ON p.id = oi.product_id
GROUP BY p.id;  -- товары без заказов не попадут в результат

-- LEFT JOIN сохранит все товары
SELECT p.name, COUNT(oi.id) AS times_ordered
FROM products AS p
LEFT JOIN order_items AS oi ON p.id = oi.product_id
GROUP BY p.id;  -- товары без заказов получат times_ordered = 0

Правило: если нужны все строки из одной таблицы независимо от наличия совпадений — используйте LEFT JOIN.

4. Условие ON vs WHERE — фильтрация до и после JOIN

-- WHERE фильтрует после соединения — из результата LEFT JOIN убираются NULL-строки
-- и он ведёт себя как INNER JOIN:
SELECT u.name, o.id
FROM users AS u
LEFT JOIN orders AS o ON u.id = o.user_id
WHERE o.status = 'delivered';  -- пользователи без заказов исчезнут

-- Если нужно сохранить всех пользователей — фильтр в ON:
SELECT u.name, o.id
FROM users AS u
LEFT JOIN orders AS o ON u.id = o.user_id AND o.status = 'delivered';
-- пользователи без delivered-заказов останутся, но с NULL в o.id

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

  1. Что такое нормализация и какую проблему она решает?
  2. Что такое внешний ключ и зачем он нужен?
  3. В чём принципиальная разница между INNER JOIN и LEFT JOIN?
  4. Почему нужно указывать имя таблицы или псевдоним перед столбцом при JOIN?
  5. Что такое декартово произведение и почему оно опасно?
  6. Как найти с помощью LEFT JOIN строки которые не имеют совпадений в другой таблице?
  7. Почему RIGHT JOIN на практике используется редко?
  8. Можно ли использовать GROUP BY и агрегатные функции вместе с JOIN? Как это работает?
  9. В чём разница между условием в ON и в WHERE при LEFT JOIN?
  10. Какой порядок написания клаузул в запросе с JOIN?

Задачи

Задача 1.

Выведите список всех товаров с названием их категории. Используйте INNER JOIN. Выведите product name и category name.

Задача 2.

Выведите все заказы с именем и email пользователя который их сделал. Выведите order_id, user_name, email, status.

Задача 3.

Найдите товары которые ни разу не были заказаны. Выведите id и name товара.

Задача 4.

Для каждой категории посчитайте количество товаров в ней. Выведите название категории и количество товаров. Отсортируйте по количеству по убыванию.

Задача 5.

Выведите полный состав каждого заказа: имя пользователя, order_id, название товара, количество и цену на момент покупки. Отсортируйте по order_id.

Задача 6.

Посчитайте итоговую сумму каждого заказа (quantity * price_at_time). Выведите order_id, имя пользователя, статус заказа и итоговую сумму. Отсортируйте по сумме по убыванию.

Задача 7.

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

Задача 8.

Выведите пользователей из Москвы и суммарную стоимость всех их заказов. Учитывайте только доставленные заказы (delivered). Отсортируйте по сумме по убыванию.

Задача 9.

Для каждой категории найдите самый дорогой товар который был реально куплен (есть в order_items). Выведите название категории, название товара и цену.

Задача 10.

Найдите пользователей у которых есть хотя бы один отменённый заказ (cancelled). Выведите имя пользователя, email и количество отменённых заказов.

Предыдущий урок | Следующий урок