Wielu z nas spotkało się z problemem polegającym na tym, że potrzebne nam dane rozmieszczone są w kilku tabelach bazay danych. Do wybierania danych, które rozstrzelone są po całej bazie w różnych tabelach i mają jeden czynnik wspólny służy komenda JOIN, która występuje w połączeniu z frazami LEFT, RIGHT, INNER i innymi. Poniższy wpis przybliży wam ich działanie.

Do demonstracji niezbędne będą 2 tabele w bazie danych, które będą posiadały jeden element wspólny. Jego nazwa może być identyczna bądź różna, jednak przy identycznym nazewnictwie mamy możliwość zastosowania szerszego wachlarza komend, za pomocą których można wybierać dane wspólne z obu tabel.

efektem są 2 tabele postaci:

Teraz, gdy już mamy podstawę do ćwiczeń przedstawię zestaw komend za pomocą których wybierzemy dane na podstawie zawartości obu tabel. Pracownikom przypiszemy działy, departamentom pracowników w różnych wariantach.

INNER JOIN

Jest to najbardziej popularna operacja używana w operacjach. Efektem jest kombinacja wartości z 2 tabeli które mają wspólny element wspólny , wskazany w komendzie jako "łącznik" tabel.

Elementem wspólnym tabel , na podstawie którego wykonywane jest porównywanie jest "ID_dzialu". Elementy według których dokonywane są dopasowania nie muszą nazywać się identycznie, mozę to być równie dobrze ID_dzialu inp NR_pracownika. Nazewnictwo nie ma znaczenia, istotne natomiast są wartości aby były tego samego typu o były porównywalne.

Przy wskazywaniu elementu"łącznika" należy sprecyzować również tabele z której wartość pochodzi: pracownik.ID_dzialu ponieważ polecenie inner join pozwala na łaczenie więcej niż 2 tabel.

SELECT *FROM   pracownik
       INNER JOIN dzial
          ON pracownik.ID_dzialu = dzial.ID_dzialu'

Efekt jest następujący:

Wiersze z tabel zostały dopasowane tak aby nazwisku o pewnym id był przypisany dział o identycznej wartości id.

Komendę możemy wzbogacić oczywiście o definicję warunków sortowań np:

SELECT *FROM   pracownik INNER JOIN dzial ON pracownik.ID_dzialu = dzial.ID_dzialu where dzial.`ID_dzialu`<103 order by nazwisko DESC

efekt:

można również zastosować bardziej wyraźny zapis:

SELECT * FROM   pracownik, dzial
WHERE  pracownik.ID_dzialu = dzial.ID_dzialu

efekt tej komendy jest analogiczny jak efekt komendy pierwszej 

-------------------------------------------------------------------------------------------------

Equi-join

Warto wiedzieć o istnieniu Equi-join znanego również jako equijoin, który jest specyficznym przypadkiem join'a używającego tylko równości w porównaniach. Używanie komparatorów innych niż "=" dyskwalifikuje join jako equi-join. Przykładem equi-join jest ostatni przykład:

SELECT * FROM   pracownik, dzial
WHERE  pracownik.ID_dzialu = dzial.ID_dzialu

SQL zapewnia opcjonalną skróconą wersję przewidzianą dla equi-join poprzez użycie konstrukcji:

SELECT *FROM   pracownik
       INNER JOIN dzial
          USING (ID_dzialu)

efektem której jest również wynik postaci:

--------------------------------------------------------------------------------------------------------

Natural join

Natural join oferuje dalszą specjalizację equi-join. "łącznik" tabel pojawia się automatycznie poprzez porównywanie kolumn obu tabel na podstawie nazw kolumn któe muszą być identyczne. np:

SELECT * FROM   pracownik NATURAL JOIN dzial

-------------------------------------------------------------------------------------------------------- 

Cross join

Cross join zwraca iloczyn zbiorów rekordów z obu tabel. Innymi słowy Cross join łączy każdy wiersz kolumny 1 z każdym wierszem kolumny 2.

SELECT * FROM   pracownik CROSS JOIN dzial

cross join można również zastosować z postaci ukrytej,

przykład  ukrytego cross join

SELECT * FROM   pracownik, dzial;

efekt jak wyżej 

Zmodyfikuję trochę zawartość tabeli aby prawidłowo pokazać następne przykłady (zmiana id działu administracja):

Outer joins

Outer joins nie wymagają tego, aby w każdej z tabel był element o tej samej wartości elementu "łączącego" tabele.  Zachowywany jest każdy rekord tabeli nawet jeśli żaden rekord drugiej tabeli do niego nie pasuje.  Rozróżniamy left outer joins, right outer joins, and full outer joins w zależności od tego, z której tabeli chcemy zachować wszystkie wartości.

-------------------------------------------------------------------------------------------

Left outer join / left join

Wynikiem operacji left outer join (lub wersja uproszczona left join) dla tabel A i B jest tabela zawierająca wszystkie rekordy z tabeli lewej (A) nawet jeśli wynik join nie znajduje odpowiedników z tabeli prawej - więc jeśli żaden z rekordów tabeli B nie pasuje do rekordów z tabeli A , join zwróci wiersze tabeli A z odpowiednią ilością wartości NULL w każdej kolumnie tabeli B.

Podsumowując left outer join zwraca wszystkie wartości z tabeli lewej + pasujące wartości z tabeli prawej ( w przypadku braku dopasowania pojawiają się pola NULL).

W sytuacji gdy do jednego wiersza tabeli lewej dopasowanych jest więcej wierszy z tabeli prawej, wiersze z tabeli lewej zostaną powtórzone tak, aby wszystkie dopasowania z tabeli prawej mogły być poprawnie wyświetlone.

SELECT *  FROM   pracownik  LEFT OUTER JOIN dzial 
ON pracownik.ID_dzialu = dzial.ID_dzialu

Jak łatwo zauważyć gdy zmianie uległo id administracji Kulikowski i Nowik stracili pracę ;) nie ma odpowiadających id działów (do których zostali przypisani) nazw działów stąd wartości NULL. Bez wykoannych zmian w tabeli wynik operacji left join wyglądałby następująco:

----------------------------------------------------------------------------------------------------------------------- 

Right outer join / right join

Lub w skrócie right join jest niemalże identyczny z left join z tą różnicą że zachowywana zawsze jest zawartość tabeli prawej - B. Jeżeli do wiersza z tabeli B pasuje więcej niż 1 wiersz z tabeli A wiersze z tabeli B będą zdublowane, nadomiast nalogicznie jeśli żaden rekord z tabeli A nie pasuje do rekordu z tabeli B zostaną wstawione wartości NULL.

SELECT * FROM   pracownik RIGHT OUTER JOIN dzial
          ON pracownik.ID_dzialu = dzial.ID_dzialu

Jak widać zgodnie z opisem pozostały tylko 4 rekordy, w administracji nikt nie pracuje i nie ma sytuacji gdzie kilku pracowników pracowałoby w jednym dziale ( brak powtórzeń rekordów z nazwami działów).

Powyższy efekt można uzyskać komendą left join zamieniając kolejność tabel w komendzie:

SELECT * FROM   dzial LEFT OUTER JOIN pracownik
          ON pracownik.ID_dzialu = dzial.ID_dzialu

---------------------------------------------------------------------------------------

Full outer join

Efektem tej operacji jest wynik łączący efekt left outer join i right outer join. Tabela wynikowa będzie zawiwerała rekordy z obu tabel i wypełni wartościąNULL brakujące dopasowania na każdej ze stron.

Pozwoli to przykładowo zobaczyć który pracownik pracuje w jakim departamencie, dodatkowo pokaże departamenty bez pracowników i osoby nie przypisane żadnemu departamentowi.

SELECT * FROM   pracownik
       FULL OUTER JOIN dzial
          ON pracownik.ID_dzialu = dzial.ID_dzialu

Niektóre bazy danych w tym (MYSQL) nie obsługują tej funkcjonalności bezpośrednio, lecz mogą być emulowane za pomocą left join, right join i union.

SELECT *FROM   pracownik
       LEFT JOIN dzial
          ON pracownik.ID_dzialu = dzial.ID_dzialu
UNION
SELECT *FROM   pracownik
       RIGHT JOIN dzial
          ON pracownik.ID_dzialu = dzial.ID_dzialu

SQLite nie obłsuguje right join'ów więc outer join może byćemulowany w następujący sposób: 

SELECT pracownik.*, dzial.* FROM   pracownik
       LEFT JOIN dzial
          ON pracownik.ID_dzialu = dzial.ID_dzialu
UNION ALL
SELECT pracownik.*, dzial.* FROM   dzial
       LEFT JOIN pracownik
          ON pracownik.ID_dzialu = dzial.ID_dzialu
WHERE  pracownik.ID_dzialu IS NULL

-----------------------------------------------------------------------------------------------------------------------------

Self join

zapytanie to pozwala np znaleźć wszystkie dopasowania wewnątrz  jednej tabeli np wszystkich pracowników z tego samego kraju. Jeśli posiadasz 2 oddzielne tabele z pracownikami i krajami oraz powiązaniami to nie ma problemu z wykorzystaniem poprzednich join'ów. Self join ma zastosowanie gdy wszystko odbywa się wewnątrz jednej wielkiej tabeli agregującej wszystkie dane.

wpis stowyrzłem na podstawie: Wikipedia