LEFT JOIN w SQL: zachowaj rekordy i uniknij błędów

Raport ma pokazać wszystkich klientów, także tych bez zamówień. LEFT JOIN zwykle zachowa ich w wyniku, ale liczba wierszy może wzrosnąć, jeśli jeden klient ma kilka pasujących zamówień.

Wynik zachowuje klientów, ale może zawierać ich kilka razy

Wyobraź sobie tabelę klientów i tabelę zamówień. Gdy klient nie ma zamówienia, jego dane nadal pojawią się w wyniku, a kolumny zamówienia będą miały wartość NULL. Tak działa zewnętrzne złączenie lewostronne, zapisywane też jako LEFT OUTER JOIN.

SELECT c.CustomerId, c.Name, o.OrderId
FROM Customers AS c
LEFT JOIN Orders AS o
    ON o.CustomerId = c.CustomerId;

Customers jest tu lewą tabelą, bo występuje przed operatorem. Jeśli dla klienta istnieją trzy zamówienia, zapytanie zwróci trzy wiersze z jego danymi. Uważam, że najważniejsza zasada brzmi: ten operator zachowuje lewą stronę, ale nie obiecuje jednego wiersza na każdy jej rekord.

To rozróżnienie przydaje się szczególnie wtedy, gdy raport ma pokazywać po jednym wierszu na klienta. Sam operator tego nie zapewni. Najpierw trzeba ustalić, czy wynik ma zawierać każde zamówienie, czy na przykład tylko łączną ich liczbę.

Warunek w ON zachowuje rekord bez pasującej płatności

Klient może mieć zamówienia, ale nie mieć żadnego opłaconego. Jeśli interesują Cię wszyscy klienci wraz z opłaconymi zamówieniami, filtr statusu umieść w ON:

SELECT c.CustomerId, c.Name, o.OrderId
FROM Customers AS c
LEFT JOIN Orders AS o
    ON o.CustomerId = c.CustomerId
   AND o.Status = 'Paid';

Klient bez opłaconych zamówień zostanie w wyniku, a kolumny zamówienia będą puste. Przeniesienie warunku statusu do WHERE odsieje wiersze, w których o.Status ma wartość NULL. W praktyce taki filtr często sprawia, że rezultat zachowuje się jak po INNER JOIN.

Krótka reguła pomaga uniknąć przypadkowego odfiltrowania danych: ograniczasz to, co może się dopasować - umieść warunek w ON; ograniczasz gotowy wynik - użyj WHERE. Przy złączeniach zewnętrznych te miejsca nie są zamienne.

Trzy pasujące zamówienia zmienią trzy rekordy klientów w pięć wierszy

Załóżmy, że w tabeli są trzej klienci: pierwszy ma trzy zamówienia, drugi nie ma żadnego, a trzeci ma jedno. Po złączeniu otrzymasz pięć wierszy: trzy dla pierwszego klienta, jeden z pustymi danymi zamówienia dla drugiego i jeden dla trzeciego.

To częsty powód zawyżonych wyników w raporcie. Jeśli potem policzysz wiersze przez COUNT(*), klient bez zamówień dostanie wynik równy 1, bo jego zachowany wiersz nadal istnieje. Do policzenia zamówień użyj COUNT(o.OrderId), zakładając, że OrderId nie dopuszcza NULL.

SELECT c.CustomerId,
       c.Name,
       COUNT(o.OrderId) AS OrderCount
FROM Customers AS c
LEFT JOIN Orders AS o
    ON o.CustomerId = c.CustomerId
GROUP BY c.CustomerId, c.Name;

Nie naprawiaj takiego wyniku odruchowym DISTINCT. Może ukryć powtórzone wartości w wyświetlanych kolumnach, ale nie odpowie, które zamówienie ma zostać wybrane ani jak policzyć wiele dopasowań. Jeśli ma zostać jedno zamówienie, określ regułę wyboru, na przykład najnowszą datę, i zastosuj ją jawnie.

Brak dopasowania wykryjesz przez klucz po prawej stronie

To złączenie przydaje się także do wyszukiwania brakujących powiązań, na przykład klientów bez zamówień. Po połączeniu odfiltruj wiersze, w których klucz zamówienia jest pusty:

SELECT c.CustomerId, c.Name
FROM Customers AS c
LEFT JOIN Orders AS o
    ON o.CustomerId = c.CustomerId
WHERE o.OrderId IS NULL;

Testuj kolumnę będącą kluczem głównym lub innym polem NOT NULL. Gdy sprawdzisz pole, które samo może zawierać NULL, nie odróżnisz braku dopasowanego rekordu od dopasowanego rekordu z pustą wartością w tej kolumnie.

Alternatywą jest NOT EXISTS, który wyraża wprost, że szukasz klientów bez zamówienia:

SELECT c.CustomerId, c.Name
FROM Customers AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM Orders AS o
    WHERE o.CustomerId = c.CustomerId
);

Przy prostym sprawdzaniu braków czytelność często rozstrzyga wybór. Dokumentacja MySQL opisuje wariant z LEFT JOIN i testem NULL, a w aplikacji możesz postawić na NOT EXISTS, jeśli wyraźniej pokazuje intencję zapytania.

USING skraca zapis w PostgreSQL, a SQL Server wymaga ON

Jeśli obie tabele łączy kolumna o tej samej nazwie, PostgreSQL pozwala zapisać warunek przez USING:

SELECT c.CustomerId, c.Name, o.OrderId
FROM Customers AS c
LEFT JOIN Orders AS o USING (CustomerId);

W wyniku kolumna łączenia pojawi się raz. Przy ON wybierasz kolumny z obu tabel osobno, co bywa wygodniejsze, gdy mają podobne nazwy albo potrzebujesz obu wartości. Dokumentacja PostgreSQL opisuje tę różnicę w wyniku, a składnia Transact-SQL dla SQL Server pokazuje warunek łączenia zapisany przez ON.

Jeśli kod ma działać na różnych silnikach, wybieraj ON i jawne kolumny w SELECT. Zapis jest dłuższy, ale łatwiej zauważyć, skąd pochodzi każde pole i które tabele łączysz.

Przed wdrożeniem sprawdź brak, jedno i wiele dopasowań

Najlepsza kontrola zapytania obejmuje trzy przypadki: rekord lewej tabeli bez odpowiednika, rekord z jednym dopasowaniem oraz rekord z kilkoma dopasowaniami. Taki zestaw od razu pokaże, czy zachowanie NULL i liczba wierszy odpowiadają zamierzonemu raportowi.

Potem sprawdź, czy agregujesz właściwą kolumnę i czy filtry po prawej stronie są w odpowiednim miejscu. Dzięki temu wynik pozostanie czytelny zarówno w raporcie, jak i po przekazaniu danych dalej, na przykład do aplikacji .NET.