Self join w SQLite: złączenie tabeli z samą sobą

Self join to po prostu złączenie z aliasami

W self join nie ma nic szczególnego. To zwykły JOIN, w którym obie strony są akurat tą samą tabelą. Cała sztuczka polega na tym, że SQLite musi jakoś odróżnić obie kopie, więc każdej nadajesz alias.

Sięgasz po niego zawsze, gdy wiersz w tabeli odwołuje się do innego wiersza w tej samej tabeli. Klasyczny przypadek: tabela employees, w której każdy wiersz ma manager_id wskazujące innego pracownika:

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;

Ada nie ma kierownika. Boris i Cleo podlegają Adzie. Diego i Esme podlegają Borisowi. Cała relacja mieści się w jednej tabeli i właśnie tu self join pokazuje, co potrafi.

Podstawowy kształt

Aby połączyć każdego pracownika z imieniem jego kierownika, złącz employees z samą sobą. Jedna kopia gra rolę "pracownika", druga "kierownika":

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 pracownik,
    m.name AS kierownik
FROM employees AS e
INNER JOIN employees AS m
    ON e.manager_id = m.id;

Czytaj to jak dwie tabele, które akurat dzielą to samo miejsce na dane. e to wiersz pracownika, m to wiersz kierownika. Warunek złączenia e.manager_id = m.id dopasowuje je do siebie: dla każdego pracownika znajdź w m wiersz, którego id pasuje do manager_id pracownika.

Zauważ, że Ady nie ma w wyniku. Jej manager_id to NULL, a INNER JOIN odrzuca wiersze bez dopasowania.

Zachowanie wierszy bez dopasowania: LEFT JOIN

Jeśli chcesz mieć w wyniku wszystkich, łącznie z osobami bez kierownika, przełącz się na 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 pracownik,
    m.name AS kierownik
FROM employees AS e
LEFT JOIN employees AS m
    ON e.manager_id = m.id
ORDER BY e.id;

Teraz Ada pojawia się z NULL w kolumnie kierownika. Ta sama mechanika self join, tylko typ złączenia robi to, co LEFT JOIN robi zawsze: zachowuje każdy wiersz z lewej strony i wstawia puste wartości tam, gdzie nie ma dopasowania.

Tej formy zwykle chcesz, gdy wyświetlasz listę osób. "Brak kierownika" to informacja, a odrzucenie wiersza już nie.

Aliasy nie są opcjonalne

Spróbuj złączenia bez aliasów, a SQLite nie będzie wiedzieć, o co ci chodzi:

SELECT name, manager_id FROM employees JOIN employees ON manager_id = id;
-- Error: ambiguous column name: name

Każda kolumna występuje dwa razy, raz z każdej kopii tabeli, i SQLite nie potrafi wybrać. Aliasy rozwiązują to, nadając każdej instancji własną nazwę. Wybieraj aliasy, które opisują rolę wiersza, a nie tabelę:

• e i m dla pracownika i kierownika.
• parent i child dla hierarchii.
• a i b, gdy porównujesz dowolne pary.

To właśnie dzięki aliasom self join czyta się przejrzyście.

Szukanie par w obrębie tabeli

Self join nie służy tylko do hierarchii. Pasuje zawsze, gdy chcesz porównać wiersze w tej samej tabeli. Oto lista produktów, w której szukamy każdej pary o tej samej cenie:

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

INSERT INTO products (name, price) VALUES
    ('Kubek',    9.99),
    ('Zeszyt',   9.99),
    ('Długopis', 2.50),
    ('Ołówek',   2.50),
    ('Lampa',   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;

Warto zauważyć dwie rzeczy. Po pierwsze, a.price = b.price to właściwy warunek dopasowania. Po drugie, a.id < b.id sprawia, że zapytanie nie zwraca każdej pary dwa razy (raz jako (Kubek, Zeszyt), drugi raz jako (Zeszyt, Kubek)) ani nie łączy każdego wiersza z nim samym. Ten trik z < warto zapamiętać: pojawia się zawsze, gdy wypisujesz pary.

Dwa poziomy w górę

Self join obsługuje jeden krok w hierarchii. Chcesz kierownika kierownika każdego pracownika? Złącz trzy razy:

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;

Każdy nowy alias oznacza jeden poziom wyżej w drzewie. Działa to dobrze przy dwóch czy trzech krokach, ale szybko się sypie: trzeba by znać głębokość hierarchii w chwili pisania zapytania i dodawać złączenie na każdy poziom. To właśnie ściana, którą przebijają rekurencyjne CTE.

Kiedy nie sięgać po self join

Self join to właściwe narzędzie, gdy w wyniku potrzebujesz kolumn z obu stron relacji. Jeśli chcesz tylko filtrować, na przykład znaleźć każdego pracownika, którego kierownikiem jest Ada, podzapytanie często czyta się lepiej:

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');

Bez żonglowania aliasami, a intencja jest oczywista. Praktyczna zasada: chcesz w wyniku danych z obu wierszy? Self join. Potrzebujesz tylko wartości do porównania? Podzapytanie.

Przy hierarchiach o dowolnej głębokości (schematy organizacyjne, drzewa plików, komentarze w wątkach) żaden z tych wzorców się nie skaluje. To już terytorium rekurencyjnych CTE.

Dalej: podzapytania

Self join i podzapytania rozwiązują częściowo te same problemy, a wiedza, które z nich pasuje, oszczędzi ci później sporo mrużenia oczu nad SQL. Następna strona dokładnie omawia podzapytania (skalarne, skorelowane i w formie IN) oraz to, gdzie każde z nich się sprawdza.