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ń.
