Problemy z duplikatami przy JOIN

Problemy z duplikatami przy złączeniu wynikają z relacji wiele do wielu, mnożącej liczbę zwracanych wierszy.

Skąd biorą się powielone wiersze po złączeniu tabel

Duplikaty pojawiające się po użyciu JOIN zwykle nie są błędem samego silnika SQL. Wynikają z liczności relacji między tabelami oraz z warunku określonego po słowie ON. Jeżeli jeden rekord z tabeli po lewej stronie pasuje do trzech rekordów tabeli po prawej stronie, zapytanie zwróci trzy wiersze zawierające dane tego samego rekordu lewego. Przy dwóch pasujących rekordach powstają dwa wiersze, a przy braku dopasowania zachowanie zależy od rodzaju złączenia.

Przykładowo tabela orders może zawierać jedno zamówienie, a tabela order_items kilka pozycji przypisanych do tego zamówienia. Zapytanie łączące je po identyfikatorze zamówienia prawidłowo zwróci osobny wiersz dla każdej pozycji. Z punktu widzenia użytkownika może to wyglądać jak powielenie zamówienia, ale w rzeczywistości każdy wiersz opisuje inną kombinację danych: to samo zamówienie oraz inną pozycję. Problem pojawia się wtedy, gdy raport miał pokazywać jedno zamówienie, lecz zapytanie pobiera jednocześnie szczegóły na poziomie pozycji.

Szczególnie dużo wyników może powstać przy połączeniu dwóch relacji jeden-do-wielu dotyczących tego samego rekordu. Jeżeli klient ma trzy adresy zapisane w jednej tabeli i cztery zgłoszenia w drugiej, bez wcześniejszego ograniczenia danych połączenie może utworzyć dwanaście kombinacji. Jest to efekt iloczynu liczby dopasowanych rekordów, a niekoniecznie obecności identycznych rekordów w bazie. Dlatego przed usunięciem rzekomych duplikatów trzeba ustalić, jaki poziom szczegółowości ma mieć wynik.

Warunek ON, klucze i niepełne kryteria dopasowania

Najczęstszą przyczyną nieoczekiwanej liczby wierszy jest złączenie po kolumnie, która nie identyfikuje rekordu jednoznacznie. Klucz główny tabeli ma unikalną wartość w każdym wierszu, natomiast zwykła kolumna, taka jak nazwisko, miasto, status albo numer dokumentu, może występować wielokrotnie. Połączenie tabel wyłącznie po takiej kolumnie dopasuje wszystkie rekordy o tej samej wartości. Jeżeli nazwisko występuje u kilku osób, warunek ON a.last_name = b.last_name nie wskazuje konkretnej osoby, lecz całą grupę osób o tym nazwisku.

Bezpieczniejsze złączenie zwykle wykorzystuje klucz obcy po stronie tabeli zależnej i odpowiadający mu klucz główny tabeli nadrzędnej, na przykład customers.id = orders.customer_id. W praktyce trzeba jednak sprawdzić, czy zapytanie nie pomija dodatkowej części identyfikatora. W systemach, w których rekord rozpoznaje się przez kilka kolumn, połączenie tylko po jednej z nich może łączyć dane należące do różnych okresów, oddziałów albo wersji rekordu. Wtedy warunek powinien uwzględniać wszystkie kolumny potrzebne do jednoznacznego dopasowania.

Warto odróżnić warunek złączenia od filtrów umieszczonych w WHERE. Przy LEFT JOIN filtr dotyczący tabeli prawej dodany w WHERE może usunąć wiersze bez dopasowania i zmienić praktyczne działanie zapytania na podobne do INNER JOIN. Przeniesienie tego ograniczenia do klauzuli ON zachowuje rekordy z lewej tabeli, a jednocześnie ogranicza zestaw dopasowywanych rekordów po prawej stronie. Ta różnica nie zawsze usuwa powielanie, ale pomaga kontrolować, które relacje są brane pod uwagę.

Rozróżnienie DISTINCT, agregacji i poprawnego modelu danych

Klauzula DISTINCT usuwa powtarzające się całe wiersze wyniku, lecz nie naprawia błędnego warunku JOIN. Jeżeli dwa wiersze mają różne wartości w dowolnej wybranej kolumnie, pozostaną w rezultacie. Przykładowo dwa rekordy zawierające ten sam identyfikator klienta, ale różne daty zdarzeń, nie zostaną zredukowane do jednego przez SELECT DISTINCT customer_id, event_date. Użycie DISTINCT może także ukryć fakt, że zapytanie pobiera zbyt wiele kombinacji i przez to obciąża bazę większą liczbą operacji.

Jeżeli celem jest jeden wiersz na klienta, zamówienie lub urządzenie, trzeba określić regułę wyboru danych szczegółowych. Można zastosować agregację, na przykład COUNT, SUM, MIN albo MAX, razem z GROUP BY. Wtedy wiele rekordów podrzędnych zostaje świadomie przedstawionych jako jedna wartość podsumowująca. Jeżeli potrzebny jest konkretny rekord, na przykład ostatnie zdarzenie, należy najpierw ustalić, co oznacza „ostatnie”, a następnie wybrać rekord według uporządkowanego kryterium. Samo MAX(date) zwraca datę, ale nie musi zwrócić pozostałych kolumn z tego samego wiersza.

Przy relacji wiele-do-wielu typowym rozwiązaniem jest tabela pośrednia zawierająca klucze obu stron. Jeżeli użytkownik może mieć wiele ról, a jedna rola może należeć do wielu użytkowników, wynik złączenia użytkowników, tabeli łącznikowej i ról będzie zawierał osobny wiersz dla każdej pary użytkownik–rola. To zachowanie jest prawidłowe. Jeśli raport wymaga jednego wiersza na użytkownika, dane ról trzeba pogrupować, ograniczyć do wybranej roli albo zwrócić w odrębnej strukturze. Przed użyciem funkcji redukujących należy także sprawdzić ograniczenia unikalności i indeksy, ponieważ to one opisują oczekiwane zależności w danych.

Praktyczne sprawdzenie zapytania i wyników

Diagnostykę warto rozpocząć od uruchomienia każdego fragmentu osobno. Najpierw należy policzyć rekordy w tabeli bazowej, następnie sprawdzić liczbę dopasowań po stronie tabeli dołączanej, a potem wykonać pełne złączenie z dodatkowymi kolumnami identyfikującymi źródłowy rekord. Pomocne jest tymczasowe dodanie identyfikatorów kluczy głównych, aby zobaczyć, które kombinacje są powtarzane. Zapytanie kontrolne z GROUP BY i COUNT(*) pozwala wskazać klucze, dla których występuje więcej niż jedno dopasowanie.

Należy też sprawdzić, czy relacja jest zgodna z założeniem aplikacji. Kolumna używana jako klucz obcy powinna wskazywać właściwy rekord nadrzędny, a dane nie powinny zawierać przypadkowych wartości powodujących dodatkowe dopasowania. Warto przejrzeć definicje kluczy, ograniczeń unikalności oraz indeksów, ale sam indeks nie gwarantuje braku duplikatów. Indeks może przyspieszyć wyszukiwanie, natomiast unikalność wynika z odpowiedniego ograniczenia lub klucza. Trzeba również ustalić, czy zapytanie nie wykonuje połączenia z tabelą historii, logów lub wersji, gdzie wiele rekordów dla jednego obiektu jest zamierzone.

Warszawskie Pogotowie Komputerowe zajmuje się profesjonalną naprawą laptopów i komputerów PC, a przy problemach z aplikacjami bazodanowymi może pomóc w odtworzeniu zapytania, prześledzeniu relacji między tabelami oraz sprawdzeniu, czy obserwowane powielanie wynika z danych, warunku JOIN czy sposobu prezentacji wyników. W praktyce poprawa polega na dopasowaniu zapytania do oczekiwanego poziomu szczegółowości, a nie na mechanicznym dodaniu DISTINCT. Dzięki temu raport zachowuje znaczenie danych, a użytkownik może odróżnić prawdziwe powtórzenia od prawidłowych wierszy opisujących różne elementy tej samej relacji.