SELF JOIN w SQL: pracownicy, przełożeni i unikalne pary

SELF JOIN to użycie tej samej tabeli po obu stronach złączenia. Jedna tabela przechowuje pracowników, a jej dwa aliasy pozwalają potraktować wiersze raz jako pracowników, raz jako przełożonych. Nie piszesz osobnego słowa kluczowego SELF JOIN. Wybierasz zwykłe INNER JOIN albo LEFT JOIN i określasz, które wiersze mają do siebie pasować.

W tym haśle przechodzę od relacji pracownik-przełożony do par osób z tego samego zespołu. Najważniejsze pytanie brzmi: czy wynik ma zachować osobę, dla której nie ma dopasowania? Ta decyzja zmienia odpowiedni typ złączenia i miejsce, w którym umieścisz filtr. Wszystkie poniższe zapytania i tabele wynikowe sprawdzono w SQLite 3.53.4 przez Node 24. Dane są syntetyczne.

Jedna tabela, dwie role

Przygotuj nową, pustą bazę i wykonaj ten blok:

-- example: fixture
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER,
    team TEXT NOT NULL
);
INSERT INTO employees VALUES
    (1, 'Ada', NULL, 'core'),
    (2, 'Ben', 1, 'ops'),
    (3, 'Cleo', 1, 'ops'),
    (4, 'Dan', 2, 'ops'),
    (5, 'Eva', 99, 'qa');

id jednoznacznie identyfikuje osobę, a manager_id wskazuje ID jej bezpośredniego przełożonego. Ada nie ma przełożonego w tych danych. Eva ma wpisane 99, którego nie ma w kolumnie id. Celowo zostawiam błędne powiązanie, żeby pokazać różnicę między brakiem przełożonego a brakującym rekordem.

Dlatego ćwiczenie nie deklaruje klucza obcego dla manager_id. W rzeczywistym modelu rozważ taki klucz i jego egzekwowanie. Zależnie od reguł systemu zapobiegnie to zapisowi nieistniejącego ID. Sama poprawność odwołania nie wyklucza jednak cykli w hierarchii ani sytuacji, w której ktoś wskazuje siebie.

Alias e oznacza tutaj pracownika, a m przełożonego. To dwie nazwy użycia tabeli w jednym zapytaniu. Nie tworzymy kopii danych ani drugiej trwałej tabeli. Kwalifikowanie kolumn, na przykład e.id i m.id, wyjaśnia, z której roli korzystamy, i usuwa niejednoznaczność nazw.

LEFT JOIN zachowuje osobę bez dopasowania

Chcę zobaczyć wszystkich pracowników i, jeśli istnieje dopasowanie, imię przełożonego:

-- example: left
SELECT e.id, 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;

Wynik ma pięć wierszy. Ada pozostaje na liście z NULL po stronie przełożonego. Eva również pozostaje, choć przyczyna braku dopasowania jest inna. Wiersz Dana wskazuje Bena, bo 2 z e.manager_id pasuje do 2 z m.id.

NULL w tabeli wynikowej oznacza brak wartości SQL, a nie tekst składający się z czterech liter. Warunek równości nie dopasowuje NULL do NULL. Przy LEFT JOIN brak dopasowania oznacza uzupełnienie kolumn prawej strony wartościami NULL, co opisuje dokumentacja złączeń SQLite.

Tutaj każda osoba pojawia się dokładnie raz, ponieważ prawa strona jest dopasowywana po unikalnym kluczu m.id. Złączenie po imieniu nie dawałoby tej gwarancji: dwie osoby o tym samym imieniu mogłyby pomnożyć wiersze. Nie usuwaj takiego efektu od razu przez DISTINCT; najpierw sprawdź klucz i zamierzoną relację.

INNER JOIN zwraca tylko dopasowane relacje

Jeśli interesują mnie wyłącznie osoby z istniejącym rekordem przełożonego, zmieniam typ złączenia:

-- example: inner
SELECT e.id, e.name AS employee, m.name AS manager
FROM employees AS e
INNER JOIN employees AS m ON e.manager_id = m.id
ORDER BY e.id;

Zostają trzy wiersze. Ada i Eva odpadają, bo dla żadnej nie znaleziono pasującego m.id. Wynik nie oznacza, że osoby zniknęły z tabeli. Zmienił się tylko warunek obecności w zestawieniu.

W raporcie kontroli jakości danych zwykle potrzebujesz właśnie tych pominiętych przypadków, dlatego wygodniejszy będzie LEFT JOIN. W raporcie przedstawiającym wyłącznie istniejące relacje przełożony-pracownik INNER JOIN może być zgodny z celem. Najpierw nazwij zbiór, który chcesz otrzymać, potem wybierz operator.

Odróżnij brak przełożonego od błędnego ID

Samo wyświetlenie m.name nie mówi, skąd wziął się NULL. Sprawdzam więc wartość po lewej stronie oraz klucz dopasowania po prawej:

-- example: classification
SELECT e.id, e.manager_id,
       CASE
           WHEN e.manager_id IS NULL THEN 'no_manager'
           WHEN m.id IS NULL THEN 'missing_reference'
           ELSE 'matched'
       END AS link_status
FROM employees AS e
LEFT JOIN employees AS m ON e.manager_id = m.id
ORDER BY e.id;

no_manager oznacza puste wskazanie w wierszu pracownika. missing_reference oznacza wskazanie, którego nie dało się znaleźć. To techniczna klasyfikacja przykładu; nie rozstrzyga, czy brak przełożonego jest dozwolony dla danej roli.

Testuję m.id IS NULL, ponieważ klucz rekordu przełożonego nie może być NULL. W modelu dopuszczającym brak imienia test m.name IS NULL mieszałby dwa przypadki: niedopasowaną osobę oraz poprawnie dopasowaną osobę bez imienia. Dobrze wybrana kolumna kontrolna pozwala uniknąć takiego błędu.

Filtr w ON i filtr w WHERE dają różne wyniki

Załóżmy, że chcę pokazać wszystkich pracowników, ale imię przełożonego tylko wtedy, gdy ten należy do zespołu core. Warunek na przełożonego umieszczam w ON:

-- example: on-filter
SELECT e.id, e.name AS employee, m.name AS manager
FROM employees AS e
LEFT JOIN employees AS m
    ON e.manager_id = m.id AND m.team = 'core'
ORDER BY e.id;

Dan nadal jest na liście, lecz jego przełożony Ben należy do ops, więc nie spełnia warunku dopasowania. Dostajemy NULL po prawej stronie. Warunek nie bada zespołu Dana; bada m.team, czyli zespół przełożonego.

Teraz przenoszę filtr do WHERE:

-- example: where-filter
SELECT e.id, e.name AS employee, m.name AS manager
FROM employees AS e
LEFT JOIN employees AS m ON e.manager_id = m.id
WHERE m.team = 'core'
ORDER BY e.id;

Tym razem wynik zachowuje tylko osoby z dopasowanym przełożonym z core. Wiersze z NULL w m.team nie spełniają równości i odpadają. Skutek zależy od konkretnego predykatu: nie każdy filtr w WHERE zmienia charakter zewnętrznego złączenia w ten sam sposób. Przykładowo warunek szukający m.id IS NULL wybierałby właśnie niedopasowane wiersze.

Rozróżnienie ON i WHERE opisuje także dokumentacja PostgreSQL. Korzystam z niej jako źródła wyjaśnienia; wykonanie przykładów w tym materiale sprawdzono w SQLite, bez deklaracji testu w PostgreSQL.

Unikalne pary osób z jednego zespołu

SELF JOIN przydaje się również wtedy, gdy nie masz kolumny wskazującej przełożonego. Chcę utworzyć każdą parę osób należących do tego samego zespołu dokładnie raz:

-- example: pairs
SELECT a.id AS first_id, b.id AS second_id, a.team
FROM employees AS a
INNER JOIN employees AS b
    ON a.team = b.team AND a.id < b.id
ORDER BY a.id, b.id;

Warunek a.team = b.team łączy osoby z tego samego zespołu. Warunek a.id < b.id wyklucza parę osoby z nią samą i wybiera jeden z dwóch możliwych kierunków. Dostaję parę 2-3, ale już nie 3-2. To dobry wzorzec dla par nieuporządkowanych, w których zamiana stron niczego nie zmienia.

Samo a.id <> b.id usunęłoby pary z samym sobą, lecz zachowałoby oba kierunki. Z kolei pominięcie warunku na ID dałoby w trzyosobowym zespole dziewięć kombinacji, w tym trzy połączenia osoby z samą sobą. Zastanów się więc, czy twój problem dotyczy relacji kierunkowej, czy par bez wyróżnionej strony.

Dla zespołu liczącego n osób liczba takich nieuporządkowanych par wynosi n × (n - 1) / 2. Trzy osoby dają trzy pary, cztery osoby sześć. Przy dużych grupach wynik może szybko rosnąć. To liczba wierszy wynikająca z definicji zadania, nie uniwersalna ocena szybkości konkretnego silnika. Plan zapytania i czas wykonania trzeba sprawdzić na własnych danych.

Granice przykładu i szybkie sprawdzenie wyniku

Jedno złączenie pokazuje jeden poziom relacji: pracownik i jego bezpośredni przełożony. Nie rozwija automatycznie całej hierarchii. Do przejścia przez dowolną liczbę poziomów potrzebujesz innego rozwiązania, na przykład rekurencyjnego CTE, oraz zasad obsługi cykli.

Przed użyciem wzorca w raporcie sprawdź trzy rzeczy: znaczenie obu aliasów, unikalność klucza dopasowania i oczekiwaną liczbę wierszy. Potem dodaj przypadek bez powiązania. Jeśli raport ma obejmować wszystkich pracowników, po takim teście nadal powinieneś widzieć wiersz tej osoby.

Nazwy aliasów w przykładach są prostymi identyfikatorami bez cudzysłowów. Apostrofy służą tu do zapisu wartości tekstowych, takich jak 'core'. Nie korzystam z akceptowanych przez niektóre silniki wyjątków pozwalających zapisywać aliasy jak literały tekstowe.

Jeśli dopiero utrwalasz podstawy łączenia danych, zobacz też przykład CROSS JOIN. Porównanie liczby kombinacji ułatwia zauważenie, co faktycznie ogranicza warunek ON w samodzielnym złączeniu tabeli.