Subquery는 SQL 안에 SQL문을 사용하는 것으로
SELECT, FROM, WHERE 등 다양한 곳에서 사용된다.
1.다음과 같이 부서별 직원 수를 Subquery로 구하시오
SELECT D.DEPT_NAME
, (SELECT COUNT(*) FROM DEPT_EMP DE WHERE DE.TO_DATE = '9999-01-01' AND DE.DEPT_NO=D.DEPT_NO)
FROM DEPARTMENTS D;
SELECT D.DEPT_NAME, COUNT(*)
FROM DEPARTMENTS D
INNER JOIN DEPT_EMP DE ON DE.DEPT_NO=D.DEPT_NO
WHERE DE.TO_DATE = '9999-01-01'
GROUP BY D.DEPT_NAME;
SELECT절에 Subquery를 사용하는 예이다.
Subquery를 사용한 SQL문은 부서를 가져오면서 각 부서(DEPT_EMP)에 배정된 직원의 인원수를 계산(COUNT)한다.
앞서 연습한 Join을 사용한 SQL문은 부서와 할당된 부서(DEPT_EMP)를 조인해서
부서명으로 그룹화하고 그 부서의 직원수를 계산(COUNT)한다.
둘다 적절한 SQL문은 아니고 설명을 위해 작성한 SQL문이다.
2. 각 부서에서 가장 오래 근무한 직원을 출력하시오.
SELECT DEPT_NAME
, (SELECT FIRST_NAME
FROM DEPT_EMP DE
INNER JOIN EMPLOYEES E ON E.EMP_NO = DE.EMP_NO
WHERE DE.TO_DATE = '9999-01-01' AND DE.DEPT_NO=D.DEPT_NO
ORDER BY FROM_DATE
LIMIT 1) EMPLOYEE
FROM DEPARTMENTS D;
Subquery에서 조인을 사용했다.
먼저, 부서 리스트(DEPARTMENTS )를 조회한다.
부서를 가져오면서 각 부서(DEPT_EMP)에 배정받은 날짜(FROM_DATE)가 가장 빠른 직원의 이름(EMPLOYEES)을 조회한다.
연습: 가장 오래된 직원 10명이 근무했던 처음과 마지막 부서를 출력하시오.
SELECT EM.*
, (SELECT DEPT_NAME FROM DEPT_EMP DEE, DEPARTMENTS DE
WHERE DEE.DEPT_NO=DE.DEPT_NO ORDER BY FROM_DATE LIMIT 1) FIRST_DEPT
, (SELECT DEPT_NAME FROM DEPT_EMP DEE, DEPARTMENTS DE
WHERE DEE.DEPT_NO=DE.DEPT_NO ORDER BY FROM_DATE DESC LIMIT 1) LAST_DEPT
FROM EMPLOYEES EM
ORDER BY HIRE_DATE
LIMIT 10;
3. 각 부서에서 급여를 가장 많이 받는 직원 리스트를 구하시오.
SELECT DEPT_NAME
, (SELECT FIRST_NAME
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 DE.DEPT_NO=D.DEPT_NO
ORDER BY SALARY DESC
LIMIT 1) EMPLOYEE
FROM DEPARTMENTS D;
급여를 알기 위해 급여 테이블을 Subquery의 조인에 추가하였다.
먼저, 부서 리스트(DEPARTMENTS )를 조회한다.
부서를 가져오면서
해당 부서에 속한 직원들(DEPT_EMP)의 급여(SALARIES)를 확인한 뒤,
급여가 큰 순으로 정렬해서 가장 많이 받는 직원 1명(LIMIT)의 이름(EMPLOYEES)을 가져와 출력하게 된다.
4.부서별로 직원 수를 구하시오.
1번에서는 부서별 직원수를 조인과 SELECT절에 subquery를 사용하였다.
이 경우 데이터의 양이 많을 경우 속도가 느려질 수 있는데
속도를 고려하여 적절한 SQL을 작성하시오.
SELECT DEPT_NAME, CNT
FROM DEPARTMENTS D
INNER JOIN (
SELECT DEPT_NO, COUNT(*) CNT
FROM DEPT_EMP
WHERE TO_DATE='9999-01-01'
GROUP BY DEPT_NO
) DS ON D.DEPT_NO=DS.DEPT_NO
ORDER BY D.DEPT_NO
1번 문제에서 두 가지 방법을 사용하였다.
- 10개의 부서를 추출하고, 각 부서의 인원수를 계산하는 것
- 부서와 인원 테이블을 조인해서 데이터를 구성하고 부서명으로 그룹화 한 뒤 인원을 계산하는 것이다.
조인을 사용하는 것이 추천되는 방식인데
문제는 문자열로 그룹화 하는 것이다.
가급적 정형화된 코드로 그룹화 하는 것이 좋다.
더욱이 조인을 먼저 하기 때문에 많은 데이터 조인(부서수 10 & 인원수 240,124)이 발생한다.
개인적으로 가장 선호하는 방식은 위에 작성한 코드처럼
부서코드로 그룹화해서 인원을 계산하고, 부서명을 찾기 위해 조인하는 것이다.
부서수가 10개이므로 10개의 데이터(10 & 10)로 조인을 하기 때문에 더 빠르게 처리된다.
SQL 속도 처리(튜닝)는 처리하는 데이터 개수만 잘 세어도 거의 문제가 발생하지 않는다.
따라서 항상 몇 건의 데이터를 조인하고 그룹화 하는지 파악해야 한다.
5. 전체 평균보다 많이 받는 직원 수를 계산하시오.
SELECT COUNT(*) -- 107706
FROM SALARIES S
WHERE S.TO_DATE = '9999-01-01'
AND SALARY >= (SELECT AVG(SALARY) FROM SALARIES WHERE TO_DATE = '9999-01-01');
전체 평균을 Subquery로 구하여 조건절(WHERE)에서 사용하였다.
먼저, 현재 근무 중인 직원들의 급여 평균(AVG(SALARY))를 계산하고
그 금액보다 큰(>=) 직원들 인원을 계산(COUNT)한다.
6. 다음과 같이 퇴직한 직원 정보를 구하시오.
SELECT *
FROM EMPLOYEES E
WHERE NOT EXISTS(SELECT 1 FROM DEPT_EMP DE WHERE DE.TO_DATE = '9999-01-01' AND E.EMP_NO=DE.EMP_NO);
SELECT *
FROM EMPLOYEES E
WHERE EMP_NO NOT IN (SELECT DISTINCT EMP_NO FROM DEPT_EMP DE WHERE DE.TO_DATE = '9999-01-01');
이 데이터에서는 퇴직 필드가 없기 때문에 퇴직의 개념부터 먼저 정의해야 한다.
이 데이터에서 근무중인 직원은 "실습 2 - GROUP" 에서 정의한 데로
급여나 현재 근무 부서, 업무의 종료일자가 '9999-01-01'인 것이다.
따라서 퇴사는 종료일자가 '9999-01-01'아닌 것이 될 것이다.
즉, 직원중에서 급여, 근무 부서, 업무 테이블에 종료일자가 '9999-01-01'인 데이터가 없는(NOT EXISTS, NOT IN ) 직원이 퇴사자가 된다.
EXISTS와 IN 둘다 많이 사용되는 명령어로
DBMS에 따라 다르지만 대부분 Subquery의 양이 많으면 EXISTS를 사용하라고 권하고 있다.
개인적으로도 속도가 빨라서 EXISTS를 자주 사용한다.
다음 SQL문을 실행하여 정확성을 검증해 보자.
전체 직원수(300,024) = 근무중인 직원수(240,124) + 퇴사한 직원수(59,900)
SELECT COUNT(*)
FROM EMPLOYEES E;
SELECT COUNT(DISTINCT EMP_NO)
FROM DEPT_EMP DE
WHERE DE.TO_DATE = '9999-01-01';
7. 연도별 퇴직 직원수를 구하시오.
SELECT YEAR(TO_DATE), COUNT(*)
FROM (
SELECT DE.EMP_NO, MAX(TO_DATE) TO_DATE
FROM DEPT_EMP DE
WHERE NOT EXISTS(SELECT 1 FROM DEPT_EMP DE1 WHERE DE1.TO_DATE = '9999-01-01' AND DE1.EMP_NO=DE.EMP_NO)
GROUP BY DE.EMP_NO
) DS
GROUP BY YEAR(TO_DATE)
앞 5번 SQL문을 Subquery로 하여 그룹화 하였다.
퇴사 날짜를 구하기 위해 직원 테이블이 아닌 할당된 부서테이블(DEPT_EMP)을 사용하였다.
퇴사 날짜는 부서에서 근무한 마지막 날짜인 종료날짜(TO_DATE) 중에서 가장 큰값(Max)이 퇴사 날짜가 될 것이다.
각 개인별(GROUP BY DE.EMP_NO) 퇴사 날짜를 구해서
퇴사날짜를 년도별로 그룹화 하면 연도별 퇴사 직원수를 구할 수 있다.
이 경우는 다음과 같은 SQL 문이 더 효율적일 수 있다.
생각해보기: 각 부서에서 급여를 가장 많이 받는 직원이름과 급여를 출력하시오.
