23.10.12. Oracle SQL JOIN ②

■  ANSI(American National Standards Institue)-SQL(Strutured Query Language) join 문법

A. cartesian product 

SELECT employee_id, last_name, department_name
FROM hr.employees CROSS JOIN hr.departments;

B. natural join 
    a. equi join
    b. 조인조건술어를 자동으로 만들어준다.
    c. 양쪽 테이블 '(쓰임이 달라도)동일한 이름의 모든 컬럼'을 기준으로 조인조건술어를 만든다.
        - 동일한 이름의 컬럼이 1개 있을 경우에 쓰는게 좋다.
    d. 양쪽 테이블의 동일한 이름의 컬럼이 '데이터 타입이 틀릴 경우', 오류 발생한다.
    

SELECT e.employee_id, d.department_name
FROM hr.employees e, hr.departments d
WHERE e.department_id = d.department_id;

SELECT e.employee_id, d.department_name
FROM hr.employees e NATURAL JOIN hr.departments d; 

SELECT d.department_name, l.city
FROM hr.departments d NATURAL JOIN hr.locations l;

 

C. JOIN USING
    a. equi join 
    b. 조인 조건의 '기준 컬럼'을 지정한다.
    c. using절 안에 같은 테이블-여러 컬럼을 기준으로 가능

SELECT e.employee_id, department_id, d.department_name
FROM hr.employees e JOIN hr.departments d
USING ( department_id ); 

SELECT e.employee_id, e.department_id, d.department_name -- USING절 뒤에 오는 컬럼을 쓸 때에는 테이블로 지정된 컬럼을 사용X
FROM hr.employees e JOIN hr.departments d
USING ( d.department_id ); -- 오류발생 : USING 절 뒤에 테이블을 지정X

SELECT e.employee_id, department_id, d.department_name, location_id, l.city
FROM hr.employees e JOIN hr.departments d
USING ( department_id )
JOIN hr.locations l 
USING( location_id )
WHERE department_id IN (20, 30);

D. JOIN ON 의 3가지
    a. EQUI JOIN, SELF JOIN, NON EQUI JOIN
    b. ON 절을 이용해서 조인조건 술어를 직접 만들어서 사용한다.
    
a) EQUI JOIN

SELECT e.employee_id, e.department_id, d.department_name
FROM hr.employees e JOIN hr.departments d
ON e.department_id = d.department_id ;

SELECT e.employee_id, e.department_id, d.department_name
FROM hr.employees e JOIN hr.departments d
ON e.department_id = d.department_id 
AND e.manager_id = d.manager_id;

SELECT e.employee_id, e.department_id, d.department_name, l.city
FROM hr.employees e JOIN hr.departments d
ON e.department_id = d.department_id 
JOIN hr.locations l
ON d.location_id = l.location_id;

SELECT e.employee_id, e.department_id, d.department_name, l.city
FROM hr.employees e JOIN hr.departments d
ON e.department_id = d.department_id 
JOIN hr.locations l
ON d.location_id = l.location_id
WHERE e.last_name LIKE '%a%' -- where절 먼저 수행 후 join 수행
AND e.salary >= 10000;

 

b) SELF JOIN

SELECT w.employee_id, w.last_name, m.employee_id, m.last_name
FROM hr.employees w JOIN hr.employees m
ON w.manager_id = m.employee_id;

c) NON EQUI join

SELECT e.employee_id, e.salary, j.grade_level
FROM hr.employees e JOIN hr.job_grades j
ON e.salary BETWEEN j.lowest_sal AND j.highest_sal;

SELECT e.employee_id, e.salary, j.grade_level, d.department_id
FROM hr.employees e JOIN hr.job_grades j
ON e.salary BETWEEN j.lowest_sal AND j.highest_sal
JOIN hr.departments d
ON e.department_id = d.department_id;

E. OUTER JOIN

[LEFT OUTER JOIN ON]

SELECT e.last_name, d.department_name
FROM hr.employees e, hr.departments d
WHERE e.department_id = d.department_id(+);

SELECT e.last_name, d.department_name
FROM hr.employees e LEFT OUTER JOIN hr.departments d
ON e.department_id = d.department_id;

-------------------------------------------------------------

SELECT e.last_name, d.department_name, l.city
FROM hr.employees e, hr.departments d, hr.locations l
WHERE e.department_id = d.department_id(+)
AND d.location_id = l.location_id(+);

SELECT e.last_name, d.department_name, l.city
FROM hr.employees e LEFT OUTER JOIN hr.departments d
ON e.department_id = d.department_id
LEFT OUTER JOIN hr.locations l
ON d.location_id = l.location_id;

[RIGHT OUTER JOIN ON]

SELECT e.last_name, d.department_name
FROM hr.employees e, hr.departments d
WHERE e.department_id(+) = d.department_id;

SELECT e.last_name, d.department_name
FROM hr.employees e RIGHT OUTER JOIN hr.departments d
ON e.department_id = d.department_id;

[FULL OUTER JOIN ON]

SELECT e.last_name, d.department_name
FROM hr.employees e, hr.departments d
WHERE e.department_id(+) = d.department_id(+); -- 오류발생("a predicate may reference only one outer-joined table") 서브쿼리로 해결가능

SELECT e.last_name, d.department_name
FROM hr.employees e FULL OUTER JOIN hr.departments d
ON e.department_id = d.department_id;