BAZY DANYCH · INF.03

JOIN — łączenie tabel

Dane o uczniach, książkach i wypożyczeniach leżą w osobnych tabelach. JOIN składa je z powrotem w jeden czytelny obraz: kto co wypożyczył, z której klasy pochodzi i kiedy ma oddać książkę.

INNER JOIN — łączenie tabel po kluczach

Tabela ksiazki nie przechowuje nazwy kategorii ani nazwy wydawnictwa. Zamiast tego ma kolumny kategoria_id i wydawnictwo_id, czyli numery wskazujące wiersze w innych tabelach. Żeby w raporcie pokazać czytelnikowi „Fantastyka” zamiast liczby 2, trzeba połączyć dwie tabele. Do tego służy JOIN.

INNER JOIN zwraca tylko te pary wierszy, które spełniają warunek połączenia. Warunek zapisujemy po słowie ON i najczęściej porównujemy klucz obcy z kluczem podstawowym. Jeśli książka ma kategoria_id = 2, a w tabeli kategorie istnieje wiersz o id = 2, para zostanie dopasowana. Jeśli numer nie istnieje, wiersz nie pojawi się w wyniku.

SELECT k.tytul, kat.nazwa AS kategoria, k.cena
FROM ksiazki k
JOIN kategorie kat ON k.kategoria_id = kat.id
WHERE kat.kod = 'FAN'
ORDER BY k.cena DESC;

Litery k i kat to aliasy tabel. Dzięki nim zapis jest krótszy, a kolumny o tych samych nazwach można jednoznacznie wskazać. W obu tabelach występuje na przykład kolumna id; zapis k.id oznacza identyfikator książki, a kat.id identyfikator kategorii. Bez aliasów baza nie wiedziałaby, o którą kolumnę chodzi.

JOIN można łączyć w łańcuch. Raport wypożyczeń wymaga uczniów, książek i pracowników: każde wypożyczenie wskazuje ucznia, książkę i osobę obsługującą. Każde kolejne JOIN dokłada kolumny do bieżącego wiersza, więc po trzech połączeniach mamy w jednym wierszu dane z czterech tabel. Kolejność łączenia nie zmienia wyniku, ale wpływa na czytelność zapytania.

SELECT u.nazwisko, ks.tytul, w.termin_zwrotu
FROM wypozyczenia w
JOIN uczniowie u ON w.uczen_id = u.id
JOIN ksiazki ks ON w.ksiazka_id = ks.id
WHERE w.status = 'przeterminowane'
ORDER BY w.termin_zwrotu, u.nazwisko;

Relacja wiele-do-wielu wymaga tabeli pośredniej. Książka może mieć kilku autorów, a autor może napisać wiele książek. Tabela ksiazki_autorzy przechowuje pary ksiazka_id i autor_id, a kolumna rola mówi, czy dana osoba jest autorem, czy współautorem. Aby pokazać autorów książki, trzeba połączyć trzy tabele: ksiazki, ksiazki_autorzy i autorzy.

SELECT ks.tytul, a.imie, a.nazwisko, ka.rola
FROM ksiazki ks
JOIN ksiazki_autorzy ka ON ks.id = ka.ksiazka_id
JOIN autorzy a ON ka.autor_id = a.id
WHERE ks.id = 4
ORDER BY ka.rola, a.nazwisko;

Przykłady z życia

1. Katalog biblioteczny. Czytelnik widzi tytuł i nazwę kategorii, choć w tabeli książek zapisany jest tylko numer kategorii. JOIN tłumaczy numer na nazwę w chwili wyświetlania, więc zmiana nazwy kategorii wymaga poprawienia jednego wiersza, a nie wszystkich książek.

2. Plan lekcji. Lekcja łączy nauczyciela, przedmiot, klasę i salę. Żadna z tych informacji nie powinna być wpisywana wielokrotnie w jednym wierszu planu. JOIN zbiera je z tabel słownikowych po identyfikatorach, podobnie jak biblioteka łączy książkę z autorem.

3. Zamówienie w sklepie. Pozycja zamówienia wskazuje produkt i zamówienie. Raport „co zamówił klient” wymaga połączenia tabeli pozycji z produktami i zamówieniami. Bez JOIN widzielibyśmy tylko numery, a nie nazwy produktów.

Geografia: mapa cyfrowa łączy warstwę miejscowości z warstwą gmin po identyfikatorze gminy. Nazwa gminy jest przechowywana raz, a miejscowości wskazują ją kluczem — dokładnie jak książki wskazują kategorię. JOIN pozwala nałożyć na siebie obie warstwy bez kopiowania nazw.

LEFT JOIN i RIGHT JOIN — wiersze bez pary

INNER JOIN pokazuje tylko dopasowane pary. To dobre pytanie „które książki mają kategorię”, ale złe pytanie „które kategorie nie mają żadnej książki”. Jeśli po prawej stronie nie ma pasującego wiersza, INNER JOIN po cichu go pomija. Wtedy wynik wygląda na kompletny, choć brakuje w nim najważniejszej informacji: braku.

LEFT JOIN zachowuje wszystkie wiersze z tabeli po lewej stronie, nawet jeśli nie mają pary po prawej. Dla niepasującego wiersza kolumny prawej tabeli przyjmują wartość NULL. Dzięki temu można zobaczyć zarówno uczniów, którzy wypożyczyli książkę, jak i tych, którzy nie wypożyczyli nic. Wiersz ucznia bez wypożyczeń ma w kolumnach wypożyczenia same wartości NULL.

SELECT u.imie, u.nazwisko, w.id AS wypozyczenie
FROM uczniowie u
LEFT JOIN wypozyczenia w ON u.id = w.uczen_id
ORDER BY u.nazwisko;

Wynik ma 47 wierszy: 45 wypożyczeń oraz dwóch uczniów bez żadnego wypożyczenia. Każdy z tych dwóch uczniów pojawia się raz, z NULL w kolumnie wypozyczenie. To właśnie ten NULL pozwala odróżnić „nie ma wypożyczenia” od „wypożyczenie ma numer zero”, którego w bazie nie używamy.

Filtr w WHERE a filtr w ON. To najczęstsza pułapka outer joinów. Warunek dopisany do ON decyduje, które wiersze prawej tabeli mogą się dopasować; warunek w WHERE sprawdzamy już po połączeniu i może odrzucić wiersze z NULL. Jeśli chcesz zachować wszystkich aktywnych uczniów i tylko oznaczyć tych z aktywnym wypożyczeniem, status umieść w ON.

SELECT u.imie, u.nazwisko, w.id AS aktywne_wypozyczenie
FROM uczniowie u
LEFT JOIN wypozyczenia w
  ON u.id = w.uczen_id AND w.status = 'w trakcie'
WHERE u.aktywny = 1
ORDER BY u.nazwisko;

Gdyby warunek w.status = 'w trakcie' przenieść do WHERE, uczniowie bez aktywnego wypożyczenia mieliby NULL w kolumnie statusu, a porównanie z NULL odrzuciłoby ich wiersze. LEFT JOIN zamieniłby się w praktyce w INNER JOIN, choć w zapisie nadal widnieje słowo LEFT.

RIGHT JOIN jest lustrzanym odbiciem LEFT JOIN. Zachowuje wszystkie wiersze z tabeli po prawej stronie. Zamiast pisać FROM wypozyczenia w RIGHT JOIN uczniowie u, można zamienić tabele miejscami i napisać FROM uczniowie u LEFT JOIN wypozyczenia w — wynik będzie ten sam, a zapytanie czytelniejsze. W praktyce częściej używa się LEFT JOIN, bo łatwiej czyta się je od tabeli, którą chcemy zachować w całości.

SELECT u.nazwisko, w.id AS wypozyczenie
FROM wypozyczenia w
RIGHT JOIN uczniowie u ON w.uczen_id = u.id
ORDER BY u.nazwisko;

Przykłady z życia

1. Lista uczestników wycieczki. Wychowawca ma pełną listę uczniów i osobno zapisane zgody. LEFT JOIN pokazuje wszystkich uczniów, a brak zgody jako NULL. Dzięki temu od razu widać, kto jeszcze nie oddał podpisu, zamiast zgubić te osoby w wyniku INNER JOIN.

2. Klienci bez zamówień. Sklep chce wysłać ofertę osobom, które nigdy nic nie kupiły. LEFT JOIN klientów z zamówieniami i warunek zamowienie.id IS NULL daje właśnie taką listę. To klasyczny wzorzec „kto nie ma powiązanego rekordu”.

3. Awarie bez zgłoszenia. Serwis ma listę maszyn i listę zgłoszeń. LEFT JOIN pokazuje także maszyny, które nie mają żadnego zgłoszenia. Brak zgłoszenia może oznaczać, że wszystko działa, albo że nikt nie zauważył problemu — dlatego raport jest potrzebny.

Geografia: lista stacji meteorologicznych połączona z pomiarami przez LEFT JOIN pokaże także stacje, które nie przesłały danych. INNER JOIN ukryłby je, a właśnie brak pomiaru jest często najważniejszą informacją w monitoringu środowiska.

Zapytania wielotabelowe — raport wypożyczeń

Prawdziwy raport rzadko mieści się w jednej tabeli. Biblioteka chce pokazać nazwisko ucznia, jego klasę, tytuł książki, kategorię i kwotę kary. Te informacje są rozłożone na pięć tabel: uczniowie, klasy, ksiazki, kategorie i wypozyczenia. Zapytanie wielotabelowe łączy je w jeden czytelny wiersz.

Zaczynamy od tabeli zdarzeń, czyli wypozyczenia. Każdy jej wiersz wskazuje ucznia, książkę i pracownika. Dołączamy uczniów po w.uczen_id = u.id, klasy po u.klasa_id = kl.id, książki po w.ksiazka_id = ks.id, a kategorie po ks.kategoria_id = kat.id. Każde JOIN dokłada kolumny do bieżącego wyniku, więc po całym łańcuchu mamy w jednym wierszu dane z pięciu tabel.

SELECT u.nazwisko, kl.nazwa AS klasa, ks.tytul,
       kat.nazwa AS kategoria, w.kara
FROM wypozyczenia w
JOIN uczniowie u ON w.uczen_id = u.id
JOIN klasy kl ON u.klasa_id = kl.id
JOIN ksiazki ks ON w.ksiazka_id = ks.id
JOIN kategorie kat ON ks.kategoria_id = kat.id
WHERE w.kara > 0
ORDER BY w.kara DESC, u.nazwisko;

Kolejność łączenia nie jest przypadkowa. Zaczynamy od tabeli, która ma najwięcej wierszy w raporcie, i dołączamy do niej tabele słownikowe. Dzięki temu łatwo zauważyć, którego klucza brakuje. Warto też od razu nadawać aliasy i kwalifikować kolumny: w kilku tabelach występuje id i nazwa, więc zapis bez aliasu byłby niejednoznaczny.

Połączenie tabel a grupowanie to dwa etapy. Najpierw JOIN tworzy pełne wiersze, a dopiero potem GROUP BY zbiera je w grupy. Raport „ile wypożyczeń ma każda klasa” wymaga połączenia klas, uczniów i wypożyczeń, a następnie grupowania po nazwie klasy. COUNT(w.id) policzy wypożyczenia, a SUM(w.kara) sumę kar w danej klasie.

SELECT kl.nazwa AS klasa,
       COUNT(w.id) AS wypozyczenia,
       SUM(w.kara) AS suma_kar
FROM klasy kl
JOIN uczniowie u ON kl.id = u.klasa_id
JOIN wypozyczenia w ON u.id = w.uczen_id
GROUP BY kl.nazwa
ORDER BY wypozyczenia DESC, kl.nazwa;

Raport przekrojowy grupuje po dwóch kolumnach. Zestawienie klas i statusów wypożyczeń pokazuje, ile w każdej klasie jest zwrotów, książek w trakcie i przeterminowań. Każda para klasa–status tworzy osobną grupę, więc wynik ma więcej wierszy niż liczba klas. To nadal jedno zapytanie, choć tabela wygląda jak kilka raportów zestawionych obok siebie.

SELECT kl.nazwa AS klasa, w.status, COUNT(*) AS liczba
FROM klasy kl
JOIN uczniowie u ON kl.id = u.klasa_id
JOIN wypozyczenia w ON u.id = w.uczen_id
GROUP BY kl.nazwa, w.status
ORDER BY kl.nazwa, w.status;

Przykłady z życia

1. Zebranie z rodzicami. Wychowawca chce jednym raportem pokazać uczniów, ich klasy i liczbę wypożyczonych książek. Bez łączenia tabel musiałby otwierać kilka arkuszy i przepisywać dane ręcznie. Jedno zapytanie z JOIN i GROUP BY daje gotową tabelę do wydruku.

2. Raport sprzedaży. Firma analizuje sprzedaż według kategorii produktów i regionów. Tabele zamówień, pozycji, produktów i regionów łączy się po kluczach, a następnie grupuje po dwóch wymiarach. To ten sam schemat co raport klas i statusów wypożyczeń.

3. Turniej szkolny. Tabela meczów wskazuje drużyny i sędziów. Raport „punkty według drużyny i rundy” wymaga połączenia meczów z drużynami oraz grupowania po dwóch kolumnach. Bez JOIN widzielibyśmy tylko numery drużyn, a nie ich nazwy.

Ekonomia: analityk biznesowy mówi o „ziarnie raportu” — o tym, co opisuje jeden wiersz. W raporcie wypożyczeń ziarnem jest jedno wypożyczenie, dopóki nie dołączymy autorów. Dodanie tabeli autorów zmienia ziarno na parę książka–autor, więc sumy trzeba liczyć ostrożnie. To pojęcie wraca w każdym raporcie przekrojowym.

Ćwiczenia SQL

Zadania do samodzielnego wykonania w edytorze SQL.

• 1Katalog dla rodzicówPołącz książki z kategoriami i wydawnictwami, aby przygotować katalog literatury pięknej i fantastyki.Zadanie i podpowiedzi →
• 2Aktywne wypożyczenia do przypomnieniaPołącz wypożyczenia z uczniami, klasami i książkami, aby przygotować listę książek w trakcie.Zadanie i podpowiedzi →
• 3Kto jeszcze nic nie wypożyczyłUżyj LEFT JOIN, aby znaleźć aktywnych uczniów bez żadnego wypożyczenia.Zadanie i podpowiedzi →
• 4Ranking autorówPołącz autorów z książkami przez tabelę pośrednią i pokaż autorów z co najmniej dwiema książkami.Zadanie i podpowiedzi →