SQL JOIN — ćwiczenia z odpowiedziami na bazie książek i autorów

Na tej lekcji uczniowie połączą dwie tabele: książki i ich autorów. Zobaczą, dlaczego dane rozdziela się na osobne tabele, jak złączyć je przez klucz obcy i czym różni się INNER JOIN od LEFT JOIN, gdy któraś książka nie ma przypisanego autora albo autor nie ma jeszcze żadnej książki.

Materiał jest przeznaczony na 45 minut dla uczniów, którzy znają SELECT, WHERE, ORDER BY i aliasy — na przykład po lekcji SQL SELECT z ćwiczeniami. Zapytania są przygotowane dla MySQL 8 lub MariaDB, np. w phpMyAdmin.

Przygotowanie bazy

Pobierz pakiet z sekcji materiałów. W pustej bazie ćwiczeniowej zaimportuj raz plik baza.sql. Tworzy on dwie tabele — autorzy (3 wiersze) i ksiazki (6 wierszy) — i nie usuwa niczego, co już istnieje. Ponowny import w tej samej bazie zakończy się błędem istniejącej tabeli; wtedy użyj innej pustej bazy. Nie pracuj na bazie produkcyjnej.

Import najlepiej wykonać przed lekcją. Na początku zajęć wystarczy, że uczniowie sprawdzą liczbę wierszy: SELECT COUNT(*) FROM ksiazki; powinno zwrócić 6.

Dane, na których pracujemy

Tabela autorzy:

Tabela ksiazki:

Kolumna autor_id to klucz obcy: wskazuje id w tabeli autorzy. Zamiast powtarzać imię i nazwisko przy każdej książce, zapisujemy tylko numer autora. Dzięki temu zmiana nazwiska wymaga poprawy jednego wiersza, a nie wszystkich książek. Autorzy i książki są fikcyjni.

Dwa wiersze celowo „nie pasują”: książka *Zielony balkon* nie ma autora (NULL), a Ewa Zielińska nie ma jeszcze żadnej książki. Na tych przypadkach widać różnicę między rodzajami złączeń.

Cele i plan lekcji

Po zajęciach uczeń potrafi:

• wyjaśnić rolę klucza głównego i obcego w dwóch powiązanych tabelach;
• napisać INNER JOIN z warunkiem ON i aliasami tabel;
• użyć LEFT JOIN, aby zachować wiersze bez dopasowania, i odczytać w wyniku NULL;
• znaleźć wiersze bez dopasowania warunkiem IS NULL.

INNER JOIN: książki z nazwiskiem autora

Zanim uczniowie napiszą zapytanie, poproś ich, żeby ręcznie odczytali autora książki *Tajemnica mapy*: autor_id = 2, więc w tabeli autorzy szukamy id = 2 — to Piotr Lis. Złączenie robi dokładnie to samo dla każdego wiersza.

SELECT k.tytul, a.imie, a.nazwisko
FROM ksiazki AS k
INNER JOIN autorzy AS a ON k.autor_id = a.id;

Wynik ma 5 wierszy. k i a to aliasy tabel — skracają zapis i pokazują, z której tabeli pochodzi każda kolumna. Warunek po ON mówi, które wiersze do siebie pasują: wartość autor_id książki ma być równa id autora.

INNER JOIN zwraca tylko pary, które pasują do siebie. Brakuje książki *Zielony balkon*, bo NULL nie jest równe żadnemu id. Nie ma też Ewy Zielińskiej — żadna książka na nią nie wskazuje. Samo słowo JOIN w MySQL oznacza to samo co INNER JOIN.

LEFT JOIN: zachowaj wszystkie wiersze z lewej tabeli

SELECT k.tytul, a.nazwisko
FROM ksiazki AS k
LEFT JOIN autorzy AS a ON k.autor_id = a.id;

Teraz wynik ma 6 wierszy. LEFT JOIN zachowuje każdy wiersz tabeli zapisanej po lewej stronie (po FROM), nawet bez dopasowania. Dla *Zielonego balkonu* kolumna nazwisko ma wartość NULL, czyli „brak danych”.

Kolejność tabel ma znaczenie. Gdy zamienimy je miejscami (FROM autorzy AS a LEFT JOIN ksiazki AS k), zachowani zostaną wszyscy autorzy: pojawi się Ewa Zielińska z NULL w miejscu tytułu, a zniknie *Zielony balkon*.

Trzy zadania z JOIN

Zadanie 1. Informatyka z autorami. Wyświetl tytuł, imię i nazwisko autora dla książek z kategorii informatyka, posortowane alfabetycznie według tytułu. Użyj INNER JOIN i aliasów tabel. Podaj liczbę wierszy wyniku.

Zadanie 2. Autor bez książek. Wyświetl imię i nazwisko autorów, którzy nie mają w bazie żadnej książki. Użyj LEFT JOIN z tabelą autorzy po lewej stronie oraz warunku na NULL.

Zadanie 3. Grube książki — także bez autora. Wyświetl tytuł, liczbę stron i nazwisko autora dla książek mających ponad 150 stron, od najgrubszej. Książka bez autora też ma się pojawić w wyniku. Nadaj kolumnie z nazwiskiem alias autor.

Dla chętnych: wyświetl każdego autora i liczbę jego książek jako kolumnę liczba_ksiazek, łącznie z autorem, który ma 0 książek. Potrzebne będą COUNT i GROUP BY. Zastanów się, dlaczego COUNT(k.id) daje inny wynik niż COUNT(*).

Rozwiązania z oczekiwanymi wynikami

Zadanie 1:

SELECT k.tytul, a.imie, a.nazwisko
FROM ksiazki AS k
INNER JOIN autorzy AS a ON k.autor_id = a.id
WHERE k.kategoria = 'informatyka'
ORDER BY k.tytul;

Wynik ma 3 wiersze. WHERE filtruje już złączone wiersze, dlatego można w nim używać kolumn z obu tabel.

Zadanie 2:

SELECT a.imie, a.nazwisko
FROM autorzy AS a
LEFT JOIN ksiazki AS k ON k.autor_id = a.id
WHERE k.id IS NULL;

Wynik: jeden wiersz — Ewa Zielińska. Najpierw LEFT JOIN tworzy wiersz Zielińskiej z NULL we wszystkich kolumnach książki, a potem WHERE k.id IS NULL zostawia tylko takie wiersze. Warunek k.id = NULL nie zadziała: porównanie z NULL nigdy nie jest prawdziwe, dlatego używa się IS NULL.

Zadanie 3:

SELECT k.tytul, k.strony, a.nazwisko AS autor
FROM ksiazki AS k
LEFT JOIN autorzy AS a ON k.autor_id = a.id
WHERE k.strony > 150
ORDER BY k.strony DESC;

Pułapka: z INNER JOIN wynik miałby tylko 3 wiersze, bo zniknąłby *Zielony balkon*. Wymaganie „książka bez autora też ma się pojawić” wskazuje na LEFT JOIN. W phpMyAdmin wartość NULL jest zwykle wyróżniona kursywą.

Dla chętnych:

SELECT a.imie, a.nazwisko, COUNT(k.id) AS liczba_ksiazek
FROM autorzy AS a
LEFT JOIN ksiazki AS k ON k.autor_id = a.id
GROUP BY a.id, a.imie, a.nazwisko
ORDER BY liczba_ksiazek DESC;

Wynik: Nowak 3, Lis 2, Zielińska 0. COUNT(k.id) pomija wartości NULL, więc autor bez książek dostaje 0. COUNT(*) liczy wiersze, a Zielińska ma w złączeniu jeden wiersz (z NULL) — otrzymałaby 1.

Co sprawdzić przy błędzie lub złym wyniku?

Pytania kontrolne i punktacja

1. Do czego służy kolumna autor_id w tabeli ksiazki?
2. Ile wierszy zwróci INNER JOIN książek z autorami, a ile LEFT JOIN z książkami po lewej? Dlaczego?
3. Dlaczego w zadaniu 2 używamy IS NULL, a nie = NULL?

Odpowiedzi: autor_id jest kluczem obcym — wskazuje wiersz autora w tabeli autorzy. INNER JOIN zwraca 5 wierszy, bo pomija książkę bez autora; LEFT JOIN zwraca 6, bo zachowuje wszystkie książki i wpisuje NULL w brakujące kolumny. Porównanie z NULL daje wynik nieznany, a nie prawdę, więc = NULL nie wybierze żadnego wiersza; do sprawdzania braku wartości służy IS NULL.

Proponowana punktacja (razem 10 punktów): zadanie 1 — poprawne złączenie z ON 2 pkt, filtr i sortowanie 1 pkt; zadanie 2 — LEFT JOIN z właściwą kolejnością tabel 2 pkt, IS NULL 1 pkt; zadanie 3 — LEFT JOIN 1 pkt, warunek, sortowanie i alias 1 pkt; pytanie 2 z uzasadnieniem — 2 pkt. Zadanie dla chętnych nie jest wymagane do kompletu punktów.

Baza SQL i karta pracy do pobrania

• Pakiet SQL: baza do importu, polecenia, rozwiązania i karta pracy
• Karta pracy ucznia — otwórz i wydrukuj

Pakiet zawiera baza.sql, start.sql z poleceniami, rozwiazania.sql z zapytaniami z tej lekcji oraz instrukcję importu. Materiały są dostępne bez logowania. Klucz jest publiczny i służy nauce oraz samodzielnemu sprawdzeniu, nie poufnemu sprawdzianowi.

Co dalej po JOIN?

Kolejne zajęcia warto poświęcić funkcjom agregującym z GROUP BY i HAVING, a potem złączeniom więcej niż dwóch tabel, np. książek, autorów i wypożyczeń. Ten zakres, razem z projektowaniem tabel i kluczami, rozwija kurs SQL w TablicaPro z prezentacjami, ćwiczeniami i testami.

Źródła techniczne: składnia JOIN w dokumentacji MySQL 8.4, praca z wartościami NULL w MySQL oraz opis JOIN w dokumentacji MariaDB. Jeśli łączysz bazy danych z programowaniem stron, zobacz także lekcję PHP od podstaw.