Podstawy użycia instrukcji JOIN w bazach danych MySQL

JOIN jest instrukcją pozwalającą pobrać powiązane dane z więcej niż jednej tabeli podczas jednego zapytania do serwera bazy danych. Niestety, JOIN jest zbyt często źle wytłumaczony lub nawet pomijany, jako zbyt trudny, w kursach dla początkujących adeptów baz danych. A szkoda, bo dobrze zaprojektowane zapytanie z JOIN-em potrafi niesamowicie ułatwić życie i przyspieszyć wykonywanie skryptu.

Posłużę się przykładem z tej strony: skrypt, który generował wykresy dobowe i tygodniowe z trzech czujników temperatury przed optymalizacją wykonywał się około 55 sekund. Po przeprojektowaniu zapytań do bazy danych, przy użyciu instrukcji JOIN i UNION, wygenerowanie tych samych wykresów trwa średnio 17 sekund. To ponad 3x szybciej!

Najłatwiej opisać użycie instrukcji JOIN na przykładzie dwóch tabel z powiązanymi danymi: w jednej z nich, osoby, umieściłem listę imion z identyfikatorami języków programowania, jakimi się posługują. W drugiej, jezyki, znajdują się nazwy języków programowania. Zakładam, że nie każda osoba zna jakiś język programowania, a niektóre z nich nie są znane przez żadną z osób.

join-1join-2

A to kod do utworzenia powyższych tabel:

 CREATE TABLE `jezyki` (
 `id` TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
 `nazwa` VARCHAR(10) NULL DEFAULT NULL,
 PRIMARY KEY (`id`)
 )
 COLLATE='utf8_general_ci'
 ENGINE=InnoDB
CREATE TABLE `osoby` (
 `imie` VARCHAR(10) NULL DEFAULT NULL,
 `jezyk` TINYINT(3) UNSIGNED NULL DEFAULT NULL,
 INDEX `FK_osoby_jezyki` (`jezyk`)
 )
 COLLATE='utf8_general_ci'
 ENGINE=InnoDB;

Klucze obce

Ponieważ w powyższym przykładzie korzystam z silnika InnoDB, warto zastosować jedną z ciekawych funkcji, którą on umożliwia, a mianowicie z kluczy obcych. Pozwala ona na powiązanie ze sobą dwóch tabel, w których występują te same dane – tutaj są to identyfikatory języków programowania. Dzięki dodaniu klucza obcego do tabeli osoby:

  • Każda zmiana identyfikatora (id) w tabeli jezyki spowoduje aktualizację powiązanych identyfikatorów w bazie osoby
  • Próba usunięcia rekordu z tabeli jezyki, dla którego istnieje co najmniej jedna osoba wygeneruje błąd i przerwie operacje
ALTER TABLE `osoby`
ADD CONSTRAINT `FK_jezyk` FOREIGN KEY (`jezyk`) REFERENCES `jezyki` (`id`) ON UPDATE CASCADE;

Składnia JOIN

SELECT tabela1.pole1, tabela1.pole2, tabela2.pole1, tabela1.pole2, [...] from tabela1
[LEFT/RIGHT/...] JOIN tabela2
ON tabela1.pole2 = tabela2.pole2
where [...]

Istotą polecenia JOIN jest zestawienie dwóch lub więcej tabeli „obok siebie”, porównując rekordy za pomocą słowa kluczowego ON.
Składnia podobna jest do klasycznego SELECT, z tym, że niezbędne jest dla każdej kolumny podanie nazwy tabeli. Nie wystarczy więc napisać pole1, bo może ono występować w obu zestawianych tabelach. Następnie należy umieścić instrukcje JOIN, „doklejającą” tabele obok już wybranej oraz, za pomocą ON, wskazać, które dwa pola mają się pokrywać. Na załączonej grafice widać wyraźnie, że zwrócone rekordy dopasowane są polami jezyki.id oraz osoby.jezyk.
9

Typy instrukcji JOIN:

JOIN/INNER JOIN

select jezyki.nazwa, osoby.imie from jezyki
join osoby
on jezyki.id = osoby.jezyk

join-3join

Ta podstawowa instrukcja JOIN zwraca wyłącznie rekordy, które znajdują się w obydwu tabelach. Jest stosowana równie często, co dalej opisana: LEFT JOIN

LEFT JOIN

select jezyki.nazwa, osoby.imie from jezyki
left join osoby
on jezyki.id = osoby.jezyk

join-4left-join
LEFT JOIN wyróżnia tabelę z instrukcji select (lewą, bo osoby zostały dołączone po prawej stronie). W tym przypadku zwrócone zostaną wszystkie rekordy z tabeli jezyki, nawet gdy nie mają swoich dopasowań w tabeli osoby.

RIGHT JOIN

select jezyki.nazwa, osoby.imie from jezyki
right join osoby
on jezyki.id = osoby.jezyk

join-5right-join
RIGHT JOIN, dla odmiany, „wyróżnia” tabelę prawą, czyli dołączaną. Tutaj zapytanie zwróci wszystkie rekordy tabeli osoby, nawet, gdy nie każdy z nich ma przypisany odpowiadający język programowania.
Ta odmiana JOIN jest stosowana dużo rzadziej niż LEFT. Do „good practice” należy takie konstruowanie kwerend, by stosować raczej LEFT niż RIGHT.

OUTER JOIN

select jezyki.nazwa, osoby.imie from jezyki
left join osoby
on jezyki.id = osoby.jezyk
union
select jezyki.nazwa, osoby.imie from jezyki
right join osoby
on jezyki.id = osoby.jezyk

join-6outer-join
Polecenie OUTER JOIN, choć obecne w innych odmianach SQLa, a MySQLu nie występuje. Aby osiągnąć podobny efekt, należy zastosować polecenie UNION, łączące wyniki dwóch różnych zapytań. OUTER JOIN jest połączeniem dwóch wyżej opisanych konstrukcji – za jego pomocą otrzymamy rekordy wspólne dla obu tabel, a także unikatowe dla każdej z nich oddzielnie.

LEFT/RIGHT JOIN z wykluczeniem

select jezyki.nazwa, osoby.imie from jezyki
left join osoby
on jezyki.id = osoby.jezyk
where osoby.imie is null

join-7
left-outer-join

select jezyki.nazwa, osoby.imie from jezyki
right join osoby
on jezyki.id = osoby.jezyk
where jezyki.nazwa is null

join-8
right-outer-join
Dzięki zastosowaniu klauzuli WHERE […] is null możliwe jest wyświetlenie rekordów, które znajdują się w jednej z tabel, ale nie ma ich w drugiej z nich.

Do zarządzania bazami danych używam programu HeidiSQL. Jest darmowy i wygodniejszy w użyciu niż popularny phpMyAdmin.

Udostępnij:Share on FacebookEmail this to someoneWykop!Share on Google+Print this pageShare on TumblrTweet about this on Twitter

Dodaj komentarz

Twój adres email nie zostanie opublikowany.