23.10.12. Oracle SQL JOIN ①

■ JOIN 

A. 두 개이상의 테이블에서 원하는 테이블를 가지고 오는 방법 

SELECT employee_id, last_name, department_id 
FROM hr.employees;

SELECT department_id, department_name
FROM hr.departments;

1) Cartesian Product

    a. 조인 조건이 생략되었을 경우
    b. 조인 조건이 잘못 만든 겨우 
    c. 첫 번째 테이블의 행의 수와 두 번째 테이블 행의 수가 곱해진다.

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

 

2) EQUI JOIN, INNER JOIN, SIMPLE JOIN, 등가조인

    a. 조인 key 값이 일치하는 데이터만 추출하는 조인

SELECT employee_id, last_name, department_name
FROM hr.employees e, hr.departments d
WHERE department_id = department_id; -- 오류발생 "column ambiguously defined" : 컬럼의 모호성

SELECT e.employee_id, e.last_name, d.department_name
FROM hr.employees e, hr.departments d -- 테이블 별칭에는 'as' 사용X
WHERE e.department_id = d.department_id; -- 조인조건술어
      /*  M쪽               1쪽(중복성X)       = M쪽
 조인 건수: M쪽 집행의 행의 수보단 작거나 같게 나와야 한다.
        */

SELECT e.employee_id, e.last_name, l.city
FROM hr.employees e, hr.departments d, hr.locations l -- 테이블 순서 X
WHERE e.department_id = d.department_id -- 조인조건술어
AND d.location_id = l.location_id; -- 조인조건술어

 

B. 조인 수행할 테이블 N개면 조인조건술어 N-1개 기술해야 한다.
    - WHERE (조인조건) AND (조인조건)

 

[문제1] 사원들(employee_id)의 국가명(country_name)을 출력하세요.

SELECT e.employee_id, c.country_name
FROM hr.employees e, hr.departments d, hr.locations l, hr.countries c
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND l.country_id = c.country_id;

 

[문제2] 80번 부서에 근무하는 사원들의 last_name, job_id, department_name, city 출력하세요.

[방법1]

SELECT e.last_name, 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 -- 조인 조건 술어
AND e.department_id = 80; -- 비조인조건술어

[방법2]

SELECT e.last_name, 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
AND d.department_id = 80;

[문제3] locations 테이블에 있는 city 컬럼에 Toronto 도시에서 근무하는 모든 사원의 last_name, job_id, department_id, department_name 출력하세요.

SELECT e.last_name, e.job_id, d.department_id, d.department_name
FROM hr.employees e, hr.departments d , hr.locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND l.city = 'Toronto';

 

/* 성능저하 쿼리문 */
SELECT e.last_name, e.job_id, d.department_name
FROM hr.employees e, hr.departments d 
WHERE d.department_id = 80 -- 비조인 조건
AND e.department_id = d.department_id ; --조인조건

/* 		셀프 튜닝 ↓ 
    " a = b = c → a = c " 
*/
SELECT e.last_name, e.job_id, d.department_name
FROM hr.employees e, hr.departments d 
WHERE d.department_id = 80 --비조인 조건
AND e.department_id = 80 ; -- 비조인 조건

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

3) OUTER JOIN (누락된 데이터도 출력)
    a. 키 값이 일치되는 데이터 또는 키 값이 일치되지 않은 데이터도 출력하는 조인
    b. (+)를 이용해서 OUTER JOIN 표현한다.
    c. 한쪽에만 사용할 것. 양쪽에 (+)를 수행하면 오류 발생.
    d. (+) 부호가 없는 테이블에서의 누락된 정보를 출력하는 것

SELECT e.last_name, e.job_id, d.department_name 
FROM hr.employees e, hr.departments d 
WHERE e.department_id = d.department_id(+); --e.department_id 에서의 누락된 정보

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

SELECT e.last_name, e.job_id, d.department_name 
FROM hr.employees e, hr.departments d 
WHERE 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(+);

 

table 3개 이상일 때, outer join 순서 

'ⓐ 조인 + d.location_id = 조인결과집합'
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, l.city
FROM 'ⓐ 조인 결과 집합', hr.locations l
WHERE  d.location_id = l.location_id(+);

[문제] commission_pct 에 null 이 아닌 사원들의 last_name, commission_pct, department_name 을 출력해주세요.

tip) 건수에 대한 검증도 확인해야 한다.

SELECT e.last_name, e.commission_pct, d.department_name
FROM hr.employees e, hr.departments d
WHERE e.department_id = d.department_id(+)
AND e.commission_pct IS NOT NULL;

SELECT COUNT(*) COMM
FROM hr.employees
WHERE commission_pct IS NOT NULL;

4) SELF JOIN 
    a. 자신의 테이블을 참조할 때 사용하는 조인
    b. 테이블을 2개 이상을 사용해야 하므로, 테이블의 별칭을 이용해야 한다. 

SELECT employee_id, last_name, manager_id
FROM hr.employees; --일반직원테이블(w)

SELECT employee_id, last_name
FROM hr.employees; --관리자(직속상관) 테이블(m)

SELECT
    w.employee_id    "일반직원 id",
    w.last_name      "일반직원 name",
    m.employee_id    "관리자 id",
    m.last_name      "관리자 name"
FROM hr.employees w, hr.employees m
WHERE w.manager_id = m.employee_id(+);

5) NON EQUI JOIN, 비등가 조인

    a. EQUI JOIN(=) 할수 없는 다른 비교연산자를 사용하는 조인의 유형
    b. 값을 범위로 조인하려는 경우, 많이 사용한다. 

SELECT e.employee_id, e.salary, j.grade_level
FROM hr.employees e, hr.job_grades j 
WHERE e.salary >= j.lowest_sal
AND e.salary <= j.highest_sal;

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

SELECT e.employee_id, e.salary, j.grade_level, d.department_name
FROM hr.employees e, hr.job_grades j, hr.departments d 
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal
AND e.department_id = d.department_id; -- 누락된 데이터 존재

SELECT COUNT(*) "d.department_id"
FROM hr.employees e, hr.job_grades j, hr.departments d 
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal
AND e.department_id = d.department_id;

SELECT e.employee_id, e.salary, j.grade_level, d.department_name
FROM hr.employees e, hr.job_grades j, hr.departments d 
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal
AND e.department_id = d.department_id(+); 

SELECT COUNT(*) "d.department_id outer-join"
FROM hr.employees e, hr.job_grades j, hr.departments d 
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal
AND e.department_id = d.department_id(+);

[문제] 급여의 등급레이블의 빈도수를 출력하세요.(등급별 count)

SELECT j.grade_level, count(*)
FROM hr.employees e, hr.job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal
GROUP BY j.grade_level;