JOIN 기반 메커니즘

조인 기반 메커니즘

1. 조인의 개요와 데이터 집합 레벨 변화

개요

• 둘 이상의 테이블을 서로 연결하여 데이터 추출

  

• 조인한 두 테이블은 서로 동격이다.

데이터 집합 레벨의 변화

• 1:M 조인 시 결과 집합은 M집합의 레벨을 따라간다.

  • 여기서 1은 고유(unipue)한 값을 가진 집합, M은 중복 건이 존재하는 집합이다.

  • 아래의 그림처럼 deptno가 유일한 dept는 1, deptno가 중복되는 emp는 M이다.

    

  • 조인 결과 emp를 기준으로 dept 테이블의 컬럼들이 중복으로 출력된다.

실습 문제 풀기

실습 환경에 대한 설명

• emp_salary_his, emp_dept_his : 과거 급여, 소속 부서 이력 테이블

• emp, dept : 직원의 현재 소속 부서, 부서 정보가 담긴 테이블, Master 테이블

실습 문제

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

  select emp.*, dept.dname
  from hr.emp as emp
    join hr.dept as dept on emp.deptno = dept.deptno
  

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

  select emp.*, dept.dname
  from hr.emp as emp
    join hr.dept as dept on emp.deptno = dept.deptno
  where emp.job = 'SALESMAN'
  

• 부서명 SALES와 RESEARCH의 소속 직원들의 부서명, 직원번호, 직원명, JOB 그리고 과거 급여 정보 추출

  select dept.dname, emp.empno, emp.ename, emp.job, sal_his.sal
  rom hr.emp as emp
    join hr.dept as dept on emp.deptno = dept.deptno
    join hr.emp_salary_hist as sal_his on sal_his.empno = emp.empno
  where dept.dname in ('SALES', 'RESEARCH')
  

  • 위의 문제에서 emp는 dept에게 M집합, emp_salary_hist는 emp에게 M집합이므로 최종 M집합은 emp_salary_hist가 된다.

  

  • 조인 결과, emp_salary_hist의 sal을 기준으로 중복된 값들이 생성된 것을 볼 수 있다.

• 부서명 SALES와 RESEARCH의 소속 직원들의 부서명, 직원번호, 직원명, JOB 그리고 과거 급여 정보중 1983년 이전 데이터는 무시하고 데이터 추출

  select dept.dname, emp.empno, emp.ename , emp.job, sal_his.sal, sal_his.fromdate, sal_his.todate
  from hr.emp as emp
    join hr.dept as dept on emp.deptno = dept.deptno
    join hr.emp_salary_hist as sal_his on sal_his.empno = emp.empno
  where dept.dname in ('SALES', 'RESEARCH')
    and sal_his.fromdate >= to_date('19830101', 'yyyymmdd')
  /* order by 1, 2, 3, sal_his.fromdate */
  

• 부서명 SALES와 RESEARCH 소속 직원별로 과거부터 현재까지 모든 급여를 취합한 평균 급여

  with member_sal as (
      select dept.dname, emp.empno, emp.ename , emp.job, sal_his.sal, sal_his.fromdate, sal_his.todate
      from hr.emp as emp
          join hr.dept as dept on emp.deptno = dept.deptno
          join hr.emp_salary_hist as sal_his on sal_his.empno = emp.empno
      where dept.dname in ('SALES', 'RESEARCH')
          and sal_his.fromdate >= to_date('19830101', 'yyyymmdd')
      /* order by 1, 2, 3, sal_his.fromdate */
  )
  select empno, max(ename) as ename, avg(sal) as avg_sal
  from member_sal
  group by empno
  

• 직원명 SMITH의 과거 소속 부서 정보를 구할 것

  select emp.empno, emp.ename, dept_his.fromdate, dept_his.todate, dept.dname
  from hr.emp as emp
      join hr.emp_dept_hist as dept_his on dept_his.empno = emp.empno
      join hr.dept as dept on dept.deptno = dept_his.deptno
  where emp.ename = 'SMITH'