05 JOIN

■ JOIN
- 두개 이상의 테이블에서 내가 원하는 데이터를 가지고 오는 방법

SELECT employee_id, last_name, department_id
FROM hr.employees;      -- 107행
+
SELECT department_id, department_name
FROM hr.departments;    -- 27행

 

1. cartesian product
- 조인 조건이 생략되었을 경우
- 조인 조건이 잘 못 만든 경우
- 첫번째 테이블의 행수와 두번째 테이블 행수가 곱해진다.

SELECT employee_id, last_name, department_name
FROM hr.employees, hr.departments;
    --> 107행 * 27행

 

2. eqi join, inner join, simple join, 등가조인
- 조인키 값이 일치하는 데이터만 추출하는 조인

- 조인 조건은 M쪽 집압의 행의수보다 작거나 같게 나와야 한다.

SELECT e.employee_id, e.last_name, d.department_name
FROM hr.employees e, hr.departments d
WHERE e.department_id = d.department_id;    -- 조인조건술어
	     M쪽	      1쪽	    = M쪽

 

- join을 수행할 테이블이 n개면 조인조건술어는 n-1개 기술해야 한다.

SELECT e.employee_id, e.last_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;          		-- 조인조건술어, 순서무관

 

[문제] 사원들의 employee_id, country_name을 출력

SELECT employee_id, 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;

 

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

SELECT e.last_name, e.job_id, 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;

 

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

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

↓ 성능개선(9i 버전~)

SELECT e.last_name, e.job_id, d.department_name
FROM hr.employees e, hr.departments d
WHERE e.department_id = 80
AND d.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
- 키 값이 일치되는 데이터 또는 키값이 일치되지 않는 데이터도 출력하는 조언
- (+)를 이용해서 outer join 표현한다.
- 양쪽에 (+)를 수행하면 오류발생

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;

join 알고리즘
① 　 　 ② 　   ③  테이블
└ ─ ─ ─ ┘ 　 　│　
　 　□ 　 　 　  │
　 　└ ─ ─ ─ ─ ┘ 
　 　 　 　○

WHERE e.department_id = d.department_id
AND d.location_id = l.location_id;
-- 106명 : (e O + d O) + 지역 O

WHERE e.department_id(+) = d.department_id
AND d.location_id = l.location_id;
-- 122명 : (이름 null 포함 + 부서명 O) + 지역 O

WHERE e.department_id = d.department_id(+)
AND d.location_id = l.location_id;
-- 106명 : (이름 O + 부서 null) + 지역 O

WHERE e.department_id = d.department_id
AND d.location_id(+) = l.location_id; 
-- 106명 : (이름 O + 부서 O) + 지역 null
  -- ① + ②
AND d.location_id = l.location_id(+);
-- 106명 : (이름 O + 부서 O) + 지역 null

WHERE e.department_id(+) = d.department_id
AND d.location_id(+) = l.location_id;
-- 138명 : (이름 null + 부서 O) + 지역 null

WHERE e.department_id(+) = d.department_id
AND d.location_id = l.location_id(+);

WHERE e.department_id = d.department_id(+)
AND d.location_id(+) = l.location_id;

WHERE e.department_id = d.department_id(+)
AND d.location_id = l.location_id(+);

[문제] commission_pct에 null 이 아닌 사원들의 last_name, commission_pct, department_name 출력

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(*)
FROM hr.employees e
WHERE e.commission_pct is not null; -- 35명

4. self join
- 자신의 테이블을 참조할 때 사용하는 조인

SELECT employee_id, last_name, manager_id
FROM hr.employees;  -- 일반직원테이블(w)
+
SELECT employee_id, last_name
FROM hr.employees;  -- 관리자(직속상관)테이블(m)
↓
SELECT w.employee_id, w.last_name, m.employee_id, m.last_name
FROM hr.employees w, hr.employees m
WHERE w.manager_id = m.employee_id;

SELECT w.employee_id, w.last_name, m.employee_id, m.last_name
FROM hr.employees w, hr.employees m
WHERE w.manager_id = m.employee_id(+);



5. non eqi join, 비등가조인
- eqi join(=) 할 수 없는 다른 비교연산자를 사용하는 조인의 유형
- 값을 범위로 조인하려는 경우 많이 사용한다.

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 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
ORDER BY j.grade_level;

 

■ ANSI(American Nationl Standard Institue)-SQL(Structured Query Language) JOIN 문법

1. cartesian product
- 조인 조건이 생략되었을 경우
- 조인 조건이 잘 못 만든 경우
- 첫번째 테이블의 행수와 두번째 테이블 행수가 곱해진다.

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


2. NATURAL JOIN
- equi join
- 조인조건술어를 자동으로 만들어 준다.
- 양쪽 테이블의 동일한 이름의 모든 컬럼을 기준으로 조인조건술어를 만든다.
- 양쪽 테이블의 동일한 이름의 컬럼 데이터 타입이 다를 경우 오류 발생

SELECT e.employee_id, d.department_name
FROM hr.employees e NATURAL JOIN hr.departments d;
||
SELECT e.employee_id, d.department_name
FROM hr.employees e, hr.departments d
WHERE e.department_id = d.department_id
AND e.manager_id = d.manager_id;    -- 주의

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

SELECT d.department_name, l.city
FROM hr.departments d ,hr.locations l
WHERE d.location_id = l.location_id;

3. JOIN USING
- equi join
- 조인조건의 기준 컬럼을 지정한다.
- using절에 사용된 컬럼은 어느 테이블이라고 지정하면 오류 발생한다.

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

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);


- 여러 컬럼을 기준컬럼을 사용 할 경우
SELECT e.employee_id, d.department_name
FROM hr.employees e JOIN hr.departments d
USING(department_id, manager_id);


4. JOIN ON
- equi join, self join, non equi join
- on 절을 이용해서 조인조건술어를 직접 만들어서 사용한다.

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, 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%'
AND e.salary >= 10000;


- 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;


- 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_name
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;

 

JOIN	ANSI-SQL JOIN
5. OUTER JOIN
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 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;
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;
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 FULL OUTER JOIN hr.departments d ON e.department_id = d.department_id;

 

[문제] 2006년도에 입사한 사원들의 부서이름별 급여의 총액, 평균을 출력해주세요.
1) 오라클전용
SELECT d.department_name, sum(e.salary) 급여총액, round(avg(e.salary)) 급여평균
FROM hr.employees e, hr.departments d
WHERE e.hire_date >= to_date('2006-01-01','yyyy-mm-dd')
AND e.hire_date <= to_date('2006-12/31','yyyy-mm-dd')
AND e.department_id = d.department_id
GROUP BY d.department_name;

2) ANSI 표준
SELECT d.department_name, sum(e.salary) 급여총액, round(avg(e.salary)) 급여평균
FROM hr.employees e JOIN hr.departments d
ON e.department_id = d.department_id
WHERE e.hire_date BETWEEN to_date('2006-01-01','yyyy-mm-dd')
                AND to_date('2006-12/31','yyyy-mm-dd')
GROUP BY d.department_name;


> 참고 <
WHERE e.hire_date >= to_date('2006-01-01','yyyy-mm-dd')
AND e.hire_date <= to_date('2006-12-31','yyyy-mm-dd');

★
WHERE e.hire_date >= to_date('2006-01-01 00:00:00','syyyy-mm-dd hh24:mi:ss')
AND e.hire_date <= to_date('2006-12-31 00:00:00','syyyy-mm-dd hh24:mi:ss')
>>> 주의!! 시간 기본값 0시로 되어있다.
↓
AND e.hire_date <= to_date('2006-12-31 23:59:59','yyyy-mm-dd');
AND e.hire_date < to_date('2007-01-01','yyyy-mm-dd'); ★
>>> 시간까지 지정하거나 날짜를 1일 추가하여 검색

rrrr 시간표시는 없지만 시간 기본값은 00:00:00 
yyyy 사용 추천

SELECT to_char(to_date('-2000','syyyy'),'syyyy ad bc'),
        to_char(to_date('2000','syyyy'),'syyyy ad bc')
FROM dual;
syyyy : 기원전 표기 필요 시


[문제] 2007년도에 입사한 사원들의 도시이름별 급여의 총액, 평균을 출력해주세요.
단 부서 배치를 받지 않은 사원들의 정보도 출력해주세요.
1) 오라클 전용
SELECT l.city, sum(e.salary) 급여총액, round(avg(e.salary)) 급여평균
FROM hr.employees e, hr.departments d, hr.locations l
WHERE e.hire_date >= to_date('2007-01-01','yyyy-mm-dd')
AND e.hire_date < to_date('2008-01-01','yyyy-mm-dd')
AND e.department_id = d.department_id(+)
AND d.location_id = l.location_id(+)
GROUP BY l.city;

2) ANSI 표준
SELECT l.city, sum(e.salary) 급여총액, round(avg(e.salary)) 급여평균
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
WHERE e.hire_date >= to_date('2007-01-01','yyyy-mm-dd')
AND e.hire_date < to_date('2008-01-01','yyyy-mm-dd')
GROUP BY l.city;


[문제] last_name에 a문자가 2개 이상 포함되어 있는 사원들의 
    last_name,salary,grade_level, department_name을 출력해주세요.
1) 오라클 전용
SELECT e.last_name, e.salary, j.grade_level, d.department_name
FROM hr.employees e, departments d, job_grades j
WHERE instr(last_name,'a',1,2) > 1 --  > 1 : 위치값이 2이상인가
ANDe.department_id = d.department_id
AND e.salary BETWEEN j.lowest_sal AND j.highest_sal

2) ANSI 표준
SELECT e.last_name, e.salary, j.grade_level, d.department_name
FROM hr.employees e JOIN departments d
ON e.department_id = d.department_id
JOIN job_grades j
ON e.salary BETWEEN j.lowest_sal AND j.highest_sal
AND instr(e.last_name,'a',1,2) >= 2;


[문제] 담당관리자(직속상관)보다 먼저 입사한 사원의 이름과 입사일 및 
    해당 관리자의 이름과 입사일 출력해주세요.
1) 오라클 전용
SELECT e.last_name, e.hire_date, m.last_name, m.hire_date
FROM hr.employees e, hr.employees m
WHERE e.manager_id = m.employee_id
AND m.hire_date > e.hire_date;

2) ANSI 표준 
SELECT e.last_name, e.hire_date, m.last_name, m.hire_date
FROM hr.employees e JOIN hr.employees m
ON e.manager_id = m.employee_id
WHERE m.hire_date > e.hire_date;