SQL Academy · Урок

Что такое самосоединение

Q: Урок «Что такое самосоединение» бесплатный?

Да — полный текст урока «Что такое самосоединение» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Academy, подпишись на CoddyKit PRO. Курс SQL Academy содержит 4 уроков всего.

Q: Чему я научусь в уроке «Что такое самосоединение»?

Соединяйте таблицу с самой собой с помощью псевдонимов Ты практикуешь SQL Academy с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Q: Нужен ли мне опыт, чтобы начать SQL Academy?

Предыдущий опыт не требуется. SQL Academy на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 1 из 4.

Q: Сколько времени занимает урок «Что такое самосоединение»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Q: Можно ли писать и запускать код в этом уроке SQL Academy?

Да. Каждый урок SQL Academy включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Соединяйте таблицу с самой собой с помощью псевдонимов

«Что такое самосоединение» — бесплатный урок SQL Academy на CoddyKit. Это урок 1 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Academy, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Academy содержит 4 уроков всего.

Соединение таблицы с самой собой

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

Самый распространённый пример — таблица сотрудников, где в каждой строке сотрудника также хранится ID его руководителя — который тоже является сотрудником в той же таблице.

Таблица сотрудников

Рассмотрим таблицу employees, в каждой строке которой есть id, name и manager_id, указывающий на другую строку в той же таблице.

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

CREATE TABLE employees (
  id         INT PRIMARY KEY,
  name       VARCHAR(50),
  manager_id INT
);

INSERT INTO employees VALUES
  (1, 'Alice',   NULL),
  (2, 'Bob',     1),
  (3, 'Carol',   1),
  (4, 'Dave',    2),
  (5, 'Eve',     2),
  (6, 'Frank',   3);

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

Если Вы хотите вывести каждого сотрудника рядом с именем его руководителя, оба фрагмента данных находятся в одной и той же таблице. Обычный JOIN соединяет две разные таблицы (или как минимум два разных источника).

Чтобы прочитать одну и ту же таблицу дважды — один раз для сотрудника и один раз для руководителя, — необходимо использовать самосоединение в сочетании с псевдонимами таблиц.

Знакомство с псевдонимами

Чтобы выполнить самосоединение, укажите имя одной и той же таблицы дважды в предложении FROM / JOIN и задайте каждому вхождению свой псевдоним. Псевдонимы позволяют SQL рассматривать их как две отдельные логические таблицы.

Обычно используют короткие описательные псевдонимы, например e для сотрудника и m для руководителя.

-- Conceptual structure (not yet a full query)
-- FROM employees AS e
-- JOIN employees AS m ON e.manager_id = m.id

Ваше первое самосоединение

Ниже приведён полный пример самосоединения, который выводит каждого сотрудника вместе с именем его руководителя. Условие ON связывает manager_id сотрудника с id руководителя.

У Алисы нет руководителя, поэтому она исключается из результата с помощью INNER JOIN — на стороне руководителя для неё нет совпадающей строки.

SELECT
  e.name   AS employee,
  m.name   AS manager
FROM employees AS e
INNER JOIN employees AS m
  ON e.manager_id = m.id;

Включение строк без руководителя

При использовании INNER JOIN строки, для которых нет совпадения, удаляются. У Алисы указано manager_id = NULL, поэтому она исчезает из результатов.

Переключитесь на LEFT JOIN, чтобы сохранить всех сотрудников и просто выводить NULL (или метку), если руководитель отсутствует.

SELECT
  e.name             AS employee,
  COALESCE(m.name, 'No Manager') AS manager
FROM employees AS e
LEFT JOIN employees AS m
  ON e.manager_id = m.id;

Фильтрация результатов самосоединения

К самосоединению можно добавить предложение WHERE так же, как и к любому другому запросу. Приведённый ниже пример выбирает только сотрудников, непосредственный руководитель которых — Алиса (идентификатор = 1).

SELECT
  e.name  AS employee,
  m.name  AS manager
FROM employees AS e
INNER JOIN employees AS m
  ON e.manager_id = m.id
WHERE m.name = 'Alice';

Подсчёт непосредственных подчинённых

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

SELECT
  m.name           AS manager,
  COUNT(e.id)      AS direct_reports
FROM employees AS e
INNER JOIN employees AS m
  ON e.manager_id = m.id
GROUP BY m.name
ORDER BY direct_reports DESC;

Самосоединения для неиерархических данных

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

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

SELECT
  a.name AS employee_1,
  b.name AS employee_2,
  a.manager_id
FROM employees AS a
INNER JOIN employees AS b
  ON a.manager_id = b.manager_id
  AND a.id < b.id;

Роль псевдонима

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

Можно использовать любые имена псевдонимов. Главное правило заключается в том, что каждая ссылка на столбец должна иметь правильный псевдоним, чтобы SQL понимал, к какому экземпляру таблицы обращаться.

-- This will fail: column 'name' is ambiguous
-- SELECT name FROM employees JOIN employees ON manager_id = id;

-- This works: aliases remove the ambiguity
SELECT e.name, m.name
FROM employees AS e
JOIN employees AS m ON e.manager_id = m.id;

Примеры использования в реальных системах

Самосоединения часто встречаются в реальных базах данных. К распространённым вариантам использования относятся:

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

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

Быстрая проверка

Проверьте своё понимание самосоединений, прежде чем продолжить.

Итоги урока

На этом уроке Вы узнали, что такое самосоединение и когда его использовать:

Самосоединение связывает таблицу с самой собой, обращаясь к ней дважды с разными псевдонимами
Используйте INNER JOIN, чтобы исключить строки без совпадения (например, руководителей верхнего уровня), или LEFT JOIN, чтобы сохранить их
Добавляйте WHERE, GROUP BY и агрегатные функции точно так же, как в любом другом соединении
Используйте a.id < b.id в предложении ON, чтобы избежать дублирования пар при сравнении строк в одной таблице
Самосоединения — основной инструмент для работы с иерархическими данными, деревьями категорий и любыми самоссылочными связями

Далее Вы изучите многоуровневые иерархии с помощью рекурсивных CTE.

Часто задаваемые вопросы

Урок «Что такое самосоединение» бесплатный?

Да — полный текст урока «Что такое самосоединение» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Academy, подпишись на CoddyKit PRO. Курс SQL Academy содержит 4 уроков всего.

Чему я научусь в уроке «Что такое самосоединение»?

Соединяйте таблицу с самой собой с помощью псевдонимов Ты практикуешь SQL Academy с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать SQL Academy?

Предыдущий опыт не требуется. SQL Academy на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 1 из 4.

Сколько времени занимает урок «Что такое самосоединение»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке SQL Academy?

Да. Каждый урок SQL Academy включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

← Назад к SQL Academy