Raport ma pokazać wszystkich klientów, również tych bez zamówień, ale zwykły JOIN usuwa właśnie takie wiersze. W SQL rozwiązuje to zwykle LEFT OUTER JOIN, choć wynik może zaskoczyć, jeśli filtr połączysz z nim w niewłaściwym miejscu.
Outer join zachowuje wiersze bez pary, ale filtr może je usunąć
- LEFT JOIN zachowuje wszystkie wiersze z tabeli po lewej stronie.
- RIGHT JOIN zachowuje wszystkie wiersze z tabeli po prawej stronie.
- FULL OUTER JOIN zachowuje wiersze z obu tabel, także bez dopasowania.
- NULL w kolumnach drugiej tabeli oznacza brak pasującego wiersza, nie pusty tekst ani zero.
- Warunek w WHERE może odrzucić wiersze zachowane przez złączenie zewnętrzne.
Outer join SQL zachowuje wiersze bez dopasowania
Wyobraź sobie raport, w którym trzeba uwzględnić każdego klienta, nawet jeśli jeszcze niczego nie kupił. Outer join, czyli złączenie zewnętrzne, pozwala dołączyć pasujące dane z drugiej tabeli, a przy braku dopasowania wstawić w jej kolumnach NULL. To, które wiersze pozostaną w wyniku, zależy od strony wskazanej w zapytaniu.
W praktyce najczęściej przyda się LEFT JOIN. Uważam, że to najczytelniejszy punkt wyjścia, bo łatwo sprawdzić, która tabela ma zostać zachowana w całości: wystarczy spojrzeć na nazwę po lewej stronie słowa JOIN.
LEFT, RIGHT i FULL różnią się zachowywaną stroną
Załóżmy, że masz tabele customers i orders, połączone kluczem klienta. Wybór typu złączenia określa, co stanie się z klientem bez zamówień albo zamówieniem, którego nie da się przypisać do klienta.
| Typ | Co zachowuje | Typowy przypadek |
|---|---|---|
LEFT JOIN |
Wszystkie wiersze lewej tabeli i dopasowania z prawej | Lista klientów z zamówieniami, także bez zamówień |
RIGHT JOIN |
Wszystkie wiersze prawej tabeli i dopasowania z lewej | Wszystkie zamówienia, także bez dopasowanego klienta |
FULL OUTER JOIN |
Wiersze z obu tabel, dopasowane i niedopasowane | Porównanie dwóch źródeł danych i znalezienie braków po obu stronach |
RIGHT JOIN można zwykle zapisać jako LEFT JOIN, zamieniając kolejność tabel. Dlatego w czytelnym kodzie często wystarczy trzymać się wariantu lewostronnego. Pamiętaj też, że słowo OUTER jest opcjonalne: LEFT JOIN i LEFT OUTER JOIN oznaczają to samo, co opisuje samouczek PostgreSQL o złączeniach tabel.
Warunek w WHERE może skasować efekt LEFT JOIN
Pułapka często pojawia się dopiero po dodaniu filtra. Jeśli po złączeniu odrzucisz wiersze, w których kolumna z prawej tabeli ma wartość NULL, znikną także rekordy bez dopasowania, które miało zachować LEFT JOIN.
Załóżmy, że chcesz pokazać wszystkich klientów, a przy nich tylko zamówienia ze statusem „opłacone”. Umieść warunek statusu w ON, jeśli klient bez opłaconego zamówienia nadal ma zostać w wyniku:
SELECT c.customer_id, o.order_id, o.status
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id
AND o.status = 'opłacone';Przeniesienie o.status = 'opłacone' do WHERE odrzuci wiersze, dla których kolumna statusu jest równa NULL. Tabela pomaga rozstrzygnąć, które miejsce wybrać.
| Sytuacja | Skutek | Co zrobić |
|---|---|---|
| Wiersz lewej tabeli ma pozostać nawet bez dopasowania | Filtr w WHERE po prawej stronie może go odrzucić |
Rozważ warunek w ON |
| Chcesz wyłącznie wiersze z pasującym rekordem i danym statusem | Wiersze bez dopasowania nie są potrzebne | Użyj filtra w WHERE albo rozważ INNER JOIN |
| Szukasz klientów bez żadnego zamówienia | W kolumnie klucza zamówienia pojawi się NULL |
Dodaj WHERE o.order_id IS NULL |
Ostatni wzorzec to tzw. anti-join, czyli wyszukanie wierszy bez pary. Sprawdzaj kolumnę, która nie może być NULL w istniejącym zamówieniu, na przykład klucz główny, a nie pole opcjonalne takie jak uwagi.
Duplikaty po złączeniu mogą zawyżyć sumę
Outer join nie gwarantuje jednego wiersza wyniku na jednego klienta. Jeśli klient ma trzy zamówienia, po złączeniu zobaczysz trzy wiersze z danymi tego klienta. To poprawny wynik relacji jeden-do-wielu, ale suma wartości pól z tabeli klientów może przez to zostać policzona wielokrotnie.
Przykład: jeden klient ma wydany łączny budżet 100, a w tabeli zamówień są trzy rekordy. Po złączeniu kolumna budżetu wystąpi w trzech wierszach, więc zwykłe SUM(c.budget) zwróci 300 zamiast 100. Jeśli raport ma sumować osobno budżety i zamówienia, najpierw agreguj każdą tabelę do oczekiwanego poziomu, a dopiero potem łącz wyniki.
WITH order_totals AS (
SELECT customer_id, SUM(amount) AS total_orders
FROM orders
GROUP BY customer_id
)
SELECT c.customer_id, c.budget, ot.total_orders
FROM customers AS c
LEFT JOIN order_totals AS ot
ON ot.customer_id = c.customer_id;Sprawdź liczbę wierszy po złączeniu oraz to, czy klucz po stronie, którą zakładasz jako pojedynczą, rzeczywiście jest unikalny. Jeśli wynik się multiplikuje, problemem może być liczba dopasowań, a nie sam typ złączenia.
FULL OUTER JOIN zależy od silnika bazy
Zapytanie poprawne w jednym środowisku może nie zadziałać w innym. Przykładowo informacje o wydaniu SQLite 3.39.0 wskazują dodanie obsługi RIGHT JOIN i FULL OUTER JOIN; starsze wersje SQLite mogą więc odrzucić takie zapytanie.
Gdy nie masz pewności co do obsługi danego wariantu, sprawdź wersję i dokumentację używanego silnika przed wdrożeniem. Do wielu codziennych raportów wystarczy LEFT JOIN, a FULL OUTER JOIN zostaw do sytuacji, w których naprawdę chcesz zachować niezależne rekordy z obu źródeł.
Najpierw ustal, które rekordy raport ma zachować
Przy złączeniu zewnętrznym zacznij od pytania, która tabela ma zostać pokazana w całości. Następnie sprawdź, czy warunek po drugiej stronie należy do ON, czy ma filtrować już gotowy wynik przez WHERE, i czy relacja nie zwielokrotni wierszy.
Gdy te trzy decyzje są jasne, wybór między LEFT, RIGHT i FULL przestaje być zgadywaniem. A raport z klientami bez zamówień nie zgubi ich po cichu.
