MySQL OUTER JOIN - LEFT JOIN, RIGHT JOIN i brakujące dane

Gdy raport ma pokazać wszystkich klientów, także tych bez zamówień, zwykły INNER JOIN szybko okazuje się zbyt wąski. Właśnie wtedy przydaje się mechanizm mysql outer join, czyli złączenie zewnętrzne, które zachowuje rekordy bez dopasowania. Pokażę, jak działają LEFT JOIN i RIGHT JOIN, gdzie umieszczać warunki, jak wyszukiwać brakujące dane oraz dlaczego MySQL nie oferuje bezpośredniego FULL OUTER JOIN.

Jak działa mysql outer join w praktyce

Złączenie zewnętrzne łączy dane z dwóch tabel, ale nie odrzuca automatycznie rekordów, które nie mają odpowiednika. W przypadku LEFT JOIN najważniejsza jest tabela zapisana po lewej stronie słowa JOIN. MySQL zwróci z niej każdy wiersz, nawet gdy po prawej stronie nie znajdzie pasującego rekordu.

Załóżmy, że mamy tabele customers i orders. Chcemy wyświetlić klientów wraz z ich zamówieniami:

SELECT
    c.id,
    c.name,
    o.id AS order_id,
    o.order_date
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.id;

Klient bez zamówień nadal pojawi się w wyniku, ale kolumny pochodzące z tabeli orders będą miały wartość NULL. To właśnie odróżnia LEFT JOIN od INNER JOIN, który taki rekord całkowicie pominąłby.

W praktyce najczęściej stosuję zapis LEFT JOIN, a słowo OUTER pomijam. Formy LEFT JOIN i LEFT OUTER JOIN oznaczają to samo. Analogicznie działają RIGHT JOIN oraz RIGHT OUTER JOIN.

LEFT JOIN i RIGHT JOIN

RIGHT JOIN zachowuje wszystkie wiersze z tabeli po prawej stronie. Ten zapis jest poprawny, ale przy dłuższych zapytaniach zwykle trudniej się go czyta. Najczęściej zamieniam go na LEFT JOIN, przestawiając tabele, ponieważ wtedy kierunek zachowywania rekordów jest widoczny od razu.

Warunek ON decyduje o tym, co zostanie dopasowane

W złączeniu zewnętrznym warunek ON określa, które rekordy z drugiej tabeli pasują do bieżącego wiersza. Nie oznacza jednak, że rekord niespełniający tego warunku zniknie z tabeli bazowej. Dla LEFT JOIN zostanie zachowany, a po prawej stronie pojawią się wartości NULL.

Możemy na przykład pobrać klientów i tylko ich opłacone zamówienia:

SELECT
    c.name,
    o.id AS order_id,
    o.total
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.id
   AND o.status = 'paid';

Klient z zamówieniem o statusie pending nadal znajdzie się w wyniku. Nie zobaczymy jednak tego zamówienia, ponieważ nie spełnia warunku z ON. To często dokładnie to, czego potrzebuje raport.

Pułapka z warunkiem WHERE

Ten zapis wygląda podobnie, ale działa inaczej:

SELECT
    c.name,
    o.id AS order_id,
    o.total
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.id
WHERE o.status = 'paid';

Warunek w WHERE odrzuci wiersze, dla których o.status ma wartość NULL. W efekcie klienci bez zamówień znikną, a zapytanie zacznie zachowywać się jak INNER JOIN. To jeden z najczęstszych błędów przy pracy ze złączeniami zewnętrznymi.

Jeżeli filtr musi pozostać w WHERE, można jawnie uwzględnić brak dopasowania:

WHERE o.status = 'paid'
   OR o.id IS NULL

Ja zwykle umieszczam warunki dotyczące prawej tabeli w ON, jeśli chcę zachować komplet rekordów z lewej strony. Dzięki temu intencja zapytania jest czytelniejsza i trudniej przypadkiem zgubić dane.

Praktyczne wzorce użycia złączeń zewnętrznych

Klienci bez zamówień

Jednym z najbardziej użytecznych zastosowań jest wyszukiwanie rekordów, które nie mają powiązania w drugiej tabeli. Łączymy tabele przez LEFT JOIN, a potem sprawdzamy, czy identyfikator po prawej stronie pozostał pusty:

SELECT
    c.id,
    c.name,
    c.email
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.id
WHERE o.id IS NULL;

Otrzymamy wyłącznie klientów bez zamówień. Ten wzorzec nazywa się czasem anti-join, czyli wyszukiwaniem rekordów pozbawionych odpowiednika. Stosuję go także do znajdowania produktów bez kategorii, użytkowników bez aktywności albo faktur bez płatności.

Lista produktów i liczba zamówień

Przy agregowaniu danych trzeba uważać na funkcję COUNT. Jeśli chcemy pokazać również produkty, których nikt nie zamówił, należy policzyć konkretną kolumnę z tabeli połączonej:

SELECT
    p.id,
    p.name,
    COUNT(oi.id) AS order_count
FROM products AS p
LEFT JOIN order_items AS oi
    ON oi.product_id = p.id
GROUP BY p.id, p.name;

COUNT(oi.id) zwróci dla produktu bez zamówień wartość 0. Z kolei COUNT(*) policzyłby zachowany wiersz z wartościami NULL, co w takim raporcie może dać mylący wynik.

Wiele dopasowań i powielone wiersze

Jeżeli jeden klient ma pięć zamówień, LEFT JOIN zwróci pięć wierszy dla tego klienta. Nie jest to błąd, tylko naturalny efekt relacji jeden-do-wielu. Problem pojawia się dopiero wtedy, gdy ktoś oczekuje jednej linii na klienta, ale nie używa agregacji, DISTINCT albo podzapytania.

Przed dodaniem DISTINCT sprawdzam, czy duplikaty są faktycznie niepożądane. Często maskują one błędne założenie o strukturze danych, a nie rozwiązują przyczyny problemu.

Jak odtworzyć FULL OUTER JOIN w MySQL

MySQL obsługuje LEFT JOIN i RIGHT JOIN, ale nie udostępnia bezpośredniej składni FULL OUTER JOIN. Taki typ złączenia powinien zwrócić zarówno wszystkie rekordy z lewej tabeli, jak i wszystkie rekordy z prawej, również wtedy, gdy nie mają odpowiednika.

Najczęściej można osiągnąć ten efekt przez połączenie dwóch zapytań:

SELECT
    a.id AS a_id,
    a.value AS a_value,
    b.id AS b_id,
    b.value AS b_value
FROM table_a AS a
LEFT JOIN table_b AS b
    ON b.a_id = a.id

UNION ALL

SELECT
    a.id AS a_id,
    a.value AS a_value,
    b.id AS b_id,
    b.value AS b_value
FROM table_b AS b
LEFT JOIN table_a AS a
    ON a.id = b.a_id
WHERE a.id IS NULL;

Pierwsza część zwraca wszystkie rekordy z table_a. Druga dodaje tylko te rekordy z table_b, które nie pojawiły się wcześniej. Warunek WHERE a.id IS NULL jest tu niezbędny, bo bez niego dopasowane rekordy zostałyby zwrócone drugi raz.

Używam UNION ALL, a nie UNION, gdy kontroluję eliminację duplikatów warunkiem IS NULL. UNION wykonuje dodatkowe porównywanie wyników, więc przy większych zbiorach może być niepotrzebnie kosztowny.

Wydajność, NULL i kontrola wyników

Złączenie zewnętrzne nie jest z definicji wolne, ale jego koszt zależy od liczby rekordów, warunku połączenia i indeksów. Kolumny używane w ON, takie jak orders.customer_id, powinny mieć odpowiedni indeks, szczególnie gdy tabela połączona zawiera miliony wierszy.

Do sprawdzania planu wykonania używam polecenia:

EXPLAIN
SELECT
    c.name,
    o.id
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.id;

Wynik EXPLAIN pozwala zobaczyć, czy MySQL korzysta z indeksu, ile rekordów przewiduje do odczytania i w jakiej kolejności wykonuje operacje. Sam fakt, że zapytanie działa, nie oznacza jeszcze, że będzie działało dobrze po wzroście tabeli.

NULL to nie pusty tekst

Brak dopasowania jest oznaczany przez NULL, a nie przez pusty napis ani zero. Dlatego do sprawdzania takiej sytuacji trzeba użyć IS NULL lub IS NOT NULL, a nie operatora = NULL.

WHERE o.id IS NULL

Jeśli kolumna po stronie prawej może sama zawierać wartości NULL, filtruj po kolumnie oznaczonej jako NOT NULL, najczęściej po kluczu głównym. W przeciwnym razie możesz pomylić brak całego dopasowania z rekordem, który istnieje, ale ma pustą wartość w konkretnej kolumnie.

Przeczytaj również: CTAS w Oracle SQL - składnia, przykłady i pułapki

Łączenie trzech lub większej liczby tabel

Przy kilku złączeniach kolejność ma znaczenie dla czytelności i czasem dla wyniku. Przykładowo raport klientów, zamówień i płatności może wyglądać tak:

SELECT
    c.name,
    o.id AS order_id,
    p.paid_at
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.id
LEFT JOIN payments AS p
    ON p.order_id = o.id;

W tym zapytaniu klient bez zamówienia nadal zostanie pokazany, a brak płatności będzie oznaczony przez NULL. Trzeba jednak pamiętać, że jeśli zarówno zamówienia, jak i płatności są relacjami jeden-do-wielu, liczba wynikowych wierszy może szybko wzrosnąć.

Jak dobierać złączenie bez gubienia danych

Najprostsza reguła brzmi: tabela, której komplet rekordów chcesz zachować, powinna znaleźć się po lewej stronie LEFT JOIN. To drobna decyzja w składni, ale ma bezpośredni wpływ na raport.

• Wybierz LEFT JOIN, gdy raport ma pokazać wszystkie rekordy bazowe.
• Użyj INNER JOIN, gdy interesują Cię wyłącznie pasujące pary.
• Po RIGHT JOIN sięgaj głównie wtedy, gdy poprawia czytelność istniejącego zapytania.
• Zastosuj LEFT JOIN ... IS NULL, gdy szukasz brakujących powiązań.
• Użyj UNION ALL, gdy potrzebujesz efektu zbliżonego do pełnego złączenia zewnętrznego.

Przed wdrożeniem zapytania sprawdzam je na małym, ręcznie zweryfikowanym zestawie danych. Powinien zawierać rekord dopasowany, rekord bez dopasowania, kilka dopasowań do jednego wiersza oraz wartości NULL. Taki test szybciej ujawnia błąd w ON lub WHERE niż analiza samej składni.

Od poprawnego JOIN-a do wiarygodnego raportu

Złączenie zewnętrzne jest przede wszystkim narzędziem do zachowywania kontekstu. Pozwala pokazać nie tylko dane, które istnieją po obu stronach relacji, lecz także braki, wyjątki i rekordy wymagające dalszego działania.

Najważniejsze pytanie przed napisaniem zapytania brzmi nie „który JOIN znam?”, ale które rekordy muszą pozostać w wyniku. Gdy odpowiesz na nie jasno, wybór między LEFT JOIN, INNER JOIN i konstrukcją z UNION ALL staje się dużo prostszy, a raport przestaje przypadkiem ukrywać brakujące dane.