Łączenie tabel klauzulą JOIN

Laczenie tabel pozwala uzyskac informacje z wielu tabel w jednym zapytaniu. Najczesciej uzywane operacje to: INNER JOIN, LEFT JOIN, RIGHT JOIN oraz FULL JOIN. Kazdy z nich zachowuje sie inaczej i daje inne wyniki w zaleznosci od relacji miedzy danymi.

Podstawowe łączenie tabel relacją

Przyklad:

SELECT Studenci.Imie, Studenci.Nazwisko, Oceny.Ocena
FROM Studenci
INNER JOIN Oceny ON Studenci.ID = Oceny.StudentID
  • Zapytanie wypisuje imie nazwisko z tabeli studenci i ocenę z tabeli oceny

1. INNER JOIN

INNER JOIN zwraca tylko rekordy, ktore maja pasujace wartosci w obu tabelach — czyli wiersze z dopasowaniem w obu tabelach.

Przyklad:

SELECT Studenci.Imie, Studenci.Nazwisko, Kursy.NazwaKursu
FROM Studenci
INNER JOIN Oceny ON Studenci.ID = Oceny.StudentID
INNER JOIN Kursy  ON Oceny.KodKursu = Kursy.KodKursu;

Wynik zawiera tylko studentow, ktorzy maja przypisane oceny w kursach.

2. LEFT JOIN (LEFT OUTER JOIN)

LEFT JOIN zwraca wszystkie rekordy z lewej tabeli oraz odpowiadajace im rekordy z prawej tabeli. Jezeli nie ma dopasowania, kolumny z prawej tabeli beda NULL.

Przyklad:

SELECT Studenci.Imie, Studenci.Nazwisko, Kursy.NazwaKursu
FROM Studenci
LEFT JOIN Oceny ON Studenci.ID = Oceny.StudentID
LEFT JOIN Kursy ON Oceny.KodKursu = Kursy.KodKursu;

Zwracamy wszystkich studentow — jesli nie maja ocen, kolumny kursow beda NULL.

3. RIGHT JOIN (RIGHT OUTER JOIN)

RIGHT JOIN zwraca wszystkie rekordy z prawej tabeli oraz odpowiadajace im rekordy z lewej tabeli. Jezeli brak dopasowania w lewej tabeli, kolumny z lewej beda NULL.

Przyklad:

SELECT Studenci.Imie, Studenci.Nazwisko, Kursy.NazwaKursu
FROM Studenci
RIGHT JOIN Oceny ON Studenci.ID = Oceny.StudentID
RIGHT JOIN Kursy ON Oceny.KodKursu = Kursy.KodKursu;

Ten przyklad zwroci wszystkie rekordy z tabeli Kursy, nawet jesli nie ma odpowiadajacych studentow.

Uwaga: MySQL nie wspiera bezposrednio FULL JOIN — mozna go zasymulowac przez UNION LEFT/RIGHT JOIN.

Tworzenie i wypelnianie tabel (przyklady)

CREATE TABLE Studenci (
    ID INT PRIMARY KEY,
    Imie VARCHAR(50),
    Nazwisko VARCHAR(50)
);

INSERT INTO Studenci (ID, Imie, Nazwisko) VALUES
(1, 'Anna', 'Kowalska'),
(2, 'Jan', 'Nowak'),
(3, 'Piotr', 'Zielinski'),
(4, 'Magda', 'Wisniewska'),
(5, 'Tomasz', 'Lewandowski'),
(6, 'Katarzyna', 'Mazur'),
(7, 'Adam', 'Szymanski'),
(8, 'Ewa', 'Lis'),
(9, 'Grzegorz', 'Baran'),
(10, 'Pawel', 'Wolski'),
(11, 'Michal', 'Nowicki'),
(12, 'Zofia', 'Kowalska'),
(13, 'Lukasz', 'Kaczmarek'),
(14, 'Joanna', 'Sikora'),
(15, 'Krzysztof', 'Wozniak');

CREATE TABLE Kursy (
    KodKursu VARCHAR(10) PRIMARY KEY,
    NazwaKursu VARCHAR(100),
    LiczbaGodzin INT
);

INSERT INTO Kursy (KodKursu, NazwaKursu, LiczbaGodzin) VALUES
('CS101', 'Podstawy programowania', 30),
('CS102', 'Algorytmy', 40),
('CS103', 'Bazy danych', 35),
('CS104', 'Inzynieria oprogramowania', 45),
('CS105', 'Grafika komputerowa', 25),
('CS106', 'Sieci komputerowe', 50),
('CS107', 'Bezpieczenstwo systemow', 20),
('CS108', 'Sztuczna inteligencja', 60),
('CS109', 'Systemy operacyjne', 55),
('CS110', 'Programowanie obiektowe', 50),
('CS111', 'Podstawy kryptografii', 45),
('CS112', 'Programowanie w Pythonie', 30),
('CS113', 'Rozproszone systemy', 50),
('CS114', 'Bazy danych NoSQL', 40),
('CS115', 'Zaawansowane techniki programowania', 60);

CREATE TABLE Oceny (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    StudentID INT,
    KodKursu VARCHAR(10),
    Ocena DECIMAL(3,1),
    FOREIGN KEY (StudentID) REFERENCES Studenci(ID),
    FOREIGN KEY (KodKursu)  REFERENCES Kursy(KodKursu)
);

INSERT INTO Oceny (StudentID, KodKursu, Ocena) VALUES
(1, 'CS101', 4.0),
(2, 'CS101', 3.5),
(3, 'CS102', 5.0),
(4, 'CS103', 3.0),
(5, 'CS104', 4.5),
(6, 'CS105', 4.0),
(7, 'CS106', 5.0),
(8, 'CS107', 2.5),
(9, 'CS108', 3.5),
(9, 'CS109', 2.0),
(10,'CS109', 4.0);

Cwiczenia

Cwiczenie 1: INNER JOIN

  • Zadanie 1: Wyswietl imie i nazwisko studentow oraz nazwe kursu, na ktory sa wpisani (uzyj INNER JOIN).
  • Zadanie 2: Wyswietl studentow, ktorzy maja ocene wyzsza niz 4, oraz ich kursy.

Cwiczenie 2: LEFT JOIN

  • Zadanie 1: Wyswietl wszystkich studentow oraz kursy, na ktore sa zapisani (lub NULL, jesli nie sa zapisani na zadny kurs), uzywajac LEFT JOIN.
  • Zadanie 2: Pokaz studentow, ktorzy nie sa zapisani na zadny kurs.

Cwiczenie 3: RIGHT JOIN

  • Zadanie 1: Wyswietl wszystkie kursy oraz studentow, ktorzy na nie uczeszczaja, uzywajac RIGHT JOIN.
  • Zadanie 2: Pokaz kursy, na ktore nie jest zapisany zadny student.

Cwiczenie 4: Zaawansowane laczenie

  • Zadanie 1: Wyswietl imie i nazwisko studentow, ale ich ocena jest mniejsza niz 3 lub nie maja oceny.

Zadanie 5

Wyświetl imię, nazwisko studenta oraz ocenę, ale tylko dla studentów, którzy mają ocenę niższą niż 3.0. Wyniki posortuj rosnąco według oceny.

Zadanie 6

Wyświetl kod kursu, nazwę kursu oraz ocenę, ale tylko dla kursów, które mają liczbę godzin większą niż 40. Posortuj wynik alfabetycznie według nazwy kursu.

Zadanie 7

Wyświetl wszystkich studentów oraz ich oceny (NULL dla tych bez ocen). Pokaż tylko studentów, których imię zawiera literę „a”.

Zadanie 8

Wyświetl wszystkie kursy wraz z przypisanymi ocenami studentów (NULL, jeśli brak oceny), ale tylko dla kursów o liczbie godzin mniejszej niż 40.

Zadanie 9

Wyświetl wszystkich studentów, którzy mają ocenę dokładnie równą 5.0, wraz z kodem kursu oraz oceną.

Zadanie 10

Dla każdego kursu oblicz średnią ocenę, ale pokaż tylko te kursy, dla których ta średnia jest większa niż 4.0.

Zadanie 11

Policz, ile ocen posiada każdy student i posortuj wynik malejąco według liczby ocen.

Wynik powinien zawierać: imię, nazwisko, liczba ocen.

Zadanie 12

Wyświetl listę wszystkich kursów i przypisane do nich oceny, ale pokaż tylko te kursy, które nie mają żadnej oceny.

Sprawozdanie z zadań 5 – 12.