데이터분석 SQL - Join(1)

Join

• 관계형 DBMS에서 가장 중요한 기능
• 두 개 이상의 테이블을 서로 연결하여 데이터를 추출하는 방법 (JOIN과 Subquery 가 있다.)
• 관계형 DB에서는 JOIN을 통해 서로 다른 테이블 간의 정보를 원하는 대로 가져올 수 있다.

Join 시 데이터 집합 레벨의 변화

• 1:M 조인 시, 결과 집합은 M집합의 레벨을 유지한다.

SELECT A.ENAME, A.EMPNO, A.JOB, B.DEPTNO, B.DNAME
FROM HR.EMP A
	JOIN HR.DEPT B ON A.DEPTNO = B.DEPTNO
ORDER BY 1;

• EMP Table은 M집합 / DEPT Table은 1집합 이므로, EMP Table의 M집합으로 레벨이 유지된다.

---

예시 문제

1. 직원 정보와 직원이 속한 부서명을 가져오기.

SELECT a.*, b.dname
FROM hr.emp a
	JOIN hr.dept b ON a.deptno = b.deptno;

---

2. job이 'SALESMAN'인 직원 정보와 직원이 속한 부서명을 가져오기.

SELECT a.*, b.dname
FROM hr.emp a
	JOIN hr.dept b ON a.deptno = b.deptno
WHERE a.job = 'SALESMAN';

---

3. 부서명 sales와 research의 소속 직원들의 부서명, 직원 번호, 직원명, JOB 그리고 과거 급여정보 추출하기.

SELECT a.dname, b.empno, b.ename, b.job, c.fromdate, c.todate, c.sal
FROM hr.dept a
	JOIN hr.emp b ON a.deptno = b.deptno 
	JOIN hr.emp_salary_hist c ON b.empno = c.empno
WHERE a.dname IN ('SALES', 'RESEARCH');

---

4. 부서명 sales와 research의 소속 직원들의 부서명, 직원번호, 직원명, job정보를 추출하기. 단, 과거 급여 정보 중 1983년 이전 데이터는 무시하고 데이터 추출

SELECT a.dname, b.empno, b.ename, b.job, c.fromdate, c.todate, c.sal
FROM hr.dept a
	JOIN hr.emp b ON a.deptno = b.deptno
	JOIN hr.emp_salary_hist c ON b.empno = c.empno
WHERE a.dname IN ('SALES', 'RESEARCH')
AND c.fromdate >= to_date('1983-01-01', 'yyyy-mm-dd')
ORDER BY 1, 2, 3, c.fromdate;

---

5. 부서명 sales와 research 소속 직원별로 과거부터 현재까지 모든 급여를 취합한 평균 급여 데이터 추출

WITH
TEMP_01 AS
(
SELECT a.dname, b.empno, b.ename, b.job, c.fromdate, c.todate, c.sal
FROM hr.dept a
	JOIN hr.emp b ON a.deptno = b.deptno
	JOIN hr.emp_salary_hist c ON b.empno = c.empno
WHERE a.dname IN ('SALES', 'RESEARCH')
ORDER BY a.dname, b.empno, c.fromdate
)
SELECT empno, max(ename) AS ename, avg(sal) AS avg_sal
FROM temp_01
GROUP BY empno;

---

6. 직원 SMITH의 과거 소속 부서 정보 데이터를 추출

SELECT a.ename, a.empno, b.deptno, c.dname, b.fromdate, b.todate
FROM hr.emp a
	JOIN hr.emp_dept_hist b ON a.empno = b.empno
	JOIN hr.dept c ON b.deptno = c.deptno
WHERE a.ename = 'SMITH';