LEFT JOIN, SELF JOIN, CROSS JOIN i relacje N:M

LEFT JOIN zachowuje rekord bez dopasowania

INNER JOIN pokazał klientów mających zamówienia. Raport wszystkich klientów wymaga także Ewy i Adama. LEFT JOIN zachowuje każdy wiersz lewej tabeli; gdy prawa strona nie pasuje, jej kolumny w wyniku dostają NULL.

SELECT k.id, k.imie, z.id AS zamowienie
FROM klienci k
LEFT JOIN zamowienia z ON z.klient_id = k.id
ORDER BY k.id, z.id;

Wynik ma sześć wierszy: Ala z zamówieniami 1 i 3, Jan z 2, O’Neil z 4 oraz Ewa i Adam z NULL zamówienia. LEFT JOIN nie gwarantuje jednego wiersza na klienta — pasujących zamówień może być wiele.

Brak zamówienia i licznik zero

SELECT k.id, k.imie
FROM klienci k LEFT JOIN zamowienia z ON z.klient_id = k.id
WHERE z.id IS NULL
ORDER BY k.id;

SELECT k.id, k.imie, COUNT(z.id) AS zamowienia
FROM klienci k LEFT JOIN zamowienia z ON z.klient_id = k.id
GROUP BY k.id, k.imie ORDER BY k.id;

Pierwsze zapytanie wybiera Ewę i Adama. Sprawdzamy z.id, czyli klucz, który przy rzeczywistym dopasowaniu nie jest NULL. Test kolumny opcjonalnej, np. komentarza, byłby niejednoznaczny. Drugie zapytanie daje liczniki 2, 1, 1, 0, 0. COUNT(*) policzyłoby sztuczny wiersz z NULL po prawej i dało 1 zamiast 0 osobom bez zamówień.

Pułapka WHERE po LEFT JOIN

SELECT k.id, k.imie, z.id AS oplacone_zamowienie
FROM klienci k
LEFT JOIN zamowienia z
  ON z.klient_id = k.id AND z.status = 'oplacone'
ORDER BY k.id, z.id;

To wszyscy klienci oraz tylko ich opłacone zamówienia. Ala ma dwa dopasowania, pozostali mają NULL. Przeniesienie z.status = 'oplacone' do WHERE usunie wiersze bez dopasowania: porównanie z NULL daje UNKNOWN. Zmieni się sens raportu. Nie ma jednej reguły „zawsze ON” — umieść warunek zgodnie z tym, które wiersze chcesz zachować.

Różnice środowisk

LEFT JOIN i RIGHT JOIN obsługują MySQL 8.4, MariaDB 10.4 z naszego XAMPP, SQL Server, Oracle 19c i Access. Ten sam raport można zapisać jako zamowienia z RIGHT JOIN klienci k ON z.klient_id = k.id. Dla czytelności często łatwiej zamienić strony i użyć LEFT JOIN.

SQL Server i Oracle 19c mają FULL OUTER JOIN. MySQL 8.4 oraz MariaDB 10.4 nie mają natywnego FULL OUTER JOIN; Access też nie. Można złożyć wynik z LEFT JOIN i niedopasowanych rekordów przeciwnej strony przez UNION ALL, dbając o duplikaty. SQLite w edytorze (3.49.1) obsługuje RIGHT i FULL JOIN w trybie natywnym; tryby emulacji je odrzucają. Nie przedstawiamy wyniku SQLite FULL JOIN jako wyniku MySQL.

W Access złożone złączenia wymagają właściwych nawiasów i mają ograniczenia zagnieżdżania. Gdy połączenie zewnętrzne obejmuje wynik kilku tabel, pomocna bywa osobna zapisana kwerenda. Nie myl błędu parsera ze zmianą definicji LEFT JOIN.

SELF JOIN: jedna tabela w dwóch rolach

SELF JOIN to sposób użycia zwykłego JOIN, nie osobne słowo kluczowe. Dwa aliasy oznaczają dwie role tej samej tabeli. Znajdź pary klientów z tego samego miasta:

SELECT a.imie AS osoba_a, b.imie AS osoba_b, a.miasto
FROM klienci a
INNER JOIN klienci b ON a.miasto = b.miasto AND a.id < b.id
ORDER BY a.id, b.id;

Jedyna para po resecie to Ala i O’Neil z Gdanska. Warunek a.id < b.id usuwa parę osoby z samą sobą oraz drugi zapis tej samej pary w odwrotnej kolejności. Wspólne NULL miasta nie tworzą dopasowania przez zwykłe =.

CROSS JOIN: każda kombinacja

-- MySQL / MariaDB, SQL Server, Oracle, natywny SQLite
SELECT k.imie, pr.nazwa
FROM klienci k CROSS JOIN produkty pr
ORDER BY k.id, pr.id;
-- Access: iloczyn kartezjański przez listę źródeł
SELECT k.imie, pr.nazwa
FROM klienci AS k, produkty AS pr
ORDER BY k.id, pr.id;

5 klientów × 4 produkty daje 20 wierszy. To przydatne do budowania wszystkich wariantów, ale przypadkowy brak warunku połączenia w dużych tabelach może gwałtownie powiększyć wynik. Edytor ma limity czasu i wyników; reset odtwarza jego własną bazę.

Relacja N:M przechodzi przez tabelę pośrednią

Produkt może wystąpić w wielu zamówieniach, zamówienie ma wiele produktów. Pozycja niesie również ilość. Klucz złożony w naszym przykładzie zakazuje powtórzenia tej samej pary; jeśli biznes dopuszcza kilka niezależnych pozycji tego samego produktu, potrzebny jest inny klucz pozycji. Łączenie bez przejścia przez pozycje nie odtworzy informacji o zakupach.

Ćwiczenia

1. Wyświetl klientów bez zamówień.
2. Pokaż wszystkich klientów z liczbą opłaconych zamówień, także zero.
3. Ile kombinacji da CROSS JOIN tabeli z 8 i tabeli z 3 wierszami? Co gdy jedna jest pusta?

SELECT k.imie FROM klienci k
LEFT JOIN zamowienia z ON z.klient_id = k.id
WHERE z.id IS NULL ORDER BY k.id;
SELECT k.id, k.imie, COUNT(z.id) AS oplacone
FROM klienci k LEFT JOIN zamowienia z
ON z.klient_id = k.id AND z.status = 'oplacone'
GROUP BY k.id, k.imie ORDER BY k.id;

Źródła: MySQL JOIN, Oracle — złączenia, Access LEFT/RIGHT JOIN, SQLite SELECT. INF.03.4: relacje i zapytania wielotabelowe. Dalej: podzapytania i EXISTS.