Rodzaje JOIN w SQL: jak łączę tabele i pilnuję wyniku

JOIN jest jedną z tych rzeczy, które na początku wyglądają niewinnie. Mam klientów w jednej tabeli, zamówienia w drugiej, łączę je po identyfikatorze i gotowe. Potem okazuje się, że z raportu zniknęła część klientów, a pozostali wydali dwa razy więcej. Dział sprzedaży już otwiera szampana, ja jeszcze sprawdzam zapytanie.

Dlatego rodzaje JOIN poznaję razem z pytaniem o wynik. Chcę wiedzieć, jakie wiersze zostaną zachowane, ile razy może wystąpić jeden rekord i co oznacza brak dopasowania. Sama umiejętność napisania LEFT JOIN nie chroni przed bardzo przekonującą bzdurą.

Pokażę cały mechanizm na małym syntetycznym zbiorze. Kod działa w PostgreSQL, a tabele tymczasowe tworzę w osobnej sesji do ćwiczeń. W przykładach celowo zostawiam klienta bez zakupów oraz zamówienia bez pasującego klienta. Dzięki temu różnice między złączeniami rzeczywiście widać.

Film dotyczy przykładów z MySQL. Rozbudowane ćwiczenie poniżej przygotowałem dla PostgreSQL, włącznie ze składnią FULL OUTER JOIN. Przy pracy w innej bazie sprawdzam jej obsługiwane złączenia i składnię, zanim przeniosę zapytanie.

Zanim wybiorę JOIN, ustalam znaczenie wiersza

Zaczynam od dwóch zdań: jeden wiersz tabeli klientów oznacza klienta, a jeden wiersz tabeli zamówień oznacza zamówienie. To brzmi jak oczywistość do pominięcia. Właśnie takie oczywistości lubią wracać jako trzygodzinne spotkanie o rozbieżnościach w przychodzie.

Klient może mieć wiele zamówień. Jeżeli połączę te tabele, jego nazwisko pojawi się przy każdym pasującym zamówieniu. Nie oczekuję już jednego wiersza na klienta. Oczekuję jednego wiersza na dopasowaną parę, chyba że później wykonam agregację.

Warunek ON c.customer_id = o.customer_id opisuje, kiedy dwa wiersze pasują do siebie. Nazwy kolumn mogą być różne, a warunek może obejmować kilka pól. Przy identyfikatorach unikalnych tylko wewnątrz firmy potrzebowałbym również identyfikatora firmy. Sam numer 17 nie musi oznaczać tego samego klienta w każdym systemie.

Sprawdzam też typy danych i znaczenie klucza. Numer zamówienia oraz numer klienta mogą mieć tę samą wartość liczbową, ale połączenie ich niczego sensownego nie opisuje. Silnik nie zna mojego zamiaru. Jeśli dostanie poprawny warunek, wykona go z godną podziwu obojętnością.

Dokładną semantykę złączeń opisuje dokumentacja PostgreSQL. Ja na początku dopisuję oczekiwany poziom wyniku i kilka przypadków, które powinny się w nim znaleźć. Dopiero potem wybieram słowo między nazwami tabel.

Przygotowuję dane z brakami i wieloma dopasowaniami

Poniższy blok uruchamiam raz w nowej sesji. Są trzy osoby i pięć zamówień. Ada ma dwa zamówienia, Ben jedno, Celina żadnego. Zamówienie 104 wskazuje klienta 9, którego tutaj brakuje, a 105 nie ma przypisanego identyfikatora.

-- example: fixture
CREATE TEMP TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    customer_name TEXT NOT NULL
);
CREATE TEMP TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    amount INTEGER NOT NULL,
    status TEXT NOT NULL
);
INSERT INTO customers VALUES (1, 'Ada'), (2, 'Ben'), (3, 'Celina');
INSERT INTO orders VALUES
    (101, 1, 100, 'completed'),
    (102, 1, 50, 'cancelled'),
    (103, 2, 80, 'completed'),
    (104, 9, 40, 'completed'),
    (105, NULL, 20, 'completed');

Celowo nie definiuję klucza obcego w orders, bo chcę pokazać niespójny wycinek danych. W poprawnie ograniczonej relacji aktywny klucz obcy nie pozwoliłby zapisać nieistniejącego klienta 9. Takie braki można jednak spotkać w eksportach, tabelach pośrednich albo przy łączeniu systemów.

Kwoty są umownymi jednostkami, bez rozważań o walutach i podatkach. Na tym etapie interesuje mnie zachowanie wierszy. Status anulowania przyda się później, kiedy rozdzielę dopasowanie od filtrowania. Najpierw obejrzę wszystkie zamówienia, również anulowane.

Mały zbiór ma dużą zaletę: potrafię przewidzieć wynik bez uruchamiania kodu. Przed każdym zapytaniem zapisuję oczekiwaną liczbę wierszy. To prosty test rozumienia mechanizmu, którego nie zastąpi zielony komunikat o poprawnym wykonaniu.

INNER JOIN: interesują mnie pasujące pary

-- example: inner
SELECT c.customer_id, c.customer_name, o.order_id, o.amount
FROM customers AS c
INNER JOIN orders AS o ON o.customer_id = c.customer_id
ORDER BY o.order_id;

Otrzymuję zamówienia 101, 102 i 103. Ada występuje dwa razy, Ben raz. Celiny nie ma, bo nie ma zamówienia. Rekordy 104 i 105 odpadają, bo żaden klient nie spełnia dla nich warunku złączenia. Wynik ma trzy wiersze.

Nie opisuję tego jako „części wspólnej klientów i zamówień”. To różne rodzaje obiektów. Myślę o parach wierszy spełniających warunek. Ten sposób myślenia lepiej przygotowuje do sytuacji, w której po obu stronach występuje kilka rekordów z tym samym kluczem.

INNER JOIN wybieram, gdy brak dopasowania rzeczywiście wyklucza rekord z odpowiedzi. Na przykład chcę obejrzeć zamówienia z rozpoznanym klientem. Jeśli mam przygotować raport wszystkich zamówień, ten sam kod będzie już niepełny. Poprawność zależy od pytania, a nie od popularności danego rodzaju JOIN.

Przed dalszym liczeniem porównałbym liczbę i sumę zamówień wejściowych z tymi po złączeniu. Tu suma wszystkich kwot wynosi 290, a kwot z dopasowanym klientem 230. Różnica 60 jest wyjaśniona przez zamówienia 104 i 105. Potrafię wskazać konkretne rekordy, zamiast zgadywać, że „pewnie jakieś dane się nie zgrały”.

LEFT i RIGHT JOIN: wybieram stronę, którą zachowuję

-- example: left
SELECT c.customer_id, c.customer_name, o.order_id, o.amount
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;

Wynik ma cztery wiersze. Ada nadal ma dwa dopasowania, Ben jedno, a Celina dostaje wiersz z pustą stroną zamówienia. W kolumnach order_id i amount zobaczę NULL. LEFT JOIN zachowuje każdego klienta, ale nie obiecuje dokładnie jednego wiersza dla każdej osoby.

NULL oznacza tutaj brak wartości po prawej stronie złączenia. Nie zamieniam automatycznie wszystkich takich pól na zero. Brak identyfikatora zamówienia i kwota równa zero to różne informacje. Dopiero w gotowym raporcie liczebności mogę sensownie pokazać zero zakupów.

RIGHT JOIN zachowuje stronę prawą. Poniżej zapisuję ten sam zamiar z odwrotną kolejnością tabel, żeby łatwo porównać wyniki:

-- example: right
SELECT c.customer_id, c.customer_name, o.order_id, o.amount
FROM orders AS o
RIGHT JOIN customers AS c ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;

Otrzymuję dokładnie te same cztery wiersze. Na co dzień często wybieram LEFT JOIN, bo łatwiej mi czytać łańcuch z jedną konsekwentnie zachowywaną stroną. To preferencja czytelności, bez obietnicy automatycznej przewagi wydajnościowej.

Filtr w ON i filtr w WHERE zmieniają pytanie

Załóżmy, że chcę wszystkich klientów i tylko zakończone zamówienia. Warunek statusu dodaję do dopasowania:

-- example: left-completed
SELECT c.customer_id, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
   AND o.status = 'completed'
ORDER BY c.customer_id, o.order_id;

Mam trzy wiersze: Ada z 101, Ben z 103 i Celina bez dopasowania. Gdybym ten sam warunek statusu przeniósł do WHERE, Celina zostałaby odrzucona. Dla jej pustej strony porównanie z tekstem completed nie daje wartości prawda.

-- example: where-completed
SELECT c.customer_id, o.order_id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.status = 'completed'
ORDER BY c.customer_id, o.order_id;

Teraz zostały dwa wiersze. Nie zapamiętuję hasła, że WHERE zawsze psuje LEFT JOIN. Filtr na kolumnie klienta może być dokładnie tym, czego potrzebuję. Sprawdzam konkretny warunek i to, czy odrzuca wiersze z brakującym dopasowaniem.

FULL JOIN: chcę zobaczyć braki po obu stronach

-- example: full
SELECT c.customer_id AS known_customer_id,
       o.customer_id AS order_customer_id,
       o.order_id
FROM customers AS c
FULL OUTER JOIN orders AS o ON o.customer_id = c.customer_id
ORDER BY COALESCE(c.customer_id, o.customer_id), o.order_id;

Wynik ma sześć wierszy: trzy dopasowane zamówienia, Celinę bez zamówienia oraz dwa zamówienia bez rozpoznanego klienta. Zostawiam osobno oba identyfikatory. Dzięki temu nie ukrywam różnicy między nieistniejącym klientem 9 a brakiem identyfikatora w zamówieniu 105.

FULL JOIN przydaje mi się przy uzgadnianiu danych. Porównuję na przykład dwa zestawienia i chcę zobaczyć pozycje obecne tylko w jednym z nich. Zanim jednak zacznę uzgadniać sumy, sprawdzam unikalność klucza po obu stronach. Dwie tabele z powtarzającymi się kluczami mogą pomnożyć rekordy również przy FULL JOIN.

Porównanie NULL = NULL nie staje się prawdą tylko dlatego, że po obu stronach czegoś brakuje. Dokumentacja porównań PostgreSQL opisuje też IS NOT DISTINCT FROM, gdy potrzebuję porównania traktującego dwa braki jak równe. Używam go wyłącznie wtedy, gdy ma to sens w modelu. Łączenie wszystkich nieznanych klientów ze sobą brzmi jak przepis na wyjątkowo towarzyski błąd.

CROSS JOIN: każda kombinacja, świadomie

-- example: cross
SELECT c.customer_id, o.order_id
FROM customers AS c
CROSS JOIN orders AS o
ORDER BY c.customer_id, o.order_id;

Trzech klientów razy pięć zamówień daje piętnaście wierszy. Każda osoba zostaje zestawiona z każdym zamówieniem, niezależnie od tego, kto je złożył. Do analizy zakupów klientów taki wynik byłby błędny. Do nauki iloczynu kartezjańskiego jest bardzo uczciwy.

Sensowne zastosowanie to pełna siatka kategorii i okresów. Jeśli raport ma pokazać każdy produkt w każdym miesiącu, nawet bez sprzedaży, najpierw przygotowuję kombinacje, potem dołączam rzeczywiste wyniki. Wcześniej ograniczam produkty i miesiące do potrzebnego zakresu.

Liczę rozmiar przed wykonaniem. Tysiąc produktów i dwanaście miesięcy da dwanaście tysięcy kombinacji. Milion klientów i milion zdarzeń to już bilion. Baza nie potrzebuje motywacyjnego cytatu, żeby wykonać tę różnicę. Potrzebuje rozsądnego zapytania.

Gdy złączenie nagle zwraca ogromny wynik, sprawdzam również brakujący fragment klucza i warunek, który jest zawsze prawdziwy. Nie każdy przypadkowy iloczyn ma w kodzie napis CROSS JOIN. Czasem wygląda zupełnie profesjonalnie, dopóki nie spojrzę na liczby.

Wiele dopasowań: skąd bierze się podwójny przychód

Dołączę teraz tagi klientów. Ada ma dwa, Ben jeden. To normalna relacja, ale każda kwota Ady zostanie powtórzona dla każdego tagu.

-- example: multiplied
WITH customer_tags(customer_id, tag) AS (
    VALUES (1, 'new'), (1, 'vip'), (2, 'new')
)
SELECT o.order_id, o.amount, t.tag
FROM orders AS o
JOIN customer_tags AS t ON t.customer_id = o.customer_id
ORDER BY o.order_id, t.tag;

Otrzymuję pięć wierszy, a suma widocznych kwot wynosi 380 zamiast 230 dla tych samych trzech zamówień. Kwoty 100 i 50 występują po dwa razy. Sam JOIN działa zgodnie z definicją. Błąd zaczyna się przy potraktowaniu nowego wyniku jak tabeli z jednym wierszem na zamówienie.

Nie leczę tego odruchem SELECT DISTINCT. Dwa różne zamówienia mogą mieć identyczną kwotę, więc SUM(DISTINCT amount) potrafi usunąć prawdziwą sprzedaż. Z kolei DISTINCT na wszystkich kolumnach zachowa oba tagi. Najpierw ustalam, czego potrzebuje pytanie.

Jeśli chcę jedynie zamówienia klientów mających dowolny tag, wystarczy EXISTS. Sprawdzam istnienie pasującego rekordu, bez dokładania go do wyniku:

-- example: exists
WITH customer_tags(customer_id, tag) AS (
    VALUES (1, 'new'), (1, 'vip'), (2, 'new')
)
SELECT o.order_id, o.amount
FROM orders AS o
WHERE EXISTS (
    SELECT 1 FROM customer_tags AS t
    WHERE t.customer_id = o.customer_id
)
ORDER BY o.order_id;

Wracam do trzech zamówień i sumy 230. Jeśli potrzebuję raportu według tagów, muszę osobno zdecydować, jak przypisywać kwoty do nakładających się grup. SQL sam nie rozstrzygnie, czy przychód ma należeć do każdego tagu w całości, czy być dzielony.

Sprawdzam cały klucz i moment obowiązywania danych

W ćwiczeniu identyfikator klienta jest unikalny w całej tabeli. W rzeczywistym modelu warto to potwierdzić. Wyobraź sobie syntetyczny przykład dwóch sklepów, które niezależnie numerują klientów. Klient 1 w sklepie A i klient 1 w sklepie B oznaczają inne osoby. Połączenie wyłącznie po numerze może przypisać zamówienie obu osobom, choć składnia zapytania pozostaje poprawna.

W takim przypadku warunek dopasowania powinien uwzględniać także sklep, a odpowiednia kombinacja kolumn musi być unikalna tam, gdzie oczekuję pojedynczego rekordu. Najpierw sprawdzam model danych i reguły systemu. Dokładanie kolejnych warunków na chybił trafił, aż liczba wierszy zacznie wyglądać rozsądnie, daje mało przekonującą gwarancję jakości.

Podobny problem pojawia się, gdy tabela zawiera historię zmian. Jeden klient może mieć kilka kolejnych adresów albo statusów. Jeśli chcę odtworzyć sytuację w dniu zakupu, potrzebuję wersji obowiązującej właśnie wtedy. Jeśli chcę raport według aktualnego statusu, wybieram inny rekord. Oba pytania mają sens, lecz wymagają innej logiki dołączenia danych.

Przy przedziałach obowiązywania sprawdzam również granice dat. Dwa okresy nie powinny przypadkiem obejmować tej samej chwili, jeśli model zakłada jedną obowiązującą wersję. W przeciwnym razie pojedyncze zamówienie znów dostanie kilka dopasowań. Zanim przejdę do wielkiego raportu, testuję zdarzenie dokładnie na granicy okresu i zdarzenie bez pasującej wersji.

Te przykłady pokazują, dlaczego sama znajomość nazw złączeń nie wystarcza. Najważniejsza część warunku bywa ukryta w definicji klucza albo w pytaniu o czas. Baza wykona również błędnie opisany zamiar. Nie zgłosi błędu tylko dlatego, że wynik jest biznesowo bez sensu.

Czytam długie zapytanie po jednym złączeniu

Gdy dostaję zapytanie z kilkoma tabelami i podejrzanym wynikiem, zaczynam od jego podstawy. Sprawdzam liczbę zamówień i sumę kwot przed pierwszym JOIN. Następnie dokładam kolejne złączenie i ponawiam kontrolę. Szukam dokładnego miejsca, w którym zmienia się liczba reprezentowanych obiektów albo suma.

Jeśli liczba wierszy wzrosła, oglądam jeden konkretny identyfikator. Dwa wiersze z tym samym zamówieniem często wyjaśniają więcej niż cały eksport. Mogę zobaczyć różne tagi, wersje historyczne albo przypadkiem połączone sklepy. Dopiero znając przyczynę, decyduję, czy wielokrotność jest oczekiwana i jak ma wejść do raportu.

Jeśli wiersze zniknęły, sprawdzam niedopasowane klucze i filtry. Zwracam uwagę na typy danych, format oraz brakujące wartości. Nie usuwam automatycznie spacji z każdej kolumny ani nie zamieniam wszystkiego na tekst. Najpierw ustalam, czy problem pochodzi z jakości wejścia, czy z niewłaściwego wyboru pola do połączenia.

Zostawiam w roboczym wyniku identyfikatory z obu stron. Ułatwiają prześledzenie dopasowania. W finalnej tabeli dla odbiorcy mogę ukryć techniczne kolumny, ale podczas diagnozy są bardzo przydatne. Podobnie unikam szerokiego SELECT *, gdy powtarzające się nazwy utrudniają zrozumienie, skąd pochodzi dana wartość.

Dopiero po potwierdzeniu logiki zajmuję się wydajnością. Sprawdzam rozmiar wejścia, potrzebne kolumny, filtry oraz plan wykonania. Indeks może pomóc znaleźć dopasowania, ale nie naprawi błędnej relacji. Szybsze policzenie zawyżonego przychodu nadal zostawia mnie z zawyżonym przychodem, tylko trochę wcześniej.

Taką diagnozę zapisuję krótko obok rozwiązania. Wystarczy informacja, że tabela tagów ma wiele wierszy na klienta, a raport wymaga jednego wiersza na zamówienie. Kolejna osoba nie musi wtedy ponownie odkrywać tej samej pułapki. Za miesiąc tą kolejną osobą bardzo często jestem ja.

Kontroluję wynik przed zrobieniem wykresu

Mój prosty raport klientów uwzględnia tylko zakończone zamówienia i zachowuje osoby bez zakupów. Liczę niepuste identyfikatory zamówień, a brak sumy zamieniam na zero dopiero w końcowej miarze.

-- example: summary
SELECT c.customer_id,
       COUNT(o.order_id) AS order_count,
       COALESCE(SUM(o.amount), 0) AS total_amount
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
   AND o.status = 'completed'
GROUP BY c.customer_id
ORDER BY c.customer_id;

Wyniki to: Ada jedno zamówienie i 100, Ben jedno i 80, Celina zero i zero. COUNT(*) policzyłby Celinie wiersz utworzony przez LEFT JOIN. Różnicę między liczeniem wszystkich wierszy i niepustych wartości wyjaśnia opis funkcji agregujących.

Sprawdzam trzy rzeczy: liczbę unikalnych obiektów, sumę miary i listę brakujących dopasowań. Każda odpowiada na inne pytanie. Sama zgodność sumy nie dowodzi, że wszystkie rekordy trafiły tam, gdzie powinny. Dwa błędy mogą się nawet przypadkiem znieść.

Jako ćwiczenie dodałbym drugie zakończone zamówienie Ady z kwotą 100. Poprawna suma Ady wzrośnie do 200, a liczba zamówień do dwóch. Następnie dodałbym czwartego klienta bez zakupów. Raport powinien zachować go z zerami. Tak sprawdzam logikę zamiast podziwiać, że kod nadal się wykonuje.

Na końcu zapisałbym obok zapytania, kogo obejmuje populacja, jakie statusy uwzględniam i co oznacza każdy wiersz. Do dalszej praktyki polecam mój histogram w SQL, gdzie z poziomu klienta przechodzę do rozkładu zakupów. Dopiero z poprawnie połączonych danych buduję KPI. Wtedy wzrost sprzedaży ma szansę wydarzyć się również poza zapytaniem.