실습 3 - Join

DBMS는 데이터를 하나의 테이블에 저장하는 것이 아니라 "샘플 데이터 베이스 개요"의 ERD처럼 특성에 맞추어 분할해서 저장한다.

따라서 제대로 된 정보를 보기 위해서는 관련된 테이블을 결합(JOIN)해서 보여 줘야 한다.

이러한 결합을 조인(JOIN)이라고 하고 기준에 따라 Left, Right로 나뉘고, 

데이터 일치 여부에 따라 Inner, Outer 등으로 구분된다.

여기서는 Left Inner만 다룰 예정으로 대부분의 SELECT문이 Left Inner 조인 이기 때문이다.

일반적으로 조인이라고 하면 Left Inner을 의미할 정도로 많이 쓰이고, 

정의하자면 두 테이블 간의 교집합이라고 할 수 있다.

즉, 두 테이블간에 공통으로 있는 값을 찾는 것이라고 할 수 있다.

샘플 데이터베이스를 예로 하면 한 직원이 속한 부서는 3개의 테이블로 구성되어 있다.

직원 정보(EMPLOYEES), 부서 정보(DEPARTMENTS) 그리고 한 직원이 속한 부서를 저장하는 테이블(dept_emp)이 필요하다.

이 테이블 들이 모여서 한 직원의 부서에 대한 제대로 된 정보를 알 수 있는 것이다. 

자세한 내용은 데이터 베이스 정규화 등 관련 자료를 찾아보길 바란다.

조인(Join)문을 쉽게 이해하고 풀기 위해서는 "샘플 데이터 베이스 개요"의 ERD를 참고하는 것이 좋다.

1. 현재 근무 중인 직원 정보를 출력하시오.

SELECT E.*

  FROM EMPLOYEES E

  INNER JOIN DEPT_EMP DE ON DE.EMP_NO=E.EMP_NO

WHERE DE.TO_DATE = '9999-01-01';

2개 이상의 테이블이 사용되고,

각 테이블에는 같은 필드명(EMP_NO)이 사용될 수 있어서 테이블 별칭을 사용한다.

직원 테이블(EMPLOYEES)은 E, 부서 할당 테이블(DEPT_EMP)은 DE로 사용한다.

그리고 두 테이블 간의 관계를 나타내는 직원번호(EMP_NO)를 이용하여

조인을 하고 필요한 데이터를 구성하게 된다.

ON절에는 두 테이블의 관계를 나타내는 조건을 작성한다.

즉, 직원과 할당된 부서 테이블은 직원번호(EMP_NO)필드로 관계가 구성되어 있기 때문에 

두 테이블의 직원번호가 같은(DE.EMP_NO=E.EMP_NO) 데이터를 선택하라고 한다.

연습: 현재 근무 중인 직원의 모든 정보(수행업무 포함) 를 출력하시오.

SELECT E.*, T.TITLE

   FROM EMPLOYEES E

   INNER JOIN TITLES T ON T.EMP_NO=E.EMP_NO

WHERE T.TO_DATE = '9999-01-01';

직무 테이블(TITLES)과 조인하면 된다.

2. 다음 그림과 같이 현재 근무 중인 부서를 출력하시오.

SELECT E.EMP_NO, E.FIRST_NAME, D.DEPT_NAME

   FROM DEPT_EMP DE

   INNER JOIN EMPLOYEES E ON E.EMP_NO=DE.EMP_NO

   INNER JOIN DEPARTMENTS D ON D.DEPT_NO=DE.DEPT_NO

WHERE DE.TO_DATE = '9999-01-01';

부서 테이블(DEPARTMENTS)을 추가하여 총 3개의 테이블이 사용된다.

테이블이 더 추가되더라도 위 코드처럼 LEFT JOIN을 추가해 주면 된다.

부서는 부서번호(DEPT_NO)를 이용하여 관계를 설정한다.

3. 가장오래 근무한 직원 10명의 현재 부서를 출력하시오.

SELECT E.EMP_NO, E.FIRST_NAME, E.LAST_NAME, D.DEPT_NAME, E.HIRE_DATE

  FROM DEPT_EMP DE

  INNER JOIN EMPLOYEES E ON E.EMP_NO=DE.EMP_NO

  INNER JOIN DEPARTMENTS D ON D.DEPT_NO=DE.DEPT_NO

WHERE DE.TO_DATE = '9999-01-01' 

ORDER BY HIRE_DATE

  LIMIT 10 ;

2번 문제와 동일한 데이터를 대상으로 입사일(HIRE_DATE)로 정렬하여 10명을 추출(LIMIT)한다.

4.부서별로 직원 수를 구하되 부서 이름이 나오게 출력하시오.

SELECT DEPT_NAME, COUNT(*)

   FROM DEPT_EMP DE

   INNER JOIN DEPARTMENTS D ON D.DEPT_NO=DE.DEPT_NO

 WHERE DE.TO_DATE='9999-01-01' 

  GROUP BY DEPT_NAME

  ORDER BY DEPT_NAME;

이 경우는 조인보다 서브 쿼리가 더 효율적일 수 있다(Subquery 참조)

왜냐하면 위 SQL은 조인을 하고 문자열인 DEPT_NAME으로 그룹핑을 하기 때문에 데이터 양에 따라 속도 변화가 크다.

코드로 그룹핑하고 처리하는 것이 더 빠르게 처리 된다.

연습: 부서별, 성별 직원 수를 구하시오

SELECT D.DEPT_NAME, E.GENDER, COUNT(*)

   FROM DEPT_EMP DE

   INNER JOIN DEPARTMENTS D ON D.DEPT_NO=DE.DEPT_NO

   INNER JOIN EMPLOYEES E ON E.EMP_NO=DE.EMP_NO

 WHERE DE.TO_DATE='9999-01-01' 

 GROUP BY D.DEPT_NAME, E.GENDER

 ORDER BY D.DEPT_NAME, E.GENDER;

성별(GENDER)은 직원(EMPLOYEES) 테이블에 있기 때문에 조인할 테이블을 추가하고

그룹 필드를 부서명과 성별로 2개가 사용된다.

5. 급여 평균이 가장 높은 부서 5개를 출력하시오.

SELECT D.DEPT_NAME, AVG(SALARY) SA

  FROM DEPT_EMP DE

  INNER JOIN DEPARTMENTS D ON D.DEPT_NO=DE.DEPT_NO

  INNER JOIN SALARIES S ON S.EMP_NO=DE.EMP_NO

WHERE DE.TO_DATE='9999-01-01' AND S.TO_DATE='9999-01-01' 

GROUP BY D.DEPT_NAME

ORDER BY SA DESC

LIMIT 5;

연습: 급여 평균이 가장 높은 부서를 제외하고, 급여 평균이 높은 부서를 5개를 출력하시오.

SELECT D.DEPT_NAME, AVG(SALARY) SA

  FROM DEPT_EMP DE

  INNER JOIN DEPARTMENTS D ON D.DEPT_NO=DE.DEPT_NO

  INNER JOIN SALARIES S ON S.EMP_NO=DE.EMP_NO

WHERE DE.TO_DATE='9999-01-01' AND S.TO_DATE='9999-01-01' 

GROUP BY D.DEPT_NAME

ORDER BY SA DESC

  LIMIT 1, 5;

6. 급여를 많이 받는 부서장 리스트를 출력하시오.

SELECT E.*, S.SALARY

   FROM DEPT_MANAGER DM

   INNER JOIN SALARIES S ON S.EMP_NO = DM.EMP_NO

   INNER JOIN EMPLOYEES E ON E.EMP_NO = DM.EMP_NO

 WHERE S.TO_DATE = '9999-01-01'

 ORDER BY SALARY DESC;

ERD에 따르면 부서장 테이블(DEPT_MANAGER)은

직원 테이블을 (EMPLOYEES) 통해서 급여(SALARIES) 테이블과 관계가 있는 것으로 나온다.

하지만, 실질적으로는 직원번호(EMP_NO)를 통해 관계를 맺기 때문에 직접 조인을 맺어 주면 된다.

여기에 사용된 직원 테이블은 급여를 가지고 온후 직원 이름을 보여주기 위해 사용된 것으로 의미가 다르다.

주의: ERD에 관계 설정이 간접적이더라도 조인 조건에서는 바로 설정해서 사용하는 경우가 있다.

7. 개발부(Development)에서 급여를 가장 많이 받는 직원 5명을 출력하시오.

SELECT E.*, SALARY

   FROM DEPT_EMP DE

   INNER JOIN EMPLOYEES E ON E.EMP_NO = DE.EMP_NO

   INNER JOIN SALARIES S ON S.EMP_NO = DE.EMP_NO

 WHERE DE.TO_DATE = '9999-01-01' AND DEPT_NO='D005'

 ORDER BY SALARY DESC

    LIMIT 5;

개발부(Development)의 부서코드를 찾아야 한다.

부서(DEPARTMENTS) 테이블을 조회해 보면 개발부의 부서코드는 D005이다.

따라서 부서 할당 테이블의 부서코드(DEPT_NO)가 D005인 직원 리스트를 조회해서 급여순으로 정렬하면 된다.

생각해보기: 각 부서에서 급여를 가장 많이 받는 직원 리스트를 구하시오.