Kurs Oracle SQL online. Złączenia, czyli JOINy

Zapraszam do kolejnej lekcji kursu Oracle SQL 🙂 Dzisiaj bardziej obszerny temat 🙂

W tej lekcji kursu zajmiemy się złączeniami w bazie Oracle – czyli jak w wyniku działania zapytania uzyskać dane “porozrzucane” na wiele tabel.

Złączenie to pobranie danych, które znajdują się w więcej niż jednej tabeli:

Złączenie dwóch tabel – tabeli klientów i kredytów

Złączenia dzielimy na:

• złączenia wewnętrzne
• złączenia zewnętrzne

Oba typy złączeń omówimy niżej, a więc.. 🙂 Poniżej diagram ERD (czyli diagram pokazujący relacje między tabelami) dla użytkownika HR. W trakcie kolejnych lekcji poznaliśmy już trochę te tabele. W przypadku, gdy występuje między nimi połączenie – na diagramie znajduje się “kreska”. Jeśli jest to połączenie 1 do wiele to po stronie wiele znajduje się rozszerzenie na 3 kreski (np. przy tabeli LOCATIONS). Kolumny na których odbywa się złączenie zaznaczone są przy “kresce” kolorem zielonym – np. kolumna COUNTRY_ID w tabeli LOCATIONS wskazuje na kolumnę COUNTRY_ID w tabeli COUNTRIES.

Zatem kolumna COUNTRY_ID w tabeli LOCATIONS jest kluczem obcym do kolumny COUNTRY_ID tabeli COUNTRIES (COUNTRY_ID w COUNTRIES to klucz główny:

• Klucz główny – nie przyjmuje wartości NULLowych i zapewnia unikatowość, czyli będzie to zazwyczaj kolumna ID identyfikująca jednoznacznie rekord.
• Klucz obcy – wskazuje na klucz główny z innej tabeli. Domyślnie zapewnia, to że do LOCATIONS nie wstawimy rekordy z COUNTRY_ID nie występującym w tabeli COUNTRIES oraz nie usuniemy rekordu z tabeli COUNTRIES jeśli jego COUNTRY_ID wykorzystane jest w LOCATIONS.

Schemat użytkownika HR

Jeśli ten opis Was jeszcze nie przekonuje 🙂 rzućcie okiem na pierwszy obrazek w tej lekcji. Tabela u góry po lewej to tabela Klienci, ID klienta w tej tabeli to klucz główny (zazwyczaj to pierwsza kolumna w tabeli, ale tak być nie musi).

Tabela u góry po prawej to tabela Kredyty, ID klienta w tej tabeli to klucz obcy wskazujący na klucz główny w tabeli Klienci.

I teraz 🙂 Jeśli Klient wziął kredyt to jego ID ląduje w tabeli Kredyty – przy czym ponieważ w tabeli Kredyty jest klucz obcy to:

• do tabeli Kredyty nie wstawimy ID Klienta, który nie występuje w Klientach. Zapewne bank nie chciałby mieć zaciągniętego kredytu, którego nie ma kto spłacać 🙂
• Jeśli Klient brał kredyt i jego ID znajduje się w tabeli Kredyty – to nie będziemy mogli go usunąć. Bank nie ucieszyłby się jeśli okazałoby się, że Kredyt jest, a usunęliśmy Klienta i nie ma kto go spłacać. Klienta będzie można usunąć dopiero po usunięciu jego kredytów. Jest to forma zabezpieczenia.

Złączenia wewnętrzne: polega na wybraniu odpowiadających sobie rekordów z dwóch tabel. W Oracle SQL do złączeń można używać starszej i nowszej składni

Przykład 1a. Wybierz pracowników oraz departamenty, do których zostali przypisani – starą metody

SELECT * FROM employees e, departments d 
WHERE d.department_id = e.department_id;

Przykład 1b. Wybierz pracowników oraz departamenty, do których zostali przypisani – nową metodą

SELECT * FROM employees e JOIN departments d
ON d.department_id = e.department_id;d

Polecam pisanie zapytań nową metodą, jest bardziej czytelna, mniej podatna na błędy, ma większe możliwości 🙂

W Przykładzie 1 wypisaliśmy pracowników i ich departamenty, ale tylko pracowników którzy mają departamenty oraz departamenty, które mają pracowników. Jeśli pracownik nie ma przypisanego departamentu to nie pojawił się w wyniku oraz jeśli departament nie ma przypisanego, żadnego pracownika – też nie pojawi się w wyniku.

Czasami będziemy chcieli, żeby pojawiły się rekordy, które nie mają odpowiedników “po drugiej stronie” – np. pracowników bez departamentów. Do tego służą złączenia zewnętrzne 🙂

Złączenie zewnętrzne polega na łączeniu odpowiadających sobie rekordów z obu tabel,
a następnie dodaniu do wyniku tych, które nie posiadają odpowiednika w drugiej z tabel. Złączenia zewnętrzne dzielą się na jednostronne i obustronne.

Przykład 2a. Starą metodą: wybierz pracowników oraz departmenty, do których zostali przypisani. Wybierz również departamenty bez pracowników.

SELECT * 
FROM employees e, departments d
WHERE d.department_id = e.department_id(+);

Jak to działa? 🙂 W przypadku starej metody (+) stawiamy przy kolumnie, gdzie brakuje rekordów. Chcemy dodać departamenty nawet, gdy nie ma dla nich pracowników – czyli brakuje rekordów po stronie pracowników. Stawiamy (+) po stronie e.departament_id <–  ta kolumna pochodzi z tabeli employees.
Przykład 2b. Nową metodą: wybierz pracowników oraz departmenty, do których zostali przypisani. Wybierz również departamenty bez pracowników.

SELECT * 
FROM employees e 
RIGHT JOIN departments d
ON d.department_id = e.department_id;

A tu jak działa? Odwrotnie niż w starej 🙂 Otóż (+) wskazujemy, gdzie brakuje rekordów a LEFT lub RIGHT wskazujemy, po której stronie JOIN stoi tabela z której chcemy wziąć wszystkie rekordy. Chcemy wziąć wszystkie z tabeli departments stąd RIGHT. Patrzać na ten przykład – oczywiście, gdybyście chcieli wziąć wszystkich pracowników to nie napiszemy UP tylko LEFT :), czyli pracujemy tak jakby było to w jednej linii – LEFT lub RIGHT.

UWAGA! (+) odnosi się do kolumn (kolumna z tabeli,  w której brakuje rekordów) , LEFT | RIGHT do tabel – z której wziąć wszystkie rekordy.

A co jeśli chcielibyśmy wybrać pracowników i ich departamenty oraz pracowników bez departamentów i departamenty bez pracowników?

Przykład 3. Stara metoda

SELECT * FROM employees e, departments d
WHERE d.department_id (+) = e.department_id (+);

ORA-01468: a predicate may reference only one outer-joined table

Starą metodą nie można zrobić złączenia obustronnie zewnętrznego, czyli nie można postawić (+) po obu stronach.

Przy pomocy nowej metody można zrobić złączenie obustronnie zewnętrzne – musiałby być i LEFT, i RIGHT, czyli FULL

Przykład 3. Nowa metoda

SELECT * FROM employees e FULL JOIN departments d
ON d.department_id = e.department_id;

W jaki sposób wybrać rekordy z kilku tabel?

Przykład 4a. Starą metodą: wybierz pracowników i ich departamenty oraz lokalizacje departamentów

SELECT * FROM employees e, departments d, locations l
WHERE d.department_id = e.department_id
AND l.location_id = d.location_id;

Przykład 4b: Nową metodą: wybierz pracowników i ich departamenty oraz lokalizacje departamentów

SELECT * 
FROM employees e 
JOIN departments d
ON d.department_id = e.department_id
JOIN locations l
ON l.location_id = d.location_id;

O złączeniach można jeszcze troszkę dopowiadać, ale myślę że póki co wystarczy. Lekcja i tak wyszła dość długa 🙂

Co w następnej lekcji kursu Oracle SQL?

Podzapytania – jak pisać zapytania zależne od wyników działania innych zapytań.