07. SUBQUERY

게시 2023/01/23

By Kim Jung Wook 13 분읽는 시간

SUBQUERY

하나의 SQL문 안에 포함된 또 다른 SQL(SELECT)문
메인쿼리를 위해 보조 역할을 하는 쿼리문
SELECT, FROM, WHERE, HAVING 절에서 사용가능

1) 단일행 서브쿼리

서브쿼리의 조회 결과 값의 개수가 1개 ➡️ 조회 결과가 1행 1열
단일행 서브쿼리 앞에는 비교 연산자 사용
< , > , <= , >= , = , != , <> , ^=

  
-- 전 직원의 급여 평균보다 많은(초과) 급여를 받는 직원의
-- 이름, 직급, 부서, 급여를 직급 순으로 정렬하여 조회
SELECT EMP_NAME, JOB_NAME, DEPT_TITLE, SALARY
FROM EMPLOYEE
NATURAL JOIN JOB
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
WHERE SALARY >
(SELECT AVG(SALARY)
FROM EMPLOYEE)
ORDER BY JOB_CODE;

-- 가장 적은 급여를 받는 직원의
-- 사번, 이름, 직급, 부서코드, 급여, 입사일을 조회
SELECT EMP_ID, EMP_NAME, JOB_NAME, SALARY, HIRE_DATE
FROM EMPLOYEE
JOIN JOB USING (JOB_CODE)
WHERE SALARY = (SELECT MIN(SALARY) FROM EMPLOYEE);

2) 다중행 서브쿼리

서브쿼리의 조회 결과 값의 개수가 여러행 ➡️ 행은 여러개 컬럼은 1개
다중행 서브쿼리 앞에는 일반 비교 연산자 사용 불가
IN / NOT IN : 여러 개의 결과 값 중에서 한 개라도 일치하는 값이 있다면 혹은 없다면 이라는 의미
> ANY , < ANY : 여러개의 결과값 중에서 가장 작은 값보다 큰가? / 가장 큰 값 보다 작은가?
> ALL , < ALL : 여러개의 결과값 중에서 가장 큰 값 보다 큰가?/ 가장 작은 값 보다 작은가?

  
-- 부서별 최고 급여를 받는 직원의
-- 이름, 직급, 부서, 급여를 부서 순으로 정렬하여 조회
SELECT EMP_NAME, JOB_CODE, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY IN 
(SELECT MAX(SALARY)
FROM EMPLOYEE
GROUP BY DEPT_CODE)
ORDER BY DEPT_CODE;

-- 사수에 해당하는 직원에 대해 조회
-- 사번, 이름, 부서명, 직급명, 구분(사수 / 사원)
-- 사수 == MANAGER_ID 컬럼의 데이터
SELECT EMP_ID, EMP_NAME, DEPT_TITLE, JOB_NAME,
  CASE
      WHENEMP_ID IN (SELECT MANAGER_ID
                      FROM EMPLOYEE
                      WHERE MANAGER_ID IS NOT NULL)
        THEN '사수'
    ELSE '사원'
  END "구분"
FROME EMPLOYEE
LEFT JOIN DEPARTMENT ON (DEPT_ID = DEPT_CODE)
LEFT JOIN USING (JOB_CODE);

  
-- 대리 직급의 직원들 중에서 과장 직급의 최소 급여보다 많이 받는 직원의
-- 사번, 이름, 직급, 급여를 조회하세요
SELECT EMP_ID, EMP_NAME, JOB_NAME, SALARY
FROM EMPLOYEE
NATURAL JOIN JOB
WHERE JOB_NAME = '대리' 
AND SALARY > ANY 
(SELECT SALARY
FROM EMPLOYEE
NATURAL JOIN JOB
WHERE JOB_NAME = '과장');

  
-- 차장 직급의 급여 중 가장 큰값 보다 많이 받는 과장 직급 직원의
-- 사번, 이름, 직급, 급여를 조회하세요
SELECT EMP_ID, EMP_NAME, JOB_NAME, SALARY
FROM EMPLOYEE
NATURAL JOIN JOB
WHERE JOB_NAME = '과장'
AND SALARY > ALL
(SELECT SALARY
FROM EMPLOYEE
NATURAL JOIN JOB
WHERE JOB_NAME = '차장');

  
-- 서브쿼리 안에 서브쿼리. 서브쿼리 중첩
-- LOCATION 테이블에서 NATIONAL_CODE가 KO인 경우의 LOCAL_CODE와
-- DEPARTMENT 테이블의 LOCATION_ID와 동일한 DEPT_ID가 
-- EMPLOYEE 테이블의 DEPT_CODE와 동일한 사원을 구하시오

SELECT EMP_ID, EMP_NAME, DEPT_CODE
FROM EMPLOYEE
WHERE DEPT_CODE IN 
(SELECT DEPT_ID
FROM DEPARTMENT
WHERE LOCATION_ID = 
(SELECT LOCAL_CODE
FROM LOCATION
WHERE NATIONAL_CODE = 'KO'));

3) 다중열 서브쿼리

서브쿼리의 SELECT 절에 나열된 항목수가 여러개 ➡️ 컬럼이 여러개, 행은 1개

  
-- 퇴사한 여직원과 같은 부서, 같은 직급에 해당하는
-- 사원의 이름, 직급, 부서, 입사일 조회
SELECT EMP_NAME, JOB_CODE, DEPT_CODE, HIRE_DATE
FROM EMPLOYEE
WHERE (DEPT_CODE, JOB_CODE) =
(SELECT DEPT_CODE, JOB_CODE
FROM EMPLOYEE
WHERE ENT_YN = 'Y'
AND SUBSTR(EMP_NO, 8, 1) = '2');

4) 다중행 다중열 서브쿼리

서브쿼리 조회 결과 행 수와 열 수가 여러개 일 때

  
-- 본인 직급의 평균 급여를 받고 있는 직원의
-- 사번, 이름, 직급, 급여를 조회하세요
-- 단, 급여와 급여 평균은 만원단위로 계산하세요. TRUNC(컬럼명, -4)
SELECT EMP_ID, EMP_NAME, JOB_CODE, TRUNC(SALARY, -4)
FROM EMPLOYEE
WHERE (JOB_CODE, TRUNC(SALARY, -4)) IN
(SELECT JOB_CODE, TRUNC(AVG(SALARY), -4)
FROM EMPLOYEE
GROUP BY JOB_CODE);

5) 상[호연]관 서브쿼리

상관 쿼리는 메인쿼리가 사용하는 테이블 값을 서브쿼리가 이용해서 결과를 만든다. 서브쿼리의 단독 수행이 안된다. ( 단일행, 다중행, 다중열, 다중행다중열 서브쿼리는 비상관 서브쿼리 )
메인쿼리의 테이블 값이 변경되면 서브쿼리의 결과값도 바뀌게 되는 구조이다
서브쿼리는 먼저 메인쿼리를 한 행 조회하고, 해당 행이 서브쿼리의 조건을 충족하는지 호가인하여 SELECT를 진행한다
해석순서 메인쿼리 1행 → 1행에 대한 서브쿼리 메인쿼리 2행 → 2행에 대한 서브쿼리 … ➡️ 메인쿼리의 행의 수 만큼 서브쿼리가 생성되어 진행됨

  
-- 직급별 급여 평균보다 급여를 많이 받는 직원의
-- 이름, 직급코드, 급여 조회
SELECT EMP_NAME, JOB_CODE, SALARY
FROM EMPLOYEE MAIN
WHERE SALARY > 
(SELECT AVG(SALARY)
FROM EMPLOYEE SUB
WHERE SUB.JOB_CODE = MAIN.JOB_CODE);

-- 부서별 입사일이 가장 빠른 사원의
-- 사번, 이름, 부서명(NULL이면 '소속없음'), 직급명, 입사일을 조회
-- 입사일이 빠른 순으로 조회
-- 단, 퇴사 직원은 제외
SELECT EMP_ID, EMP_NAME, NVL(DEPT_TITLE, '소속없음'), 
JOB_NAME, HIRE_DATE
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
LEFT JOIN JOB USING(JOB_CODE)
WHERE ENT_YN = 'N'
AND HIRE_DATE =
(SELECT MIN(HIRE_DATE)
FROME EMPLOYEE
WHERE SUB.DEPT_CODE = MAIN.DEPT_CODE)
ORDER BY HIRE_DATE;

6) 스칼라 서브쿼리

SELECT 절에 사용되는 서브쿼리 결과로 1행만 반환
SQL에서 단일 값을 가르켜 스칼라라고 한다.
➡️ SELECT절에 작성되는 단일행 서브쿼리

  
-- 모든 직원의 이름, 직급, 급여
-- 전체 사원 중 가장 높은 급여와의 차
SELECT EMP_NAME, JOB_CODE, SALARY,
  (SELECT MAX(SALARY) FROM EMPLOYEE) - SALARY
FROM EMPLOYEE;

-- 각 직원들이 속한 직급의 급여 평균 조회
SELECT EMP_NAME, JOB_CODE, SALARY, 
  (SELECT CEIL(AVG(SALARY) FROM EMPLOYEE SUB 
    WHERE SUB.JOB_CODE = MAIN.JOB_CODE)
FROM EMPLOYEE MAIN;

-- 모든 사원의 사번, 이름, 관리자사번, 관리자명을 조회
-- 단, 관리자가 없는 경우 '없음'으로 표시
SELECT EMP_ID, EMP_NAME, NVL(MANAGER_ID, '없음'), 
  NVL((SELECT EMP_NAME FROM EMPLOYEE SUB 
    WHERE SUB.EMP_ID = MAIN.MANAGER_ID), '없음') "관리자명"
FROM EMPLOYEE MAIN;

7) 인라인 뷰(INLINE_VIEW)

FROM 절에서 서브쿼리를 사용하는 경우
서브쿼리가 만든 결과의 집합(RESULT SET)을 테이블 대신 사용한다

  
SELECT * FROM(
SELECT EMP_NAME "이름", DEPT_TITLE "부서"
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
)
WHERE `부서` = '기술지원부';

-- 인라인뷰를 활용한 TOP_N 분석
-- 전직원 중 급여가 높은 상위 5명의 순위, 이름, 급여 조회
-- ROWNUM 컬럼 : 행 번호를 나타내는 가상 컬럼
SELECT ROWNUM, EMP_NAME, SALARY
FROM (SELECT EMP_NAME, SALARY FROM EMPLOYEE ORDER BY SALARY DESC)
WHERE ROWNUM <= 5;

8) RANK() OVER

동일한 순위 이후의 등수를 동일한 인원수 만큼 건너뛰고 순위 계산 EX) 공동 1위가 2명이면, 다음 순위는 2위가 아니라 3위

  
-- 사원별 급여 순위
< ROWNUM >
SELECT ROWNUM, EMP_NAME, SALARY
FROM (SELECT EMP_NAME, SARARY FROM EMPLOYEE ORDER BY SALARY DESC);
-- 19 전형돈 2000000, 20 윤은해 2000000, 21 박나라 1800000

< RANK() OVER(`정렬순서`) >
SELECT RANK() OVER(ORDER BY SALARY DESC) "순위", 
EMP_NAME, SALARY
FROM EMPLOYEE;
-- 19 전현돈 2000000, 19 윤은해 2000000, 21 박나라 1800000

< DENSE_RANK() OVER(`정렬순서`) >
-- 공동 1위가 2명이어도 다음 순위는 2위
SELECT DENSE_RANK() OVER(ORDER BY SALARY DESC) "순위",
EMP_NAME, SALARY
FROM EMPLOYEE;
-- 19 전현돈 2000000, 19 윤은해 2000000, 20 박나라 1800000

9) WITH

서브쿼리에 이름을 붙여주고 사용시 이름을 사용하게 함
인라인뷰로 사용될 서브쿼리에 주로 사용됨
실행 속도가 빨라진다는 장점이 있다

  
-- 전 직원의 급여 순위
-- 순위, 이름, 급여 조회
WITH TOP_SAL AS
(SELECT EMP_NAME, SALARY
FROM EMPLOYEE
ORDER BY SALARY DESC)
SELECT ROWNUM, EMP_NAME, SALARY
FROM TOP_SAL;

10) 연습문제

  
-- 직급별 나이가 가장 어린 직원의 사번, 직급명, 나이, 
-- 보너스 포함 연봉을 조회하고 나이순으로 내림차순 정렬하세요.
-- 단, 연봉은 ₩124,800,000 으로 출력되게 하세요.

SELECT EMP_ID "사번", JOB_NAME "직급명", 
FLOOR(MONTHS_BETWEEN(SYSDATE, TO_DATE(SUBSTR(EMP_NO, 1, 6), 'RRMMDD')) / 12) "만 나이",
TO_CHAR((SALARY *( 1 + NVL(BONUS, 0) ) * 12), 'L999,999,999') "보너스 포함 연봉"
FROM EMPLOYEE MAIN
NATURAL JOIN JOB
WHERE FLOOR(MONTHS_BETWEEN(SYSDATE, TO_DATE(SUBSTR(EMP_NO, 1, 6), 
'RRMMDD')) / 12) = ( SELECT MIN(FLOOR(MONTHS_BETWEEN(SYSDATE, TO_DATE(SUBSTR(EMP_NO, 1, 6), 'RRMMDD')) / 12))
FROM EMPLOYEE SUB
WHERE (SUB.JOB_CODE = MAIN.JOB_CODE)
ORDER "만 나이" DESC;

KH 학원 공부, DB

DB ORACLE

SUBQUERY

인기 태그