SQL. Pobieranie danych z kilku tabel: natural join, join, using, using on

Tabele:
pracownicy
pracownik_id
imie
nazwisko
stanowisko_id
kierownik_id

stanowiska
stanowisko_id
nazwa_stanowiska
kierownik_id
lokalizacja

Zadanie 1
Przygotuj raport o wszystkich pracownikach pracujących w firmie i  ich stanowiskach.

Informacje o pracownikach i ich stanowiskach są umieszczone są w rożnych tabelach. Należy napisać zapytanie SQL pobierające dane z dwóch różnych tabel używając połączeń JOINS. To zapytanie będzie pobierało dane stanowisko_id z tabeli pracownicy a nazwa_stanowiska z tabeli Stanowiska używając stanowisko_id jak kolumnę wspólną.

Wynikiem zapytania będzie pojedynczy raport zawierający następujące kolumny: pracownik_id, stanowisko_id, nazwa_stanowiska

Istnieje kilka typów połączeń JOINS, których możemy używać:

1. połączenia naturalne z klauzulą NATURAL JOIN
2. Samopołączenia
3. Non equijon
4. outer joins:
   left outer joins
   right outer joins
   pelne outer joins
5. cross joins

FROM pracownicy NATURAL JOIN stanowiska;

Zauważmy iż tabela stanowiska jest połaczona z pracownicy poprzez stanowisko_id, które jest jedyną kolumną o tej samej nazwie w obu tabelach. Jeśli są inne wspólne pola połaczenie użyje ich wszystkich.
Możemy dodać ograniczenie używając klauzuli WHERE.

Zadanie 2
Raport nazw wydziału, w których zatrudnieni są pracownicy.

 

USING

Tabele:
pracownicy
pracownik_id
imie
nazwisko
stanowisko_id
kierownik_id

wydzialy
wydzial_id
wydzial_nazwa
kierownik_id
lokalizacja

Chcemy stalenić wydziały pracowników. Aby to wykonac należy porównać wartości w kolumnie stanowisko_id w tabeli stanowiska ze stanowisko_id w tabeli pracownicy. Jeśli wspólnymi kolumnami są dwie kolumny stanowisko_id oraz kierownik_id, naturalne połączenie użyje obuj tych kolumn do pokazania wyniku. Ale my chcemy połączyć te tabele za pomocą tylko kolumny stanowisko_id. Aby to wykonać musimy użyć klauzuli USING by podać tylko jedną kolumnę, która ma zostac użyta do połączenia.
Relacja pomiędzy tabelą pracownicy i stanowiska to wiele-do-jednego. Wartość w stanowisko_id kolulmnie w obu tabelach musi być taka sama. Częśto taki typ połączeń obejmuje klucz głóny i obcy.


SELECT pracownik_id, nazwisko, nazwa_wydzialu, wydzial_id
FROM  pracownicy JOIN wydzialy USING (wydzial_id)

W tym zapytaniu kolumna wydzial_id w tabelach pracownicy i wydziały są połączone, a zatem nazwa_wydzialu gdzi epracownik pracuje zostala wyświetlona.
Kolumny pracownik_id i nazwisko należą do tabeli pracownicy, podczas gdy nazwa_wydzialu należy do tabeli wydziału – i wydzial_id należy do obu tych tabel. Dlatego używanie przedrostka z nazwą tabel  rozwiązuje niejednoznaczność i zwiększa szybkość wykonywania zapytania – gdyż mówi serverowi Oracle gdzie znajdzie kolumnę. Jednak określanie nazw kolumn używając nazw tabel może być czasochołonee, szczególnie gdy nazwy tabel są długie np


zbyt_dluga_nazwa_tabeli.nazwa_kolumny

Zamiast tego można używać aliasów tabel. tak jak alias kolumny daje kolumnie nowa nazwę, tak alias tabeli daje tabeli inną nazwę np

t.nazwa_klumny

Aliasy tabel pomagają zmiejszyć kod SQL.

Pamiętaj że nie możesz określić kolumny, która jest użwana w klauzuli USING. Na przykład to zapytanie spowoduje wyświetlenie błędu:

SELECT l.miasto.d.nazwa_wydzialu
FROM lokalizacje l JOIN wydziały d
USING (lokalizacja_id)
WHERE d.lokalizacja_id =1400;

Nie można użyć aliasu  kolumny lokalizacja_id  w klauzui WHERE ponieważ kolumna jest użyta w klauzuli USING. Zapytanie powinno wyglądać tak:

SELECT l.miasto.d.nazwa_wydzialu, d.kierownik_id
FROM lokalizacje l JOIN wydziały d
USING (lokalizacja_id)
WHERE lokalizacja_id =1400;


Zauważ, że kierownik_id jest obecne w obu tabelach zarówno pracownicy jak i wydzialy. Jeśli kierownik_id nie będzie miał prefixu tabeli to server wyświetli błąd.

USING ON

Można zastąpić klauzulę USING na klauzulę ON  by uszczegółowić warunek połączenia. Zaletą używania klauzuli ON jest możliwość uszczegółowienia bezwzględnego warunku lub sprecyzowania kolumn do połączenia.


Na przykład, aby złączyć taelę pracownicy i wydziały poprzez kolumnę wydzial_id można napisać takie zapytanie SQL:

SELECT p.pracownicy, p.nazwisko,p.wydzial_id. w. wydzial_id, w.lokazliacja_id
FROM pracownicy p JOIN wydzialy d
ON (p.wydzial_id=w.wydzial_id);

W zapytaniu jeśli wydzial_id w tabeli pracownicy jestrówny z wydział_id w tabeli wydziały, wiersz jest zwracany


ON (p.wydzial_id=w.wydzial_id);


Użycie aliasu tabeli jest konieczne aby określić pasujące kolumny.  


Zadanie 3
Wyświetl raport o wszystkich pracownikach, ich wydziałach i miastach w których się mieszczą.


W tym celu musimy połączyć trzy tabele: pracownicy, wydzialy, lokalizacje


SELECT pracownik_id, city, nazwa_wydzialu
FROM pracownicy p
JOIN wydzialy w
ON w.wydzial_id = p.wydzial_id
JOIN lokalizacje l
ON w.lokalizacja_id = l.lokalizacja_id;


Pierwsze połączenie, ktore jest wykonywane to : pracownicy JOIN wydzialy:


FROM pracownicy p
JOIN wydzialy w
ON w.wydzial_id = p.wydzial_id


Pierwszy warunek połaczenia może odnosić się do kolumn w tabelach pracownicy i wydzialu al enie może odnosić się do kolumn z tabeli lokalizacje.

Drugie połączenie może odnosić się do kolumn z wszsytkich trzech tabel:

JOIN lokalizacje l
ON w.lokalizacja_id = l.lokalizacja_id;

Uwaga. Przykładowy kod można również napisać za pomocą klauzuli USING.

Zadanie 4
Przygotuj raport o numerach lokazlizacji wszystkich pracowników których kirownik ma numer 12.

SELECT p.pracownik_id, p.nazwisko, p.wydzial_id, w.wydzial_id, w.lokazacja_id
FROM pracownicy p JOIN wydzialy w
ON (p.wydzial_id = w.wydzial_id)
AND p.kierownik_id=12;

Do połączenia dodane zostały dodatkowe warunki. Zapytanie pokazuje wykonane połączenie na tabeli pracownicy i wydziały i dodatkowo wyświetla

FROM pracownicy p JOIN wydzialy tylko pracowników których kierowniki ma numer 12

Jako alternatywę możesz też użyć WHERE aby zastosować dodatkowe warunki. Wynik zapytania będzie taki sam

SELECT p.pracownik_id, p.nazwisko, p.wydzial_id, w.wydzial_id, w.lokazacja_id
FROM pracownicy p JOIN wydzialy w
ON (p.wydzial_id = w.wydzial_id)
WHERE p.kierownik_id=12;