Самосоединение

До сих пор связь всегда вела из одной таблицы в другую: заказ к гостю, строка заказа к напитку. Но у сотрудников «Пенки» есть руководитель, и руководитель тоже сотрудник. Связь начинается и заканчивается в одной таблице staff. В этом уроке соединяем таблицу саму с собой и закрываем кластер про связи, который начался с урока о моделировании.

Два экземпляра одного списка

Представь, что у тебя на руках список сотрудников, и в каждой строке номер руководителя. Чтобы узнать имя руководителя Ани, ты держишь палец на её строке, а второй рукой листаешь тот же список и ищешь строку с нужным номером. Список один, но смотришь ты в него двумя глазами: один раз как в список подчинённых, второй раз как в список начальников.

Теперь представь, что ты распечатал список дважды и положил копии рядом. Левая копия про подчинённых, правая про руководителей. Номер руководителя в левой ищем среди номеров в правой. Это и есть самосоединение: обычный JOIN, в котором обе стороны это одна таблица.

Кто чей руководитель

Посмотрим на таблицу целиком:

sqlite> SELECT id, name, role, manager_id FROM staff;
╭────┬───────┬─────────────────┬────────────╮
│ id │ name  │      role       │ manager_id │
╞════╪═══════╪═════════════════╪════════════╡
│  1 │ Ольга │ управляющая     │ NULL       │
│  2 │ Тимур │ старший бариста │          1 │
│  3 │ Аня   │ бариста         │          2 │
│  4 │ Гоша  │ бариста         │          2 │
│  5 │ Вера  │ кассир          │          1 │
╰────┴───────┴─────────────────┴────────────╯

Столбец manager_id объявлен как REFERENCES staff(id): внешний ключ смотрит в свою же таблицу. Такая схема описывает иерархию: Ольга наверху, под ней Тимур и Вера, под Тимуром два бариста.

Чтобы вместо номера увидеть имя, соединяем staff с staff. Без псевдонимов тут не обойтись: SQLite должен понимать, о какой копии речь. Назовём их e (employee, сотрудник) и m (manager, руководитель):

sqlite> SELECT e.name AS employee, m.name AS manager
   ...> FROM staff e
   ...> JOIN staff m ON m.id = e.manager_id;
╭──────────┬─────────╮
│ employee │ manager │
╞══════════╪═════════╡
│ Тимур    │ Ольга   │
│ Аня      │ Тимур   │
│ Гоша     │ Тимур   │
│ Вера     │ Ольга   │
╰──────────┴─────────╯

Читай условие так: «номер руководителя из левой копии равен номеру строки в правой». Всё остальное работает, как в уроке про JOIN. Строки размножаться не будут: у каждого сотрудника не больше одного руководителя.

Не потерять управляющую

Ольги в ответе нет. У неё manager_id равен NULL, пары в правой копии не нашлось, и обычный JOIN её выбросил. Это та же история, что с гостями без карты. Лечится тоже так же, через LEFT JOIN:

sqlite> SELECT e.name AS employee, coalesce(m.name, 'нет') AS manager
   ...> FROM staff e
   ...> LEFT JOIN staff m ON m.id = e.manager_id;
╭──────────┬─────────╮
│ employee │ manager │
╞══════════╪═════════╡
│ Ольга    │ нет     │
│ Тимур    │ Ольга   │
│ Аня      │ Тимур   │
│ Гоша     │ Тимур   │
│ Вера     │ Ольга   │
╰──────────┴─────────╯

Теперь в списке все пятеро. coalesce подставил слово «нет» туда, где руководителя нет. Сотрудника без руководителя называют корнем иерархии.

Сколько подчинённых

Перевернём вопрос: у кого сколько людей в команде? Руководитель стоит теперь снаружи, а считаем тех, кто на него ссылается. Тут пригодится коррелированный подзапрос из прошлого урока:

sqlite> SELECT m.name,
   ...>        (SELECT count(*) FROM staff e WHERE e.manager_id = m.id) AS team
   ...> FROM staff m;
╭───────┬──────╮
│ name  │ team │
╞═══════╪══════╡
│ Ольга │    2 │
│ Тимур │    2 │
│ Аня   │    0 │
│ Гоша  │    0 │
│ Вера  │    0 │
╰───────┴──────╯

Подзапрос смотрит в ту же таблицу staff, только под другим именем. Для каждого сотрудника снаружи он считает, сколько строк ссылаются на него как на руководителя. У Ольги двое прямых подчинённых, Тимур и Вера. Аня и Гоша тоже работают под Ольгой, но через Тимура, поэтому в её команду не попали.

Через ступеньку

Иерархию можно пройти на несколько уровней вверх: каждый уровень это ещё одна копия таблицы. Найдём для бариста не только руководителя, но и руководителя руководителя:

sqlite> SELECT e.name, m.name AS manager, b.name AS boss
   ...> FROM staff e
   ...> JOIN staff m ON m.id = e.manager_id
   ...> JOIN staff b ON b.id = m.manager_id;
╭──────┬─────────┬───────╮
│ name │ manager │ boss  │
╞══════╪═════════╪═══════╡
│ Аня  │ Тимур   │ Ольга │
│ Гоша │ Тимур   │ Ольга │
╰──────┴─────────┴───────╯

Таблица staff встречается в запросе трижды под тремя именами. У Тимура и Веры руководитель есть, а у руководителя руководителя нет, поэтому обычный JOIN их отбросил.

Для дерева неизвестной глубины такая цепочка не годится: сколько копий писать, если уровней может быть десять? Для этого в SQL есть рекурсивный WITH RECURSIVE, который проходит дерево сам. Это тема за рамками новичкового раздела, а пример есть в документации по ссылке в конце урока.

Пары строк из одной таблицы

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

sqlite> SELECT a.name AS first, b.name AS second
   ...> FROM drinks a
   ...> JOIN drinks b ON a.category = b.category
   ...> WHERE a.category = 'чай';
╭────────────────┬────────────────╮
│     first      │     second     │
╞════════════════╪════════════════╡
│ Чай с чабрецом │ Матча-латте    │
│ Чай с чабрецом │ Чай с чабрецом │
│ Матча-латте    │ Матча-латте    │
│ Матча-латте    │ Чай с чабрецом │
╰────────────────┴────────────────╯

Две чайные позиции дали четыре пары. Две из них лишние: напиток в паре сам с собой. Ещё две повторяют друг друга в разном порядке. Оба лишних вида пар убирает одно условие a.id < b.id: номер левого напитка строго меньше правого.

sqlite> SELECT a.name AS first, b.name AS second
   ...> FROM drinks a
   ...> JOIN drinks b ON a.category = b.category AND a.id < b.id
   ...> WHERE a.category = 'чай';
╭────────────────┬─────────────╮
│     first      │   second    │
╞════════════════╪═════════════╡
│ Чай с чабрецом │ Матча-латте │
╰────────────────┴─────────────╯

Осталась одна пара. С < строка не может стоять в паре сама с собой, а из двух порядков остаётся только один. Если бы мы написали <>, остались бы обе перестановки. Ещё больше задач на самосоединение, с разбором типичных ошибок, есть в уроке из раздела про базы данных.

Попробуй сам

В песочнице все пары кофейных напитков и сотрудники с руководителями.

Кофе в «Пенке» пять видов, значит пар должно быть десять: проверь. Потом замени < на <> и посчитай, сколько пар станет. Найди пары напитков, где первый дороже второго: условие a.price > b.price вместо сравнения номеров.

Проверь себя

Задание

Дальше

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