JOIN w SQL - INNER, LEFT, RIGHT i FULL na przykładach

Na rozmowach o pracę z analitykami od lat wraca jedno pytanie z SQL: „mamy tabelę klientów i tabelę zamówień - znajdź klientów, którzy nigdy nic nie zamówili”. Osoba, która rozumie JOIN-y, odpowiada w pół minuty. Osoba, która uczyła się SQL z definicji, zaczyna opowiadać o pętlach.

Ten artykuł ma jeden cel: żebyś należał do tej pierwszej grupy. Dlatego cały tekst stoi na jednym zestawie danych - sześciu klientów, siedem zamówień. Każde zapytanie pokazuję razem z wynikiem, więc prześledzisz palcem, skąd wziął się każdy wiersz.

JOIN w SQL na przykładach: najpierw problem dwóch tabel

Bazy relacyjne nie trzymają wszystkiego w jednej wielkiej tabeli. Dane o kliencie leżą w jednym miejscu, jego zamówienia w drugim - a łączy je klucz: kolumna klient_id w zamówieniach wskazuje na id w klientach. Oto nasze dwie tabele.

Tabela klienci:

id	imie	miasto
1	Anna	Kraków
2	Tomek	Warszawa
3	Ewa	Gdańsk
4	Marek	Wrocław
5	Ola	Poznań
6	Piotr	Warszawa

Tabela zamowienia:

id	klient_id	produkt	kwota
101	1	Laptop	4200
102	1	Mysz	89
103	2	Monitor	1150
104	4	Klawiatura	240
105	4	Słuchawki	310
106	7	Drukarka	620
107	2	Podkładka	35

Pytanie biznesowe brzmi banalnie: kto co zamówił? Ale żadna z tabel osobno na nie nie odpowiada. Zamówienia znają tylko numer klienta, klienci nie wiedzą nic o zamówieniach. JOIN skleja obie tabele po kluczu i zwraca pary „klient + jego zamówienie”.

Spójrz na dane jeszcze raz, bo ukryłem w nich dwie pułapki. Ewa, Ola i Piotr nie mają żadnego zamówienia. A zamówienie 106 wskazuje na klienta o id 7, którego w tabeli nie ma - powiedzmy, że konto skasowano przy migracji ze starego systemu. Takie „sieroty” to w realnych bazach codzienność, a ich los odróżnia od siebie cztery rodzaje JOIN-ów.

Jedna uwaga: SQL to dla analityka biznesowego narzędzie, nie rdzeń zawodu - na naszym job boardzie pojawia się w 15% aktywnych ofert, za BPMN (22%) i UML (20%). Ile SQL-a realnie potrzebujesz, rozpisałem w tekście o SQL dla analityka biznesowego. Tu bierzemy na warsztat klocek, o który rekruterzy pytają najchętniej.

INNER JOIN - część wspólna

INNER JOIN zwraca wyłącznie te wiersze, które mają dopasowanie po obu stronach. Klient bez zamówienia? Znika. Zamówienie bez klienta? Też znika. Zostaje część wspólna.

SELECT k.imie, z.produkt, z.kwota
FROM klienci k
INNER JOIN zamowienia z ON z.klient_id = k.id;

Wynik - 6 wierszy:

imie	produkt	kwota
Anna	Laptop	4200
Anna	Mysz	89
Tomek	Monitor	1150
Tomek	Podkładka	35
Marek	Klawiatura	240
Marek	Słuchawki	310

Prześledź: Anna ma dwa zamówienia, więc pojawia się w dwóch wierszach. Ewy, Oli i Piotra nie ma - nic nie kupili. Nie ma też Drukarki za 620 zł, bo jej klient nie istnieje. INNER JOIN nie zostawia po nich śladu ani ostrzeżenia.

Interpretacja biznesowa: „pokaż mi klientów, którzy coś kupili, razem z tym, co kupili”. Drobiazg składniowy: samo słowo JOIN bez przymiotnika oznacza właśnie INNER JOIN - to zapis domyślny i najczęstszy.

LEFT JOIN - wszyscy z lewej, nawet bez pary

LEFT JOIN zwraca wszystkie wiersze z lewej tabeli (tej przed słowem JOIN). Jeśli wiersz ma parę po prawej, dostaje ją jak przy INNER. Jeśli nie ma - i tak wchodzi do wyniku, a kolumny z prawej wypełniają się NULL-ami.

SELECT k.imie, z.produkt, z.kwota
FROM klienci k
LEFT JOIN zamowienia z ON z.klient_id = k.id;

Wynik - 9 wierszy:

imie	produkt	kwota
Anna	Laptop	4200
Anna	Mysz	89
Tomek	Monitor	1150
Tomek	Podkładka	35
Ewa	NULL	NULL
Marek	Klawiatura	240
Marek	Słuchawki	310
Ola	NULL	NULL
Piotr	NULL	NULL

Te trzy wiersze z NULL-ami to nie błąd. To informacja: „ten klient istnieje, ale nie znalazłem dla niego pary”. I właśnie na tej informacji stoi najsłynniejsze pytanie rekrutacyjne z SQL.

Klienci, którzy NIGDY nie zamówili - pytanie rekrutacyjne numer 1

Skoro klienci bez zamówień mają NULL w kolumnach z prawej tabeli, wystarczy o ten NULL zapytać:

SELECT k.imie
FROM klienci k
LEFT JOIN zamowienia z ON z.klient_id = k.id
WHERE z.id IS NULL;

Wynik: Ewa, Ola, Piotr. Trzy wiersze, zero zamówień, dokładnie to, o co pytał rekruter.

Dwa detale. Po pierwsze: IS NULL, nie = NULL - porównanie z NULL-em przez znak równości nigdy nie jest prawdziwe i zwróci pusty wynik. Po drugie, sprawdzam z.id, czyli klucz główny prawej tabeli: on nie bywa NULL-em w realnym zamówieniu, więc NULL na pewno oznacza brak pary, a nie dziurę w danych. Da się to też napisać przez NOT EXISTS, ale wersję z LEFT JOIN-em moim zdaniem warto znać z pamięci - to klasyka rozmów.

Więcej pytań, które realnie padają na rozmowach, znajdziesz w darmowym zestawie pytań rekrutacyjnych dla analityka biznesowego.

RIGHT i FULL JOIN - kiedy (rzadko) się przydają

RIGHT JOIN to lustrzane odbicie LEFT-a: zachowuje wszystkie wiersze z prawej tabeli. Na naszych danych zobaczymy wreszcie osieroconą Drukarkę:

SELECT k.imie, z.produkt, z.kwota
FROM klienci k
RIGHT JOIN zamowienia z ON z.klient_id = k.id;

Wynik - 7 wierszy: sześć znanych z INNER JOIN plus jeden nowy: NULL | Drukarka | 620. Zamówienie jest, klienta nie ma.

W praktyce RIGHT JOIN prawie nie występuje w kodzie zespołów, w których pracowałem. Powód jest prozaiczny: każdy RIGHT JOIN da się zapisać jako LEFT JOIN z zamienioną kolejnością tabel, a zapytania czyta się naturalnie od tabeli głównej. Zamiast „klienci RIGHT JOIN zamowienia” piszemy „zamowienia LEFT JOIN klienci” i mózg czytającego nie musi się przestawiać. Do tego część silników (np. SQLite przez lata) w ogóle nie wspierała RIGHT JOIN-a, co utrwaliło nawyk przepisywania na LEFT.

FULL JOIN (pełna nazwa: FULL OUTER JOIN) zachowuje wszystko z obu stron: pary tam, gdzie są, NULL-e tam, gdzie ich nie ma. Na naszych danych: 6 dopasowanych wierszy + 3 klientów bez zamówień + 1 zamówienie bez klienta = 10 wierszy. Kiedy się przydaje? Głównie przy audycie spójności danych - porównujesz dwa źródła (np. CRM i system fakturowania) i chcesz zobaczyć jednocześnie, czego brakuje po każdej ze stron. Robiłem takie rekoncyliacje przy migracjach i to jedyny scenariusz, w którym FULL JOIN regularnie zarabiał u mnie na siebie.

Cała czwórka na jednej ściądze, na naszych danych:

Rodzaj	Co zachowuje	Wierszy u nas	Typowe pytanie biznesowe
INNER JOIN	tylko pary z obu stron	6	kto co kupił?
LEFT JOIN	wszystko z lewej + pary	9	wszyscy klienci i ich zakupy (też zerowe)
RIGHT JOIN	wszystko z prawej + pary	7	jak LEFT, tylko od drugiej strony
FULL JOIN	wszystko z obu stron	10	co się nie zgadza między dwoma źródłami?

Najczęstsze wpadki z JOIN-ami

Składnia JOIN-ów to jeden wieczór nauki. Prawdziwe problemy zaczynają się, gdy zapytanie działa, zwraca wynik - i ten wynik jest po cichu błędny. Trzy wpadki, które widuję najczęściej.

Wpadka 1: duplikaty wierszy przy relacji 1:N

Chcesz policzyć, ilu klientów w każdym mieście coś kupiło. Piszesz:

SELECT k.miasto, COUNT(*) AS liczba_klientow
FROM klienci k
INNER JOIN zamowienia z ON z.klient_id = k.id
GROUP BY k.miasto;

Wynik: Kraków 2, Warszawa 2, Wrocław 2. Wygląda wiarygodnie. I jest błędny - w każdym z tych miast kupował jeden klient. Skąd dwójki? JOIN rozmnożył klientów: Anna ma dwa zamówienia, więc po złączeniu istnieje w dwóch wierszach, a COUNT(*) liczy wiersze, nie ludzi.

Poprawka - liczymy unikalnych klientów, nie wiersze:

SELECT k.miasto, COUNT(DISTINCT k.id) AS liczba_klientow
FROM klienci k
INNER JOIN zamowienia z ON z.klient_id = k.id
GROUP BY k.miasto;

Teraz: Kraków 1, Warszawa 1, Wrocław 1. Ta sama pułapka rozkłada SUM-y: dołącz drugą tabelę „jeden do wielu” i kwoty policzą się wielokrotnie. Zasada, którą wbijam każdemu na starcie: po każdym JOIN-ie sprawdź liczbę wierszy. Jeśli urosła, a się tego nie spodziewałeś, właśnie zdublowałeś dane.

Wpadka 2: warunek w WHERE zamiast w ON zjada NULL-e

Cel: wszyscy klienci plus ich zamówienia powyżej 100 zł - także ci bez żadnego zamówienia, bo raport ma pokazać pełną listę. Odruch podpowiada:

SELECT k.imie, z.produkt, z.kwota
FROM klienci k
LEFT JOIN zamowienia z ON z.klient_id = k.id
WHERE z.kwota > 100;

Wynik ma 4 wiersze: Anna z Laptopem, Tomek z Monitorem, Marek z Klawiaturą i Słuchawkami. Ewa, Ola i Piotr zniknęli. Ich wiersze miały kwota = NULL, a warunek NULL > 100 nie jest prawdziwy - WHERE je wyciął. LEFT JOIN po cichu zamienił się w INNER JOIN.

Poprawka: warunek dotyczący prawej tabeli przenosimy do ON, żeby filtrował zamówienia przed złączeniem, a nie klientów po nim:

SELECT k.imie, z.produkt, z.kwota
FROM klienci k
LEFT JOIN zamowienia z ON z.klient_id = k.id AND z.kwota > 100;

Wynik: 7 wierszy - cztery pary powyżej stówki plus Ewa, Ola i Piotr z NULL-ami. Reguła do zapamiętania: przy LEFT JOIN warunki na lewą tabelę idą do WHERE, warunki na prawą do ON. Przy INNER JOIN nie ma to znaczenia dla wyniku, dlatego błąd tak łatwo przeoczyć - ujawnia się dopiero, gdy ktoś zmieni JOIN na LEFT.

Wpadka 3: JOIN bez warunku, czyli iloczyn kartezjański

Stara składnia z przecinkiem wciąż działa i wciąż robi krzywdę:

SELECT k.imie, z.produkt
FROM klienci k, zamowienia z;

Zabrakło warunku złączenia, więc baza posłusznie sparuje każdego klienta z każdym zamówieniem: 6 razy 7, czyli 42 wiersze. Anna „kupiła” Drukarkę, Ewa „kupiła” wszystko. Na naszych tabelkach to zabawne. Na produkcyjnych, gdzie 100 tysięcy klientów spotyka milion zamówień, dostajesz 100 miliardów wierszy i telefon od administratora bazy.

Poprawka to po prostu jawny JOIN z ON - składnia, której używamy w całym tym artykule. Ma jedną przewagę: gdy zapomnisz ON, zapytanie się nie wykona, zamiast po cichu wyprodukować kosmos.

Przećwicz teraz - 4 zadania na tych samych danych

Czytanie o JOIN-ach a pisanie JOIN-ów to dwie różne umiejętności. Na platformie Analify mamy sandbox SQL działający w przeglądarce - bez instalowania czegokolwiek - i zestaw ćwiczeń SQL z automatycznym sprawdzaniem. Poniższe cztery zadania rozwiążesz tam albo w dowolnym narzędziu SQL. Skrypt z naszymi tabelami:

CREATE TABLE klienci (id INTEGER PRIMARY KEY, imie TEXT, miasto TEXT);
INSERT INTO klienci VALUES
(1, 'Anna', 'Kraków'), (2, 'Tomek', 'Warszawa'), (3, 'Ewa', 'Gdańsk'),
(4, 'Marek', 'Wrocław'), (5, 'Ola', 'Poznań'), (6, 'Piotr', 'Warszawa');

CREATE TABLE zamowienia (id INTEGER PRIMARY KEY, klient_id INTEGER,
  produkt TEXT, kwota INTEGER);
INSERT INTO zamowienia VALUES
(101, 1, 'Laptop', 4200), (102, 1, 'Mysz', 89), (103, 2, 'Monitor', 1150),
(104, 4, 'Klawiatura', 240), (105, 4, 'Słuchawki', 310),
(106, 7, 'Drukarka', 620), (107, 2, 'Podkładka', 35);

Zadania, od najłatwiejszego:

1. Rozgrzewka: wypisz imię klienta, produkt i kwotę dla każdego zamówienia, które ma istniejącego klienta. Powinno wyjść 6 wierszy.
2. Liczenie z zerami: wypisz wszystkich klientów z liczbą ich zamówień - także tych, którzy mają zero. Oczekiwany wynik: Anna 2, Tomek 2, Marek 2, reszta 0.
3. Pytanie rekrutacyjne: znajdź klientów, którzy nigdy nic nie zamówili - tym razem bez podglądania sekcji o LEFT JOIN.
4. Audyt danych: znajdź zamówienia, które wskazują na nieistniejącego klienta. Powinno wyjść jedno.

Zatrzymaj się tutaj i spróbuj samodzielnie. Serio - jedno napisane zapytanie uczy więcej niż pięć przeczytanych.

Rozwiązania

Zadanie 1 - zwykły INNER JOIN:

SELECT k.imie, z.produkt, z.kwota
FROM klienci k
JOIN zamowienia z ON z.klient_id = k.id;

Zadanie 2 - LEFT JOIN i licznik. Pułapka: COUNT(z.id), nie COUNT(*) - ten drugi policzy klientowi bez zamówień jego wiersz z NULL-ami i pokaże 1 zamiast 0:

SELECT k.imie, COUNT(z.id) AS liczba_zamowien
FROM klienci k
LEFT JOIN zamowienia z ON z.klient_id = k.id
GROUP BY k.imie;

Zadanie 3 - LEFT JOIN plus IS NULL (zapytanie z sekcji wyżej). Wynik: Ewa, Ola, Piotr.

Zadanie 4 - ten sam wzorzec, tylko odwracamy strony: teraz to zamówienia są tabelą, z której chcemy wszystko:

SELECT z.id, z.produkt, z.kwota
FROM zamowienia z
LEFT JOIN klienci k ON k.id = z.klient_id
WHERE k.id IS NULL;

Wynik: zamówienie 106, Drukarka, 620 zł. Zadania 3 i 4 to to samo narzędzie przyłożone z dwóch stron relacji - jeśli to widzisz, JOIN-y masz opanowane.

Gdzie dalej? Jeśli JOIN-y już siedzą, kolejny krok to GROUP BY z agregacjami na poważnie i podzapytania - plan nauki rozpisałem w SQL dla analityka biznesowego. A jeśli zastanawiasz się, jak głęboko w dane w ogóle powinien wchodzić BA, porównuję obie role w tekście analityk biznesowy a analityk danych. Pojęcia z tego artykułu i reszta słownictwa BA czekają w naszym słowniku analityka.

Chcesz sprawdzić, ile z tego zostało w głowie? Zrób darmowy test SQL - w przeglądarce, bez zakładania konta, wynik od razu.