Łączenie tabel - INNER JOIN - LEFT JOIN

W tabeli oceny nie zapisujemy imienia i nazwiska ucznia przy każdej ocenie, tylko jego numer uczen_id. Dzięki temu dane się nie powtarzają, a zmiana nazwiska wymaga poprawki w jednym miejscu. Ceną jest to, że żeby zobaczyć „Jan Kowalski – Matematyka – 5”, trzeba połączyć tabele po wspólnym polu. Do tego służy JOIN.

W bazie są jeszcze tabele nauczyciele (4 osoby, w tym Marek Kamiński, który nie wystawił żadnej oceny) oraz klasy (4A, 4B, 4C). Piotr i Kamil nie mają jeszcze ocen. Te braki przydadzą się przy LEFT i RIGHT JOIN.

JOIN to rozszerzenie zwykłego SELECT, więc najpierw przypomnij sobie jego budowę:

SELECT kolumny        -- co pokazać (* = wszystko)
FROM tabela           -- skąd
WHERE warunek         -- które wiersze (opcjonalnie)
ORDER BY kolumna;     -- jak posortować (ASC rosnąco, DESC malejąco)

INNER JOIN dołącza drugą tabelę i paruje wiersze, dla których warunek ON jest spełniony. Wiersze bez pary nie pojawiają się w wyniku.

SELECT uczniowie.imie, uczniowie.nazwisko, oceny.przedmiot, oceny.ocena
FROM uczniowie
INNER JOIN oceny
ON uczniowie.uczen_id = oceny.uczen_id;

SELECT …
FROM tabela1
JOIN tabela2 ON tabela1.klucz = tabela2.klucz;

Zobacz, jak JOIN dobiera pary

Przykład pomocniczy: szafki szkolne. Niektóre szafki są wolne (uczen_id = NULL), a Anna nie ma szafki. Wybierz rodzaj JOIN i przechodź krok po kroku.

Teraz prawdziwa baza. Zwróć uwagę, że w wyniku nie ma Piotra ani Kamila, bo nie mają ocen:

Kolumna uczen_id jest w obu tabelach. Gdy napiszesz ją bez nazwy tabeli, MySQL nie wie, o którą chodzi, i zgłasza błąd #1052 ambiguous (niejednoznaczna). Uruchom:

Żeby nie pisać za każdym razem długich nazw tabel, nadajemy im aliasy, czyli krótkie przezwiska, np. u i o. Kolumnom w wyniku też możesz nadać czytelne nazwy przez AS.

SELECT u.imie, o.ocena AS "Ocena"
FROM uczniowie u
JOIN oceny o ON u.uczen_id = o.uczen_id;

Po połączeniu tabel możesz filtrować i sortować wynik tak samo jak zwykle. Ważna jest tylko kolejność części zapytania:

SELECT … FROM … JOIN … ON … WHERE … GROUP BY … ORDER BY … LIMIT …

LEFT JOIN zwraca wszystkie wiersze z lewej tabeli (tej z FROM), nawet jeśli nie mają pary. Brakujące dane z prawej tabeli zostają wypełnione wartością NULL.

Klasyczne zastosowanie, które często pojawia się w arkuszach: znajdź wiersze bez pary. Po LEFT JOIN bierzesz tylko te wiersze, w których kolumna z prawej tabeli jest NULL:

RIGHT JOIN działa odwrotnie niż LEFT: zachowuje wszystkie wiersze z prawej tabeli (tej z JOIN). Każdy RIGHT JOIN można zapisać jako LEFT JOIN, zamieniając tabele miejscami. W praktyce i w odpowiedziach do arkuszy częściej spotkasz LEFT.

FULL JOIN (wszystko z obu stron) istnieje w standardzie SQL, ale MySQL i MariaDB go nie obsługują, więc w phpMyAdmin z XAMPP dostaniesz błąd. Ten sam wynik uzyskasz, sklejając LEFT JOIN i RIGHT JOIN słowem UNION:

Każdą kolejną tabelę dołączasz kolejnym JOIN … ON. Tabela oceny jest tu „łącznikiem”, bo ma klucze obce do obu pozostałych.

FROM uczniowie u
JOIN oceny o       ON u.uczen_id = o.uczen_id
JOIN nauczyciele n ON o.nauczyciel_id = n.nauczyciel_id

Starszy zapis: przecinek i WHERE

W starszych materiałach i w wielu odpowiedziach do arkuszy egzaminacyjnych łączenie wygląda tak: tabele wypisane po przecinku, a warunek łączenia w WHERE. Wynik jest taki sam jak przy INNER JOIN.

Na egzaminie często trzeba coś policzyć dla każdej osoby albo grupy, np. liczbę ocen każdego ucznia czy średnią w każdej klasie. Łączysz tabele, a potem grupujesz przez GROUP BY:

Napisz zapytanie i kliknij Sprawdź. Sprawdzarka porówna Twój wynik z wynikiem wzorcowego zapytania: liczbę kolumn, wszystkie wiersze i tam, gdzie to wymagane, kolejność. Nazwy kolumn w nagłówku nie mają znaczenia, ale kolejność kolumn musi być taka jak w poleceniu.

SELECT u.imie, u.nazwisko, o.przedmiot, o.ocena
FROM uczniowie u
JOIN oceny o ON u.uczen_id = o.uczen_id;

SELECT u.imie, u.nazwisko, o.ocena
FROM uczniowie u
JOIN oceny o ON u.uczen_id = o.uczen_id
WHERE o.przedmiot = 'Matematyka'
ORDER BY o.ocena DESC;

SELECT u.imie, u.nazwisko, o.przedmiot, o.ocena
FROM uczniowie u
LEFT JOIN oceny o ON u.uczen_id = o.uczen_id;

SELECT u.imie, u.nazwisko
FROM uczniowie u
LEFT JOIN oceny o ON u.uczen_id = o.uczen_id
WHERE o.ocena_id IS NULL;

SELECT u.imie, u.nazwisko, o.przedmiot, o.ocena, n.nazwisko
FROM uczniowie u
JOIN oceny o ON u.uczen_id = o.uczen_id
JOIN nauczyciele n ON o.nauczyciel_id = n.nauczyciel_id;

SELECT n.imie, n.nazwisko
FROM nauczyciele n
LEFT JOIN oceny o ON n.nauczyciel_id = o.nauczyciel_id
WHERE o.ocena_id IS NULL;

SELECT u.imie, u.nazwisko, COUNT(o.ocena_id) AS liczba
FROM uczniowie u
LEFT JOIN oceny o ON u.uczen_id = o.uczen_id
GROUP BY u.uczen_id, u.imie, u.nazwisko
ORDER BY liczba DESC;

SELECT k.nazwa, AVG(o.ocena) AS srednia
FROM klasy k
JOIN uczniowie u ON k.klasa_id = u.klasa_id
JOIN oceny o ON u.uczen_id = o.uczen_id
GROUP BY k.klasa_id, k.nazwa
ORDER BY k.nazwa;

SELECT u.imie, o.ocena
FROM uczniowie u
JOIN oceny o ON u.uczen_id = o.uczen_id;

FROM uczniowie u
LEFT JOIN oceny o ON u.uczen_id = o.uczen_id

LEFT JOIN oceny o ON …
WHERE o.ocena_id IS NULL

FROM a
JOIN b ON a.id = b.a_id
JOIN c ON b.c_id = c.id

SELECT … FROM … JOIN … ON …
WHERE … GROUP BY … HAVING …
ORDER BY … LIMIT …

SELECT u.imie, COUNT(o.ocena_id)
FROM uczniowie u
LEFT JOIN oceny o ON …
GROUP BY u.uczen_id, u.imie

Na następnej lekcji: podzapytania i modyfikacja danych (UPDATE, DELETE z warunkiem).