조인 JOIN

- JOIN: 2개 이상의 테이블을 한번에 출력되는것, pk와 fk를 사용해야됨

- 오라클은 4개의 join 이름을 구성 equi join, non-equi join, self join, outer join

 

1) equi join -> pk=fk 조건에서 조인

INNER JOIN

Q) 직원의 이름, 급여와 부서명을 출력하시오

--두개의 테이블을 같이 보는 방법
SELECT first_name 이름, salary 급여, department_name 부서
FROM employees, departments;
--근데 이러면 employees의 한 칼럼마다 departments의 모든 칼럼에 붙여져서 행수가 엄청 많아짐

SELECT first_name 이름, salary 급여, department_name 부서
FROM employees, departments
WHERE employees.department_id = departments.department_id --이렇게 조건으로 참조되는 열 지정
ORDER BY salary DESC;
--칼럼이 여러개 겹칠수 있기 때문에 아래같이 해야됨
SELECT employees.first_name 이름, employees.salary 급여, departments.department_name 부서
FROM employees, departments
WHERE employees.department_id = departments.department_id
ORDER BY salary DESC;
--별명으로 대체하기
SELECT e.first_name 이름, e.salary 급여, d.department_name 부서
FROM employees e, departments d
WHERE e.department_id = d.department_id
ORDER BY salary DESC;

Q) 직원의 이름, 매니저아이디, 지역아이디를 출력하시오 (이름순으로 정렬)

SELECT e.first_name 이름, e.manager_id 매니저아이디, d.location_id 지역아이디
FROM employees e, departments d
WHERE e.department_id = d.department_id
ORDER BY e.first_name;

Q) 직원의 이름, 매니저아이디, 지역아이디를 출력하시오, 매니저아이디가 120이상만 (이름순으로 정렬)

SELECT e.first_name 이름, e.manager_id 매니저아이디, d.location_id 지역아이디
FROM employees e, departments d
WHERE e.department_id = d.department_id AND e.manager_id>=120
ORDER BY e.first_name;

 

- 테이블 3개 연결

Q) 직원의 이름, 부서명, 해당 city를 출력하시오(city 오름차순)

SELECT e.first_name 이름,d.department_id 부서명, l.city 시티
FROM locations l, departments d, employees e
WHERE e.department_id=d.department_id AND d.location_id=l.location_id
ORDER BY 시티;

Q) 도시별 직원수 출력

SELECT l.city 시티, count(e.department_id) 직원수
FROM locations l, departments d,employees e 
WHERE d.location_id=l.location_id AND e.department_id=d.department_id
GROUP BY l.city
ORDER BY 시티;

 

- 튜닝을 신경쓰려면 join시 작은 테이블(driving table)이 앞으로 오는 것이 좋음, 칼럼이 더 적어야 비교 횟수가 더 적어짐

 

Q) 직책(job_title)이 representative인 사람의 이름, 직책, 부서명을 출력

SELECT e.first_name 이름, j.job_title 직책, d.department_name 부서명
FROM jobs j, departments d, employees e
WHERE e.department_id=d.department_id AND e.job_id=j.job_id
    AND lower(j.job_title) LIKE '%representative%'
ORDER BY 이름;

Q) 도시별로 그리고 같은 도시일 경우 다시 부서명 별로 묶어 2005년 이전에 입사한 사원수를 출력하시오.
 출력은 도시이름, 부서명, 해당 사원수를 출력하며
 만일 출력시 도시가 같으면 사원이 많은 순으로 출력하시오

SELECT l.city 시티,d.department_name 부서명, count(*) 사원수
FROM locations l, departments d, employees e
WHERE e.department_id=d.department_id AND d.location_id=l.location_id AND e.hire_date<'05/01/01'
GROUP BY l.city, d.department_name
ORDER BY 시티, 사원수 DESC;

Q) 직책이(job_title) Manager로 끝나는 사원 중에서 급여(salary)가 10,000이상 20,000이하인 사원들의 이름, 성, 연봉(salary) 및 직책(job_title)을 출력하시오 단, 급여가 많은 순으로 출력

SELECT e.first_name 이름,e.last_name 성, j.job_title 직책, e.salary 급여
FROM jobs j, employees e
WHERE e.job_id=j.job_id AND j.job_title LIKE '%Manager' AND e.salary BETWEEN 10000 AND 20000
ORDER BY 급여 DESC;

 

2) OUTER JOIN

Q) 사원의 이름 급여 부서명 출력

SELECT e.first_name 이름,e.salary 급여,d.department_name 부서명
FROM departments d, employees e
WHERE e.department_id=d.department_id;
-- 사원수는 107명인데 106명이 나옴
-- equi join 방식은 pk=fk 방식이라 fk가 null인 사람은 안나옴
-- department_id 가 null인 사람도 나오게 하기(employees의 모든 칼럼이 나오게 하기)
SELECT e.first_name 이름,e.salary 급여,d.department_name 부서명
FROM departments d, employees e
WHERE e.department_id=d.department_id(+);
--오라클에서는 만약에 결과가 나왔을때 null 나오는 쪽에 (+) 넣으면됨
--밑에는 부서는 있는데 사원은 없는 경우도 출력(departments의 모든 칼럼이 나오게 하기)
SELECT e.first_name 이름,e.salary 급여,d.department_name 부서명
FROM departments d, employees e
WHERE e.department_id(+)=d.department_id;
--WHERE e.department_id(+)=d.department_id(+); 이런 문법은 안만들어놈

- 오라클이 아닌 표준 join(ansi 1999)의 outer join (출발은 오라클의 equi join)
Q) 사원의 이름 급여 부서명 출력,양쪽다 null 나오게 하기(FULL OUTER JOIN)

SELECT e.first_name 이름,e.salary 급여,d.department_name 부서명
FROM departments d FULL OUTER JOIN employees e
ON e.department_id=d.department_id
ORDER BY 이름;

-employees의 모든 칼럼이 나오게 하기(LEFT OUTER JOIN, 왼쪽이 employees)

SELECT e.first_name 이름,e.salary 급여,d.department_name 부서명
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id=d.department_id
ORDER BY 이름;

-departments의 모든 칼럼이 나오게 하기(RIGHT OUTER JOIN, 오른쪽이 departments)

SELECT e.first_name 이름,e.salary 급여,d.department_name 부서명
FROM employees e RIGHT OUTER JOIN departments d
ON e.department_id=d.department_id
ORDER BY 이름;

 

 

3) self join: 한개의 테이블로 자료 비교

Q) 사원아이디, 사원명, 매니저이름 출력하기, 메니저 없어도 출력

SELECT e.employee_id 사원아이디, e.first_name 사원명, nvl(m.first_name,'매니저없음') 매니저이름
FROM employees e, employees m
WHERE e.manager_id=m.employee_id(+);

- union 사용방법(합집합)

SELECT e.employee_id 사원아이디, e.first_name 사원명, m.first_name 매니저이름
FROM employees e, employees m
WHERE e.manager_id=m.employee_id
UNION
SELECT e.employee_id 사원아이디, e.first_name 사원명, '매니저없음'
FROM employees e
WHERE e.manager_id IS NULL;
--union all: 중복도 허락

- minus 차집합

Q) 직원의 사번, 사원명, job_id 출력, job_id에 Clerk 들어간 사원 제외

SELECT e.employee_id 사원아이디, e.first_name 사원명, e.job_id 직책
FROM employees e
MINUS
SELECT e.employee_id 사원아이디, e.first_name 사원명, e.job_id 직책
FROM employees e
WHERE lower(e.job_id) LIKE '%clerk%';

 

4) non-equi join

--등급매기기
CREATE TABLE SALGRADE (
    grade NUMBER(5) NOT NULL,
    lsal NUMBER(8),
    hsal NUMBER(8),
    CONSTRAINT salgrade_grade_pk PRIMARY KEY(grade)
);
INSERT INTO salgrade VALUES(1,500,2999);
INSERT INTO salgrade VALUES(2,3000,3799);
INSERT INTO salgrade VALUES(3,3800,5199);
INSERT INTO salgrade VALUES(4,5200,9999);
INSERT INTO salgrade VALUES(5,10000,50000);
COMMIT;
SELECT * FROM salgrade;
SELECT * FROM tab;
--급여가 5199인 사람의 등급을 조회하시오
SELECT grade FROM salgrade WHERE hsal=5199;
--급여가 3500인 사람의 등급을 조회하시오
SELECT grade FROM salgrade WHERE lsal>=3000 AND hsal<=3799;
--이렇게 정확하게 컬럼을 맞출 수 없는 경우, pk=fk를 명확하게 표현하기 어려운 경우
--non-equi join 사용
--직원의 급여가 몇등급인지 조회(급여 내림차순, 이름 오름차순)
SELECT e.first_name 이름, e.salary 급여, s.grade 등급
FROM employees e, salgrade s
WHERE e.salary BETWEEN s.lsal AND s.hsal
ORDER BY 급여 DESC, 이름;
--이렇게 pk=fk 가 아닌 BETWEEN등으로 비교해서 사용