Typy JOIN w SQL Wyjaśnione na Przykładach

Źle napisany JOIN nie zgłasza błędu. Wykonuje się, zwraca wynik, a wiersze są błędne: brakujący klienci, zawyżone sumy, niewidoczne osierocone rekordy. Najczęściej zapytanie dostało dokładnie to, o co prosiło, a autor wybrał zły typ join albo filtrował w złym miejscu.

Join łączy wiersze z dwóch tabel na podstawie warunku, który sam piszesz. To, które wiersze przetrwają, zależy od typu join, a sześć typów poniżej daje sześć różnych odpowiedzi z tych samych dwóch tabel.

customers zawiera pięć osób. Trzy z nich zostały polecone przez innego klienta, zapisanego w referred_by:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name        TEXT NOT NULL,
  referred_by INT REFERENCES customers(customer_id)
);

INSERT INTO customers (customer_id, name, referred_by) VALUES
  (1, 'Alice', NULL),
  (2, 'Bob',   NULL),
  (3, 'Carol', 1),
  (4, 'Dave',  1),
  (5, 'Erin',  2);

orders zawiera sześć zamówień. Pięć wskazuje na prawdziwego klienta. Szóste, 106, wskazuje na klienta 99, który został usunięty po złożeniu zamówienia, więc zamówienie nie odpowiada już nikomu:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  customer_id INT NOT NULL,
  amount      NUMERIC(10,2) NOT NULL
);

INSERT INTO orders (order_id, customer_id, amount) VALUES
  (101, 1, 50.00),
  (102, 1, 20.00),
  (103, 2, 75.00),
  (104, 4, 30.00),
  (105, 4, 15.00),
  (106, 99, 10.00);

Najszybszy sposób, by śledzić przykłady, to kontener Postgres, skonfigurowany tak jak w Czym Jest Docker Compose i Jak Go Używać. To standardowy SQL. Dwa miejsca, gdzie MySQL zachowuje się inaczej, są opisane poniżej.

INNER JOIN: tylko wiersze, które mają dopasowanie po obu stronach

1
2
3
4

SELECT c.name, o.order_id, o.amount
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.name, o.order_id;

Pięć wierszy. Carol i Erin nie mają zamówień, więc znikają. Zamówienie 106 nie ma klienta, więc też znika. JOIN i INNER JOIN znaczą to samo. INNER jest opcjonalny i większość ludzi go pomija.

Na pięciu wierszach nie widać żadnego spowolnienia. Na prawdziwej tabeli orders kolumna orders.customer_id potrzebuje indeksu, inaczej każdy join na niej skanuje całą tabelę. Jak Działają Indeksy w Bazie Danych i Kiedy Je Dodawać wyjaśnia, kiedy taki indeks się opłaca.

LEFT JOIN: każdy wiersz z lewej strony, z dopasowaniem albo bez

1
2
3
4

SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.name, o.order_id;

Każdy klient jest obecny. Carol i Erin dostają NULL w kolumnach zamówienia. To jest join, który piszesz najczęściej: “wszystkie X, z czymkolwiek z Y, co mają”.

RIGHT JOIN: każdy wiersz z prawej strony, z dopasowaniem albo bez

1
2
3
4

SELECT c.name, o.order_id, o.amount
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.customer_id
ORDER BY o.order_id;

Zamówienie 106 wraca, z NULL zamiast nazwy. To jest osierocony wiersz, który inner join skrywał, a right join (albo równoważny left join) pozwala go znaleźć celowo.

RIGHT JOIN rzadko zobaczysz w prawdziwym kodzie. a RIGHT JOIN b zwraca te same wiersze co b LEFT JOIN a, więc większość zespołów używa LEFT JOIN wszędzie i zamiast tego zmienia kolejność tabel. Przepisanie RIGHT JOIN w ten sposób to kwestia konsekwencji. Oryginał nie jest błędny.

FULL OUTER JOIN: wszystko z obu stron

1
2
3
4

SELECT c.name, o.order_id, o.amount
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.name NULLS LAST, o.order_id;

Osiem wierszy: każde dopasowanie, plus klienci bez dopasowania, plus zamówienie bez dopasowania. To właśnie czyni z niego join do rekoncyliacji, gdy chcesz wszystko, co po jednej albo drugiej stronie się nie zgadza. Poza tym przypadkiem rzadko go potrzebujesz.

MySQL nie ma FULL OUTER JOIN i też nie przyjmuje NULLS LAST. Żeby uzyskać tam te same wiersze, weź LEFT JOIN, a potem dodaj UNION ALL z RIGHT JOIN przefiltrowanym przez WHERE c.customer_id IS NULL, żeby już dopasowane wiersze się nie powtórzyły. Działa też zwykły UNION dwóch takich zapytań JOIN, ale płacisz za przebieg deduplikacji, a przy tym łączy też wiersze, które są naprawdę identyczne.

CROSS JOIN: każdy wiersz połączony z każdym wierszem

1
2
3

SELECT c.name, o.order_id
FROM customers c
CROSS JOIN orders o;

Pięciu klientów razy sześć zamówień: trzydzieści wierszy, bez żadnego warunku join. Większość zapytań z CROSS JOIN na produkcji to wypadki. Ktoś napisał FROM customers, orders i zapomniał o WHERE, które miało je połączyć. Celowo chciałbyś taki join, żeby wygenerować kombinacje, jak każdy rozmiar z każdym kolorem, albo każdy dzień zakresu dat z każdym sklepem, zanim wypełnisz prawdziwe liczby.

Jak działa self-join?

customers.referred_by wskazuje na inny wiersz tej samej tabeli. Żeby pokazać po imieniu, kto kogo polecił, połącz tabelę z samą sobą pod drugim aliasem:

1
2
3
4

SELECT c.name AS customer, r.name AS referred_by
FROM customers c
LEFT JOIN customers r ON c.referred_by = r.customer_id
ORDER BY c.name;

Ten join działa tak samo jak wcześniej. customers c i customers r to dwa aliasy, a planer traktuje je jako dwie osobne tabele, które przypadkiem zawierają te same wiersze. To celowo LEFT JOIN, żeby Alice i Bob, których nikt nie polecił, zostali w wyniku. Każda tabela, w której wiersz wskazuje na inny wiersz tej samej tabeli, działa w ten sposób: pracownicy i menedżerowie, kategorie i kategorie nadrzędne, komentarze i odpowiedzi.

Czemu klauzula WHERE zamienia LEFT JOIN w INNER JOIN?

Chcesz każdego klienta, z jego zamówieniami powyżej 25 $, jeśli jakieś ma:

1
2
3
4

SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.amount > 25;

Carol i Erin zniknęły. WHERE wykonuje się po join, a dla nich o.amount to NULL. NULL > 25 nie jest ani prawdą, ani fałszem. Jest nieznane, a WHERE zatrzymuje tylko wiersze, które są prawdziwe. LEFT JOIN je zatrzymał, a filtr je odrzucił.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- Zatrzymaj każdego klienta; filtruj tylko to, które z jego zamówień się liczą
SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.amount > 25;

-- Zatrzymuje klientów bez żadnego zamówienia, ale nie klientów, których wszystkie zamówienia są <= 25
SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.amount > 25 OR o.amount IS NULL;

Użyj pierwszej. Umieszczenie warunku w ON decyduje, które zamówienia mają prawo się dopasować, a każdy klient i tak dostaje przynajmniej jeden wiersz. Druga wersja przepuszcza tylko wiersze, w których join nic nie znalazł. Klient, którego jedyne zamówienie było na 20 $, dostaje wiersz z amount = 20.00, który nie spełnia żadnego z dwóch warunków, i ten klient znowu znika. Na tych danych obie zapytania przypadkiem zatrzymują wszystkich pięciu klientów, i tak druga przechodzi testy niezauważona.

Czemu SUM zwraca złą sumę po połączeniu dwóch tabel?

Dodaj trzecią tabelę, payments, w której jedno zamówienie może być opłacone w kilku ratach:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE TABLE payments (
  payment_id INT PRIMARY KEY,
  order_id   INT NOT NULL,
  amount     NUMERIC(10,2) NOT NULL
);

INSERT INTO payments (payment_id, order_id, amount) VALUES
  (1, 101, 25.00),
  (2, 101, 25.00),
  (3, 103, 75.00);

Zamówienie 101 jest na 50 $, opłacone w dwóch ratach po 25 $. Teraz połącz wszystkie trzy tabele i zsumuj, co zamówił każdy klient:

1
2
3
4
5

SELECT c.name, SUM(o.amount) AS total_ordered
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN payments p ON p.order_id = o.order_id
GROUP BY c.name;

Prawdziwa suma Alice to 70 $ (50.00 + 20.00). Zepsuły się dwie rzeczy. Zamówienie 101 dopasowuje się do dwóch płatności, więc join zwraca ten wiersz zamówienia dwa razy, a SUM(o.amount) dodaje jej 50 $ dwa razy. Zamówienie 102 nie ma płatności, więc inner join do payments je usuwa. Dla Alice daje to 50 + 50 + 0 = 100. Dave nie ma żadnej płatności, więc nie pojawia się w wyniku.

Zasada: kiedy agregujesz kolumnę, nie łącz tabel, których agregacja nie potrzebuje. Każdy join jeden-do-wielu może pomnożyć wiersze albo je usunąć. Jeśli chcesz tylko sumy zamówień, zostaw payments na boku:

1
2
3
4

SELECT c.name, SUM(o.amount) AS total_ordered
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.name;

Jeśli w tym samym raporcie potrzebujesz też sum płatności, zrób najpierw GROUP BY order_id na payments w podzapytaniu albo CTE. To daje jeden wiersz na zamówienie, który możesz bezpiecznie połączyć z zamówieniami.

NOT IN kontra NOT EXISTS: jak znaleźć wiersze bez dopasowania?

“Klienci bez zamówień” wygląda jak zadanie dla NOT IN:

1
2

SELECT name FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

Działa tutaj tylko dlatego, że orders.customer_id jest NOT NULL. Usuń to ograniczenie i wstaw jedno zamówienie z customer_id równym NULL, a zapytanie zwróci zero wierszy, bez żadnego błędu. NOT IN rozwija się w ciąg porównań <> połączonych AND. x <> NULL jest nieznane, więc jeden NULL czyni cały warunek nieznanym dla każdego klienta.

Dwa wzorce nie mają tego problemu:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- LEFT JOIN + IS NULL
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

-- NOT EXISTS
SELECT c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

Obie zwracają Carol i Erin, a NULL w orders.customer_id nie wpływa na żadną z nich. Żadna z nich nie buduje listy do porównania. Obie sprawdzają, klient po kliencie, czy istnieje odpowiadające zamówienie. Używaj NOT EXISTS jako domyślnego wyboru. Używaj LEFT JOIN ... IS NULL, gdy zapytanie już łączy obie tabele, żeby pobrać inne kolumny.

Kiedy join zwraca więcej wierszy, niż się oczekujesz, szukaj tabeli jeden-do-wielu między JOIN a GROUP BY. Kiedy zwraca mniej, szukaj warunku WHERE na tabeli, która może być NULL. W obu przypadkach popraw zapytanie wokół samego JOIN, zanim zmienisz jego typ.

Powiązane artykuły

• Jak Działają Indeksy w Bazie Danych i Kiedy Je Dodawać
• Czym Jest Docker Compose i Jak Go Używać
• Jak Działa Cache Redis i Jak Go Używać