오라클 조인 쿼리 (ORACLE JOIN QUERY)

1. 자연 조인 (NATURAL JOIN)
Equi 조인과 동일
두 테이블의 동일한 이름을 가지는 칼럼은 모두 조인된다.

SQL> select employee_id, last_name, department_name
  2  from employees natural join departments
  3  where department_name ='Sales';

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_NAME
----------- ------------------------- ------------------------------
        174 Abel                      Sales
        176 Taylor                    Sales

2 rows selected.

2. 내부 조인 (INNER JOIN)
일반 조인의 , 를 생략하고 INNER JOIN 를 사용하며 ON절을 사용해야 한다.

Wrote file afiedt.buf

  1  SELECT E.EMPLOYEE_ID, E.LAST_NAME, D.DEPARTMENT_NAME
  2  FROM EMPLOYEES E inner join DEPARTMENTS D
  3  ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
  4* where e.job_id = 'IT_PROG'
SQL> /

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_NAME
----------- ------------------------- ------------------------------
        103 Hunold                    IT
        104 Ernst                     IT
        107 Lorentz                   IT

3 rows selected.

 

3. 외부 조인 (OUTER JOIN) - left
일반 조인에서 외부조인은 두 테이블에서 공통된 값이 없다면 테이블로부터 데이터를 리턴하지 않기때문에 그 외의 컬럼들을 보기 위한 조인이다.

LEFT OUTER JOIN 은
좌측 테이블과 우측 테이블에서 조건이 같은 컬럼은 모두 출력되며 , 우측 테이블의 컬럼은 모두 출력된다.

SQL> ed
Wrote file afiedt.buf

  1  SELECT E.LAST_NAME, E.DEPARTMENT_ID ,D.DEPARTMENT_ID , D.DEPARTMENT_NAME
  2  FROM EMPLOYEES E left outer join DEPARTMENTS D
  3  ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
  4* ORDER BY E.DEPARTMENT_ID

LAST_NAME      DEPARTMENT_ID DEPARTMENT_ID DEPARTMENT_NAME
-------------------- ------------- ------------- ------------------------------
Whalen     10       10 Administration
Hartstein   20       20 Marketing
Fay          20       20 Marketing
Mourgos   50       50 Shipping
Vargas      50       50 Shipping
Matos       50       50 Shipping
Davies      50       50 Shipping
Rajs         50       50 Shipping
Lorentz     60       60 IT
Ernst        60       60 IT
Hunold      60       60 IT
Abel         80       80 Sales
Zlotkey     80       80 Sales
Taylor       80       80 Sales
De Haan    90       90 Executive
King         90       90 Executive
Kochhar    90       90 Executive
Gietz        110      110 Accounting
Higgins     110      110 Accounting
Grant

20 rows selected.

4. 외부 조인 (OUTER JOIN) - right
RIGHT OUTER JOIN은
좌측 테이블과 우측 테이블에서 조건이 같은 컬럼은 모두 출력되며, 좌측 테이블의 컬럼은 모두 출력된다.

SQL> ed
Wrote file afiedt.buf

  1  SELECT E.LAST_NAME, E.DEPARTMENT_ID ,D.DEPARTMENT_ID , D.DEPARTMENT_NAME
  2  FROM EMPLOYEES E right outer join DEPARTMENTS D
  3  ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
  4* ORDER BY E.DEPARTMENT_ID
SQL> /

LAST_NAME      DEPARTMENT_ID DEPARTMENT_ID DEPARTMENT_NAME
-------------------- ------------- ------------- ------------------------------
Whalen     10       10 Administration
Hartstein   20       20 Marketing
Fay          20       20 Marketing
Davies      50       50 Shipping
Vargas     50       50 Shipping
Rajs         50       50 Shipping
Mourgos   50       50 Shipping
Matos      50       50 Shipping
Hunold     60       60 IT
Ernst       60       60 IT
Lorentz    60       60 IT
Taylor     80       80 Sales
Zlotkey    80       80 Sales
Abel        80       80 Sales
De Haan  90       90 Executive
King        90       90 Executive
Kochhar   90       90 Executive
Higgins    110      110 Accounting
Gietz       110      110 Accounting
              190       Contracting

20 rows selected.

5. 셀프 조인 (SELFT JOIN)

  1  select e.last_name "Employee", m.last_name "Reports To"
  2  from employees e left outer join employees m
  3* on e.employee_id = m.manager_id
SQL> /

Employee                  Reports To
------------------------- -------------------------
King                      Kochhar
King                      De Haan
De Haan                   Hunold
Hunold                    Ernst
Hunold                    Lorentz
King                      Mourgos
Mourgos                   Rajs
Mourgos                   Davies
Mourgos                   Matos
Mourgos                   Vargas
King                      Zlotkey
Zlotkey                   Abel
Zlotkey                   Taylor
Zlotkey                   Grant
Kochhar                   Whalen
King                      Hartstein
Hartstein                 Fay
Kochhar                   Higgins
Higgins                   Gietz
Vargas
Matos
Fay
Rajs
Abel
Ernst
Whalen
Lorentz
Davies
Gietz
Taylor
Grant

31 rows selected.

 혹은

 1  SELECT e.EMPLOYEE_ID ,  e.LAST_NAMe, e.JOB_ID, e.MANAGER_ID
  2   FROM EMPLOYEES e, employees m
  3* where e.employee_id = m.manager_id
SQL> /

EMPLOYEE_ID LAST_NAME                 JOB_ID     MANAGER_ID
----------- ------------------------- ---------- ----------
        100 King                      AD_PRES
        100 King                      AD_PRES
        100 King                      AD_PRES
        100 King                      AD_PRES
        100 King                      AD_PRES
        101 Kochhar                   AD_VP             100
        101 Kochhar                   AD_VP             100
        102 De Haan                   AD_VP             100
        103 Hunold                    IT_PROG           102
        103 Hunold                    IT_PROG           102
        124 Mourgos                   ST_MAN            100
        124 Mourgos                   ST_MAN            100
        124 Mourgos                   ST_MAN            100
        124 Mourgos                   ST_MAN            100
        149 Zlotkey                   SA_MAN            100
        149 Zlotkey                   SA_MAN            100
        149 Zlotkey                   SA_MAN            100
        201 Hartstein                 MK_MAN            100
        205 Higgins                   AC_MGR            101

19 rows selected.

6. 크로스 조인(cross join)
카티션 프로덕트 값을 얻을때 사용한다.
모든 컬럼의 곱한 값으로 조인되어 출력된다.

SQL> select last_name, department_name
  2  from employees
  3  cross join departments;

LAST_NAME                 DEPARTMENT_NAME
------------------------- ------------------------------
Abel                      Administration
Davies                    Administration
De Haan                   Administration
Ernst                     Administration
Fay                       Administration
Gietz                     Administration
.
.
.
.
Zlotkey                   Contracting

160 rows selected.

Elapsed: 00:00:00.06