Исполняемые доки SQLite: Self Join

Q: Что такое self join в SQLite?

Self join - это обычный JOIN, в котором таблица соединяется сама с собой. Вы задаёте ей два разных алиаса, чтобы SQLite воспринимал их как два отдельных источника строк, и сопоставляете записи по столбцу, который связывает одну строку с другой. Чаще всего так оформляют связь «родитель–потомок», например сотрудник и его руководитель.

Q: Зачем в self join обязательно нужны алиасы?

Без алиасов SQLite просто не поймёт, к какой именно «копии» таблицы относится столбец, который вы пишете. Когда у каждого экземпляра свой алиас (скажем, e для сотрудника и m для руководителя), вы спокойно пишете e.manager_id = m.id без всякой двусмысленности. Это не вопрос стиля - без алиасов запрос просто не разберётся парсером.

Q: Когда выбрать self join, а когда подзапрос?

Self join берут, когда в результате нужны столбцы сразу из обеих строк - например, имя сотрудника и имя его руководителя в одной строке выдачи. Подзапрос подойдёт, если нужно только отфильтровать данные или подтянуть одно значение. А вот для глубоко вложенных иерархий ни то, ни другое толком не работает - там в дело идёт рекурсивный CTE.

Self join - это обычный JOIN, просто с алиасами

В self join нет ничего магического. Это самый обычный JOIN, у которого по обе стороны оказывается одна и та же таблица. Вся хитрость в том, что SQLite нужно как-то различать две её копии - поэтому каждой даём свой алиас.

Соединение таблицы самой с собой пригождается, когда строка ссылается на другую строку в той же таблице. Хрестоматийный пример - таблица employees, где у каждого сотрудника есть manager_id, указывающий на его руководителя:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

SELECT * FROM employees;

У Ады руководителя нет. Борис и Клео подчиняются Аде. Диего и Эсме - Борису. Вся эта иерархия живёт в одной-единственной таблице, и именно тут self join по-настоящему показывает свою силу.

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

Чтобы вытащить каждого сотрудника вместе с именем его руководителя, соединим таблицу employees саму с собой. Одна копия будет играть роль «сотрудника», вторая - «руководителя»:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

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

Думайте об этом как о двух таблицах, которые просто хранятся в одном месте. e - это строка сотрудника, m - строка руководителя. Условие соединения e.manager_id = m.id сшивает их вместе: для каждого сотрудника ищется строка из m, у которой id совпадает с manager_id этого сотрудника.

Обратите внимание, что Ада не попала в результат. У неё manager_id равен NULL, а INNER JOIN молча выбрасывает строки, для которых не нашлось пары.

Сохраняем строки без пары: LEFT JOIN

Если в выборке нужны все сотрудники, в том числе те, у кого нет руководителя, переключайтесь на LEFT JOIN:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

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

Теперь в результате появляется и Ada - с NULL в колонке руководителя. Механика self join та же самая, просто LEFT JOIN делает то, что и должен: сохраняет все строки левой таблицы и подставляет пустоту там, где совпадения нет.

Именно такой вариант обычно и нужен, когда вы выводите список сотрудников. «Нет руководителя» - это тоже информация, и терять такую строку не стоит.

Без алиасов никак

Попробуйте написать тот же запрос без алиасов - и SQLite просто не поймёт, чего вы от него хотите:

SELECT name, manager_id FROM employees JOIN employees ON manager_id = id;
-- Ошибка: неоднозначное имя столбца: name

Каждый столбец появляется дважды - по одному из каждой копии таблицы - и SQLite не может выбрать нужный. Алиасы решают эту проблему, давая каждому экземпляру собственное имя. Подбирайте алиасы так, чтобы они отражали роль строки, а не имя таблицы:

e и m - для сотрудника и руководителя.
parent и child - для иерархий.
a и b - когда сравниваете произвольные пары.

Именно благодаря алиасу self join читается легко.

Поиск пар внутри одной таблицы

Self join - это не только про иерархии. Шаблон подходит везде, где нужно сравнить строки в пределах одной таблицы. Допустим, есть список товаров, и мы хотим найти все пары с одинаковой ценой:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL NOT NULL
);

INSERT INTO products (name, price) VALUES
    ('Кружка',    9.99),
    ('Блокнот',   9.99),
    ('Ручка',     2.50),
    ('Карандаш',  2.50),
    ('Лампа',    29.00);

SELECT a.name AS product_a, b.name AS product_b, a.price
FROM products AS a
INNER JOIN products AS b
    ON a.price = b.price
   AND a.id < b.id;

Здесь стоит обратить внимание на две вещи. Во-первых, a.price = b.price - это собственно условие совпадения. Во-вторых, a.id < b.id нужен для того, чтобы запрос не возвращал каждую пару по два раза (сначала (Кружка, Блокнот), потом (Блокнот, Кружка)) и не сводил строку саму с собой. Этот приём с < стоит запомнить - он пригодится всякий раз, когда нужно перечислить пары.

Поднимаемся на два уровня выше

Один self join покрывает один шаг по иерархии. А если хочется узнать «руководителя руководителя» для каждого сотрудника? Тогда соединяем таблицу саму с собой трижды:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

SELECT
    e.name      AS employee,
    m.name      AS manager,
    gm.name     AS grand_manager
FROM employees AS e
LEFT JOIN employees AS m  ON e.manager_id = m.id
LEFT JOIN employees AS gm ON m.manager_id = gm.id
ORDER BY e.id;

Каждый новый алиас - это ещё один шаг вверх по дереву. Для двух-трёх уровней такой подход работает нормально, но дальше всё разваливается: нужно заранее знать глубину иерархии и добавлять по join'у на каждый уровень. Именно эту стену в своё время и пробили рекурсивные CTE.

Когда self join - не лучший выбор

Self join уместен, когда в результат нужны столбцы с обеих сторон отношения. Если же задача просто отфильтровать - например, найти всех сотрудников, у которых руководитель Ада, - подзапрос обычно читается приятнее:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

SELECT name
FROM employees
WHERE manager_id = (SELECT id FROM employees WHERE name = 'Ada');

Никакой возни с алиасами, и намерение читается с первого взгляда. Простое правило: нужны ли в результате данные сразу из обеих строк? Тогда self join. А если требуется лишь значение для сравнения - берите подзапрос.

Когда же речь о иерархиях произвольной глубины (оргструктуры, файловые деревья, ветки комментариев), ни тот ни другой подход уже не тянет. Тут на сцену выходят рекурсивные CTE.

Дальше: подзапросы

Self join и подзапросы решают пересекающиеся задачи, и понимание, где какой инструмент уместен, избавит вас от долгих раздумий над SQL в будущем. На следующей странице подробно разберём подзапросы - скалярные, коррелированные и форму с IN - и покажем, в каких ситуациях каждый из них раскрывается лучше всего.

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

Что такое self join в SQLite?

Self join - это обычный JOIN, в котором таблица соединяется сама с собой. Вы задаёте ей два разных алиаса, чтобы SQLite воспринимал их как два отдельных источника строк, и сопоставляете записи по столбцу, который связывает одну строку с другой. Чаще всего так оформляют связь «родитель–потомок», например сотрудник и его руководитель.

Зачем в self join обязательно нужны алиасы?

Без алиасов SQLite просто не поймёт, к какой именно «копии» таблицы относится столбец, который вы пишете. Когда у каждого экземпляра свой алиас (скажем, e для сотрудника и m для руководителя), вы спокойно пишете e.manager_id = m.id без всякой двусмысленности. Это не вопрос стиля - без алиасов запрос просто не разберётся парсером.

Когда выбрать self join, а когда подзапрос?