sobota, 6 listopada 2010

Kurs Oracle SQL. Złączenia typu JOIN

ZŁĄCZENIA TYPU JOIN

JOIN ON



Inną metodą łączenia wielu tabel jest korzystanie z klauzuli JOIN. Przypuśćmy że chcemy pobrać dane z dwóch tabel jednocześnie. Nasze zadanie to wyświetlić nazwy departamentów i w których się znajdują. Musimy pobrać dane z tabel locations i departments. Możemy to zrobić przy wykorzystaniu dotychczas omawianej metody tj. zwykłego łączenia zewnętrznego:



Ten sam wynik możemy osiągnąć stosują join on:





NATURAL JOIN



Ponieważ tabele departments oraz locations posiadają pole o tej samej nazwie które służy nam do łączenia tych tabel, możemy skorzystać z natural join:


JOIN USING



Identyczne zastosowanie jak NATURAL JOIN ma JOIN USING. W tym przypadku musimy podać nazwę kolumny która ma służyć jako złączenie. Korzystać z niej możemy jeśli w dwóch tabelach które chcemy połączyć występuje więcej niż jedna kolumna o identycznych nazwach i typie danych.




RIGHT OUTER JOIN



Right outer join jest złączeniem zewnętrznym.
Konstrukcja typu:


select * from departments d, locations l where d.location_id(+)=l.location.id;


jest równoznaczna z konstrukcją:


select * from departments d, right outer join location l on (d.location_id=l.location_id);


Jest niejako odwrotnie niż ze znaczkiem (+) - jeśli do tej pory postawilibyśmy ten znaczek po lewej, musimy zastosować RIGHT OUTER JOIN, natomiast jeśli (+) postawilibyśmy po prawej, stosujemy LEFT OUTER JOIN.

Konstrukcja z (+):



Konstrukcja z RIGHT OUTER JOIN:



LEFT OUTER JOIN



Left outer join jest złączeniem zewnętrznym.
Konstrukcja typu:


select * from departments d, locations l where d.location_id=l.location.id(+);


jest równoznaczna z konstrukcją:


select * from departments d left outer join location l on (d.location_id=l.location_id);


Jeśli do tej pory postawilibyśmy ten znaczek po prawej, musimy zastosować LEFT OUTER JOIN, natomiast jeśli (+) postawilibyśmy po lewej, stosujemy RIGHT OUTER JOIN.


FULL OUTER JOIN



FULL OUTER JOIN jest połączeniem RIGHT OUTER JOIN i LEFT OUTER JOIN, lub konstrukcji z dwoma (+) po stronach obu tabel (choć taka konstrukcja nie może oczywiście istnieć – jest to tylko analogia). Wyświetla niedopasowane elementy z obu tabel.


Ćwiczenia


  1. Wyświetl wszystkie nazwy regionów przyporządkowując je do nazw krajów. Wynik posortuj wg. Regionów rosnąco.
  2. Wyświetl lokalizacje oraz departamenty do nich przypisane w taki sposób aby wyświetlone zostały również te lokalizacje do których nie zostały przypisane żadne departamenty. “Puste” lokalizacje powinny zostać wyświetlone na końcu.
  3. Wyświetl nazwiska oraz płacę tych pracowników którzy są przełożonymi kogokolwiek.
  4. Wyświetl tych pracowników którzy zarabiają powyżej średniej w firmie.
  5. Wyświetl tych pracowników którzy pracują w departamencie “Finance” a jednocześnie zarabiają więcej niż średnia w całej firmie. Użyj w tym celu zapytania skorelowanego.
  6. Wyświetl różnicę pomiędzy najwyższymi a najniższymi zarobkami w departamencie “Finance”
  7. Wyświetl tych pracowników którzy zarabiają więcej od kogokolwiek kto pracuje w departamencie “Finance”
  8. Wyświetl wszystkich managerów
  9. Wyświetl nazwiska pracowników oraz nazwiska ich przełożonych
  10. Wyświetl nazwiska pracowników oraz nazwy departamentów w których pracują.
  11. Wyświetl nazwiska pracowników oraz nazwy regionów w których znajdują się departamenty w których pracują.
  12. Korzystając z operatorów zbiorowych wyświetl tych pracowników którzy pracują w departamencie numer 90 a jednocześnie zarabiają mniej niż średnia zarobków w całej firmie. Nie może być powtórzeń.






Ten temat omawiam na poniższych szkoleniach:
• Podstawy Oracle SQL
• Podstawy SQL i PL/SQL
Możesz w nich uczestniczyć, a jako czytelnik tego bloga otrzymasz 10% zniżki - poinformuj o tym fakcie konsultanta.
Autor: o

17 komentarzy:

  1. Unknown11 grudnia 2010 18:00

    select * from departments d, right outer join location l on (d.location_id=l.location_id);


    "select * from departments d left outer join location l on (d.location_id=l.location_id);"


    powinno być 'locations' zamiast 'location'

    OdpowiedzUsuń
  2. Ola Mądzik 8 maja 2011 09:51

    Ten komentarz został usunięty przez autora.

    OdpowiedzUsuń
  3. Paweł9 grudnia 2011 08:27

    Istnieje krótsze zapytanie do 10 ćwiczenia niż:

    select e.last_name pracownik, d.department_name, r.region_name
    from employees e, departments d, locations l, countries cc, regions r
    where e.department_id=d.department_id and d.location_id=l.location_id
    and l.country_id=cc.country_id and r.region_id = cc.region_id;

    ?

    OdpowiedzUsuń
  4. Paweł9 grudnia 2011 08:29

    Jak to zrobić na JOIN - ach? Tu także musi być sposób dodawania kolejnych i kolejnych tabel, prawda?

    OdpowiedzUsuń
  5. marcin9 grudnia 2011 10:25

    no wlasnie tez probowalem to rozwalic za pomocą joinow, ale poleglem... ;/

    OdpowiedzUsuń
  6. andrew10 grudnia 2011 06:52

    select last_name, department_name from employees join departments using(department_id). To odnośnie 10. Po co łączycie to z locations i countries jeśli z nich nic nie wyciąganie?

    OdpowiedzUsuń
  7. gumis31 stycznia 2012 16:23

    zadanie 11 na joinach:

    select last_name, first_name,region_name from employees join departments using (department_id) join locations using (location_id) join countries using(country_id) join regions using (region_id);

    OdpowiedzUsuń
  8. Artur26 września 2012 17:04

    9 mnie pokonało, ktoś pomoże ?

    OdpowiedzUsuń
  9. Zuzanna21 lutego 2013 14:15

    AD. 9:

    SELECT P.FIRST_NAME, P.LAST_NAME, L.FIRST_NAME, L.LAST_NAME FROM EMPLOYEES P JOIN
    (SELECT DISTINCT E.EMPLOYEE_ID, M.MANAGER_ID, E.FIRST_NAME, E.LAST_NAME FROM EMPLOYEES E, EMPLOYEES M WHERE E.EMPLOYEE_ID=M.MANAGER_ID) L
    ON L.MANAGER_ID=P.MANAGER_ID;

    OdpowiedzUsuń
  10. Relatywista25 marca 2013 04:27

    Ten komentarz został usunięty przez autora.

    OdpowiedzUsuń
  11. Relatywista25 marca 2013 04:30

    Czy macie jakieś uwagi?

    zad. 1

    select region_name, country_name from regions natural join countries order by 1;

    zad. 2

    select * from departments d right outer join locations l on (l.location_id=d.location_id) order by department_name nulls last;

    zad. 3

    select employee_id, last_name, salary from employees e where exists (select manager_id from employees m where
    m.manager_id=e.employee_id);

    zad. 4

    select last_name, salary from employees where salary > (select avg(salary) from employees);

    zad. 5

    select last_name, salary from employees where salary > (select avg(salary) from employees)
    intersect
    select e.last_name, e.salary from employees e inner join departments d on (e.department_id=d.department_id) where
    department_name like ('finance');

    zad. 6

    select (max(salary) - min(salary)) as roznica from employees e inner join departments d on
    (e.department_id=d.department_id) where department_name like ('finance');

    zad. 7

    select last_name, salary from employees where salary > any (select e.salary from employees e inner join departments d on
    (e.department_id=d.department_id) where department_name like ('finance'));

    zad. 8

    select employee_id, last_name from employees e where exists (select last_name from employees m where
    e.employee_id=m.manager_id);

    zad. 9

    select e.last_name pracownik, nvl(m.last_name, 'pan prezes ;)') przelozony from employees m right join employees e on (e.manager_id=m.employee_id);

    zad. 10

    select e.last_name, d.department_name from employees e left join departments d on (e.department_id=d.department_id);

    zad. 11

    select e.last_name, r.region_name from employees e left join departments d on (e.department_id=d.department_id)
    left join locations l on (d.location_id=l.location_id)
    left join countries c on (l.country_id=c.country_id)
    left join regions r on (c.region_id=r.region_id);

    zad. 12

    select last_name from employees where department_id = 90
    intersect
    select last_name from employees where salary < (select avg(salary) from employees);

    OdpowiedzUsuń
  12. Unknown4 listopada 2014 06:01

    Mam problem, chciałbym w łączeniu tabel użyć aliasów. Pewnie robię coś nie tak, ponieważ między słowem JOIN a drugim aliasem wyrzuca błąd. Proszę o jakiś przykład

    OdpowiedzUsuń
  13. szymon F10 listopada 2014 06:39

    Witam, mam problem z zadaniem 7:
    SELECT E.SALARY, D.DEPARTMENT_NAME, E.LAST_NAME
    FROM EMPLOYEES E
    INNER JOIN DEPARTMENTS D
    ON (D.DEPARTMENT_ID=E.DEPARTMENT_ID)
    WHERE E.SALARY > ALL (SELECT E.SALARY FROM EMPLOYEES E WHERE D.DEPARTMENT_NAME = 'Finance');

    Zwraca mi wartość SALARY dla wszystkich innych działów niż Finance, ale wartość SALARY są również mniejsze niż dla działu Finance.

    OdpowiedzUsuń
    Odpowiedzi
    1. szymon F10 listopada 2014 11:52

      Moje poprawne rozwiazanie do zad. 7 ( tak mi sie wydaje):

      SELECT E.SALARY, D.DEPARTMENT_NAME
      FROM EMPLOYEES E
      JOIN DEPARTMENTS D
      ON (D.DEPARTMENT_ID=E.DEPARTMENT_ID)
      WHERE E.SALARY > ANY (
      SELECT (E.SALARY) FROM EMPLOYEES E
      JOIN DEPARTMENTS D
      ON (D.DEPARTMENT_ID=E.DEPARTMENT_ID)
      WHERE D.DEPARTMENT_NAME = 'Finance'
      );

      Usuń
    2. Unknown3 stycznia 2017 11:31

      ALL zamień na ANY :)

      Usuń
  14. Unknown3 stycznia 2017 10:50

    Ten komentarz został usunięty przez autora.

    OdpowiedzUsuń
  15. Unknown3 stycznia 2017 11:52

    ad.8 prosta wersja

    select distinct manager_id from employees
    where MANAGER_ID is not null;

    OdpowiedzUsuń

Subskrybuj: Komentarze do posta (Atom)