Relacje i JOIN w SQL: jak zrozumieć łączenie tabel

Relacje i JOIN to moment, w którym SQL przestaje być „SELECT-em z tabeli”, a zaczyna przypominać projektowanie. Dla wielu początkujących to też moment, w którym wyniki przestają się zgadzać z oczekiwaniami: wiersze znikają, wiersze się dublują, a w kolumnach pojawia się NULL. Ten artykuł prowadzi przez jedną małą bazę, na której widać każdy z tych efektów, bo wszystkie przykłady uruchomiliśmy i pokazujemy ich prawdziwe wyniki.

Po co w ogóle łączyć tabele?

Dane w bazie rozdziela się na tabele, żeby nie powtarzać tych samych informacji. Zamiast wpisywać imię klienta przy każdym zamówieniu, trzymasz klientów w jednej tabeli, a zamówienia w drugiej i wskazujesz, do kogo należą. Poniżej schemat, na którym zostaniemy do końca artykułu:

CREATE TABLE customers (
  id   INTEGER PRIMARY KEY,
  name TEXT
);

CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  total       INTEGER
);

INSERT INTO customers VALUES (1, 'Anna'), (2, 'Bartek'), (3, 'Celina');
INSERT INTO orders VALUES (1, 1, 100), (2, 1, 250), (3, 2, 80);

Anna ma dwa zamówienia, Bartek jedno, a Celina żadnego. Ta ostatnia różnica będzie za chwilę ważna.

Czym jest klucz główny i klucz obcy?

Klucz główny (PRIMARY KEY) to kolumna, która jednoznacznie identyfikuje wiersz w swojej tabeli. Tu jest to customers.id i orders.id.

Klucz obcy (FOREIGN KEY, w SQL-u zapisywany jako REFERENCES) to kolumna, która wskazuje na klucz główny innej tabeli. Tu orders.customer_id wskazuje na customers.id. Baza pilnuje spójności: nie dodasz zamówienia klienta o id, którego nie ma w customers.

To właśnie klucz obcy mówi JOIN-owi, po czym ma łączyć.

Jakie są rodzaje relacji między tabelami?

Najczęstsza jest relacja 1:N i to na niej wyrasta większość pytań o JOIN. Klucz obcy zawsze trafia po stronie „wiele”: to zamówienie wie, do którego klienta należy, a nie klient zna listę swoich zamówień.

Jak działa INNER JOIN?

INNER JOIN zwraca tylko te wiersze, które mają parę w obu tabelach:

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

Zwróć uwagę na dwie rzeczy. Po pierwsze, Celiny nie ma w wyniku: nie ma zamówień, więc nie ma z czym jej połączyć. Po drugie, Anna pojawia się dwa razy, bo ma dwa zamówienia. ON opisuje, kiedy dwa wiersze do siebie pasują, a każde pasujące dopasowanie to osobny wiersz wyniku.

Czym różni się LEFT JOIN od INNER JOIN?

LEFT JOIN zachowuje wszystkie wiersze z lewej tabeli (tej po słowie FROM), nawet jeśli nie mają pary:

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

Celina wróciła, a kolumny z prawej tabeli mają NULL. Wniosek praktyczny: NULL w wyniku LEFT JOIN-a nie znaczy „błąd”, tylko „brak pary”. LEFT JOIN i LEFT OUTER JOIN to dokładnie to samo polecenie, słowo OUTER jest opcjonalne.

Z tego wynika prosta technika: chcesz znaleźć klientów bez zamówień? Złącz lewostronnie i zostaw tylko wiersze, w których prawa strona jest pusta:

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

Wynik to jeden wiersz: Celina.

Którą tabelę dać po lewej stronie LEFT JOIN-a?

Tę, której wszystkie wiersze chcesz zobaczyć. Jeśli odwrócisz kolejność i napiszesz orders LEFT JOIN customers, dostaniesz wszystkie zamówienia, a Celina nadal zniknie, bo nie ma żadnego zamówienia, które mogłoby ją „przyprowadzić”. Zamiana stron zmienia wynik, w przeciwieństwie do INNER JOIN, gdzie kolejność tabel nie ma znaczenia.

Dlaczego po JOIN-ie mam więcej wierszy niż w tabeli?

To jedno z najczęstszych pytań początkujących i zwykle nie jest błędem. Kiedy łączysz po relacji 1:N, wiersz ze strony „jeden” powtarza się tyle razy, ile ma powiązanych wierszy po stronie „wiele”:

SELECT COUNT(*) AS wiersze
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

Wynik to 4, choć w customers są tylko 3 wiersze. Anna liczy się dwa razy.

Jeśli potrzebujesz jednego wiersza na klienta, zagreguj:

SELECT c.name,
       COUNT(o.id)             AS zamowienia,
       COALESCE(SUM(o.total), 0) AS suma
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

Dwa szczegóły, które tu robią różnicę: COUNT(o.id) zlicza tylko niepuste wartości, więc Celina ma 0, a nie 1. A COALESCE zamienia NULL z SUM na 0.

Czym różni się warunek w WHERE od warunku w ON przy LEFT JOIN?

To najczęstsza pułapka. Chcesz wszystkich klientów i, jeśli mają, ich zamówienia powyżej 90. Piszesz naturalnie:

SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.total > 90;

Bartek i Celina zniknęli. Powód: WHERE działa po połączeniu, a dla Celiny o.total jest NULL, więc warunek NULL > 90 nie jest prawdziwy i wiersz odpada. Zrobiłeś z LEFT JOIN-a w praktyce INNER JOIN.

Jeśli warunek ma dotyczyć tylko zamówień, przenieś go do ON:

SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.total > 90;

Teraz każdy klient jest w wyniku, a zamówienia poniżej 90 po prostu się nie dołączają (Bartek ma jedno za 80, więc dostał NULL). Zasada: warunek na tabeli z lewej strony — do WHERE, warunek na tabeli z prawej strony LEFT JOIN-a — do ON.

Jak zapisać relację wiele-do-wielu?

Student może zapisać się na wiele kursów, a kurs ma wielu studentów. Jednego klucza obcego tu nie wystarczy, więc dodajesz tabelę pośrednią (łączącą), której wiersz mówi „ten student chodzi na ten kurs”:

CREATE TABLE students (id INTEGER PRIMARY KEY, name  TEXT);
CREATE TABLE courses  (id INTEGER PRIMARY KEY, title TEXT);

CREATE TABLE enrollments (
  student_id INTEGER REFERENCES students(id),
  course_id  INTEGER REFERENCES courses(id),
  PRIMARY KEY (student_id, course_id)
);

Klucz główny złożony z obu kolumn dodatkowo pilnuje, że ten sam student nie zapisze się na ten sam kurs dwa razy. Zapytanie łączy trzy tabele jednym JOIN po drugim:

SELECT s.name, c.title
FROM students s
JOIN enrollments e ON e.student_id = s.id
JOIN courses c     ON c.id = e.course_id
ORDER BY s.name, c.title;

Czytasz to od lewej do prawej: student → jego zapisy → kursy, na które się zapisał. Samo słowo JOIN bez przymiotnika oznacza INNER JOIN.

Co się stanie, jeśli zapomnę warunku w ON?

Dostaniesz iloczyn kartezjański: każdy wiersz z jednej tabeli sparowany z każdym wierszem z drugiej. Dla naszych tabel to 3 klientów razy 3 zamówienia, czyli 9 wierszy bez żadnego sensu. Przy dwóch tabelach po kilka tysięcy wierszy to już miliony wierszy wyniku, więc jeśli wynik JOIN-a ma niespodziewanie dużo wierszy, najpierw sprawdź, czy każdy JOIN ma swoje ON.

Jak sprawdzić, czy JOIN robi to, co chcę?

Trzy szybkie testy, które łapią większość pomyłek:

1. Policz wiersze przed i po. Jeśli po JOIN-ie masz więcej niż w tabeli, od której zaczynasz, to relacja 1:N albo błąd w ON. Zdecyduj, które.
2. Poszukaj NULL w kolumnach z prawej strony. Są tam, gdzie lewe wiersze nie mają pary. Jeśli ich nie spodziewasz się, a są, ON łączy po złej kolumnie.
3. Zacznij od małej, własnej tabeli. Trzy-cztery wiersze, w których sam przewidzisz wynik, uczą więcej niż duża baza z internetu.

Czy do szukania klientów bez zamówień wolno użyć NOT IN?

Wolno, ale to najgorszy z trzech sposobów, bo potrafi cicho zwrócić zły wynik. Napisałeś wyżej LEFT JOIN … WHERE o.id IS NULL. Wiele osób sięga zamiast tego po podzapytanie z NOT IN:

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

Na naszych danych działa i zwraca Celina. Teraz dopisz do orders zamówienie, któremu nie przypisano klienta (customer_id jest puste, czyli NULL):

INSERT INTO orders VALUES (4, NULL, 50);

Powtórz to samo zapytanie z NOT IN: wynik jest pusty, choć Celina nadal nie ma żadnego zamówienia i nie dostajesz żadnego komunikatu o błędzie. Powód to logika trójwartościowa: porównanie z NULL nie jest ani prawdą, ani fałszem, tylko „nie wiadomo”, więc id NOT IN (…, NULL) nigdy nie jest prawdziwe.

Dwa pozostałe sposoby na tych samych danych dalej zwracają Celina:

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

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

Zasada na co dzień: do szukania wierszy „bez pary” używaj LEFT JOIN … IS NULL albo NOT EXISTS, a NOT IN (SELECT …) tylko wtedy, gdy masz pewność, że kolumna w podzapytaniu nie może być NULL.

Jak to się ma do ORM-a i frameworków?

W PHP czy Pythonie rzadko piszesz JOIN-y ręcznie, ale one nadal tam są: hasMany i belongsToMany w Eloquent oraz ForeignKey i ManyToManyField w Django ORM to nazwy relacji, które pod spodem generują dokładnie takie zapytania. Kto rozumie klucz obcy i tabelę pośrednią, ten rozumie też, dlaczego ORM potrafi wysłać setki zapytań zamiast jednego (problem N+1). Z relacjami i bazami danych pracujesz w trzech naszych kursach: w kursie PHP (projektowanie schematu MySQL, Eloquent i relacje), w kursie Python (moduły o bazach danych i ORM) oraz w kursie Zaawansowany JS, gdzie w module Next.js pracujesz z relacyjną bazą SQLite i ORM-em.

Jeśli interesuje cię, jak te same relacje zapisuje się na diagramach, zajrzyj do artykułu o diagramach klas UML, gdzie opisujemy relacje między klasami w programowaniu obiektowym.