조인쿼리 정리

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