1) 개념
- 하나의 SELECT 문장의 절 안에 포함된 또 하나의 SELECT 문장
- 메인 쿼리가 실행되기 이전에 한번만 실행
- 서브쿼리와 비교할 항목은 반드시 서브쿼리의 SELECT 한 항목의 개수와 자료형을 일치 시켜야 함
5-1. 유형
1) 단일행 서브쿼리
- 앞에 비교 연산자 사용
- <, >, >=, <=, != / ^= / <>
-- 노옹철 사원의 급여보다 많이 받는 직원의 사번, 이름, 부서, 직급, 급여 조회
SELECT
EMP_ID
, EMP_NAME
, DEPT_CODE
, JOB_CODE
, SALARY
FROM EMPLOYEE
WHERE SALARY > (SELECT
SALARY
FROM EMPLOYEE
WHERE EMP_NAME = '노옹철'
);- HAVING 절에서 사용되는 경우
-- 부서별 급여의 합계 중 합계가 가장 큰 부서의 부서명, 급여 합계를 조회
SELECT
DEPT_TITLE
, SUM(SALARY)
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
GROUP BY DEPT_TITLE
HAVING SUM(SALARY) = (SELECT
MAX(SUM(SALARY))
FROM EMPLOYEE
GROUP BY DEPT_CODE
);2) 다중행 서브쿼리
- 일반 비교 연산자 사용 불가능
- IN / NOT IN
-- 부서별 최고 급여를 받는 직원의 이름, 직급, 부서, 급여 조회
SELECT
EMP_NAME
, JOB_CODE
, DEPT_CODE
, SALARY
FROM EMPLOYEE
WHERE SALARY IN (SELECT
MAX(SALARY)
FROM EMPLOYEE
GROUP BY DEPT_CODE
);- < ANY / > ANY
- 여러 개의 결과 값 중에서 한개라도 크거나 작은 경우
- 서브 쿼리에서 나온 여러 개의 값들 중에서 하나라도 메인 쿼리의 값이 큰 경우
- 최저값보다 크면 됨
-- 대리 직급의 직원들 중에서
-- 과장 직급의 최소 급여보다 많이 받는 직원의 사번, 이름, 직급명, 급여 조회
SELECT
EMP_ID
, EMP_NAME
, JOB_NAME
, SALARY
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
WHERE JOB_NAME = '대리'
AND SALARY > ANY(SELECT
SALARY
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
WHERE JOB_NAME = '과장'
);- < ALL / > ALL
- 모든 값보다 크거나 작은 경우
- 메인 쿼리의 값이 모든 서브쿼리의 값보다 커야함(최대값보다 커야 함)
-- 차장 직급의 급여의 가장 큰 값보다 많이 받는 과장 직급의
-- 사번, 이름, 직급명, 급여를 조회
SELECT
EMP_ID
, EMP_NAME
, JOB_CODE
, SALARY
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
WHERE JOB_NAME = '과장'
AND SALARY > ALL(SELECT
SALARY
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
WHERE JOB_NAME = '차장'
);- EXIST / NOT EXIST
- 서브쿼리에만 사용하는 연산자
- 값이 존재하는가 혹은 존재하지 않는가
3) 다중열 서브쿼리
-- 퇴직한 여사원과 같은 부서, 같은 직급에 해당하는 사원의 이름, 직급, 부서, 입사일 조회
SELECT
EMP_NAME
, JOB_CODE
, DEPT_CODE
, HIRE_DATE
FROM EMPLOYEE
WHERE DEPT_CODE = (SELECT
DEPT_CODE
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO,8,1) = '2'
AND ENT_YN = 'Y'
)
AND JOB_CODE = (SELECT
DEPT_CODE
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO,8,1) = '2'
AND ENT_YN = 'Y'
);다중열 서브쿼리로 변경
- 컬럼 나열 순서도 중요(컬럼의 순번으로 비교)
- 서브 쿼리의 비교 조건은 똑같은데, 비교해야 하는 컬럼이 다른 경우 사용
- 컬럼을 여러개 써준다.
SELECT EMP_NAME , JOB_CODE , DEPT_CODE , HIRE_DATE FROM EMPLOYEE WHERE (DEPT_CODE, JOB_CODE) = (SELECT DEPT_CODE , JOB_CODE FROM EMPLOYEE WHERE SUBSTR(EMP_NO, 8,1) = '2' AND ENT_YN = 'Y' );
3-1) INLINE VIEW(인라인뷰)
- FROM절에서의 서브쿼리 사용
- 서브쿼리의 결과(RESULT SET)을 테이블 대신 사용
-- 인라인뷰로 직급별 평균 급여를 계산한 테이블을 만들고,
-- EMPLOYEE와 JOIN시
-- 평균 급여가 본인의 급여와 동일하면 조인하게 조건을 줘서
-- 직급별 평균 급여에 맞는 급여를 받고 있는 직원을 조회하는 구문
SELECT
E.EMP_NAME
, J.JOB_NAME
, E.SALARY
FROM (SELECT
JOB_CODE
, TRUNC(AVG(SALARY),-5) AS JOBAVG
FROM EMPLOYEE
GROUP BY JOB_CODE) V
JOIN EMPLOYEE E ON(V.JOBAVG = E.SALARY AND V.JOB_CODE = E.JOB_CODE)
JOIN JOB J ON (V.JOB_CODE = J.JOB_CODE)
ORDER BY J.JOB_NAME;-
주의할 점
- 인라인뷰의 결과만이 남아 있으므로 서브쿼리에서 조회에 사용하지 않은 컬럼은 조회할 수 없으며, 별칭을 사용했다면 해당 별칭으로 조회해야 함 > 서브 쿼리를 통해 만들어낸 결과 테이블에 있는 정보만이 사용 가능
-
인라인뷰를 사용한 TOP-N 분석
- ORDER BY 한 결과에 ROWNUM을 붙인다(ROWNUM은 행 번호를 의미)
- ROWNUM은 WHERE절에서 번호 부여
-- 현재는 WHERE절에서 ROWNUM이 결정되어 급여를 많이 받는 순서와 관계 없는 번호를 가짐 SELECT ROWNUM , EMP_NAME , SALARY FROM EMPLOYEE ORDER BY SALARY DESC; -- 따라서 원하는 순서의 ROWNUM이 붙게 하려면 인라인뷰를 활용해야 한다. SELECT ROWNUM , V.EMP_NAME , V.SALARY FROM (SELECT E.* FROM EMPLOYEE E ORDER BY E.SALARY DESC ) V WHERE ROWNUM <= 5;- 6위에서 10위까지의 조회
- WHERE절에서 ROWNUM은 1로 시작하고 해당 값이 FALSE가 되어 다음 행을 확인할 때 다시 1로 확인하여 모든 행이 6~10 사이라는 조건을 만족할 수 없어 결과가 0행이 된다.
- ROWNUM은 WHERE절에서 부여되기 때문에 첫줄이 조건을 충족하지 못하기 때문에 삭제되고 그 다음행에 다시 1번이 부여된다. 이것이 반복되기 때문에 조건을 영원히 충족하지 못하게 된다.
SELECT ROWNUM , V.EMP_NAME , V.SALARY FROM (SELECT E.* FROM EMPLOYEE E ORDER BY E.SALARY DESC ) V WHERE ROWNUM BETWEEN 6 AND 10;- 올바른 정답
-- FROM 절의 ROWNUM에 별칭을 지어 컬럼이 생성된 것처럼 함. SELECT V2.RNUM , V2.EMP_NAME , V2.SALARY FROM (SELECT ROWNUM RNUM , V.EMP_NAME , V.SALARY FROM (SELECT E.* FROM EMPLOYEE E ORDER BY E.SALARY DESC ) V ) V2 WHERE RNUM BETWEEN 6 AND 10;- STOPKEY 사용
SELECT V2.RNUM , V2.EMP_NAME , V2.SALARY FROM (SELECT ROWNUM RNUM , V.EMP_NAME , V.SALARY FROM (SELECT E.* FROM EMPLOYEE E ORDER BY E.SALARY DESC ) V WHERE ROWNUM < 11 ) V2 WHERE RNUM BETWEEN 6 AND 10;-
RANK()
- 동일한 순위 이후의 등수를 동일한 인원수만큼 건너 뛰고 다음 순위 계산
- 1등 2등 2등 4등...
SELECT EMP_NAME , SALARY , RANK() OVER(ORDER BY SALARY DESC) 순위 FROM EMPLOYEE; -
DENSE_RANK()
- 중복되는 순위 이후의 등수를 이후 등수로 처리
- 같은 2등이 2명이라면 1등 2등 2등 3등..
-
WITH 이름 AS (쿼리문)
- 서브쿼리에 이름을 붙여주고 사용할 경우 붙여준 이름으로 재사용 가능
- 인라인뷰로 사용 될 서브쿼리에서 이용되며 같은 서브쿼리가 여러번 사용 될 경우 중복해서 작성하지 않아도 되고, 실행 속도도 빨라진다
WITH TOPN_SAL AS (SELECT E.EMP_ID , E.EMP_NAME , E.SALARY FROM EMPLOYEE E ORDER BY E.SALARY DESC ) SELECT ROWNUM , T.EMP_NAME , T.SALARY FROM TOPN_SAL T;
4) 상호연관 서브쿼리
- 일반적으로는 서브쿼리가 만든 결과 값을 메인 쿼리가 비교 연산 하지만 상관 서브쿼리는 메인 쿼리가 사용하는 테이블의 값을 서브쿼리가 이용해서 결과를 만든다.
- 메인 쿼리의 테이블 값이 변경되면, 서브쿼리의 결과 값도 바뀜
-- 관리자 사번이 EMPLOYEE 테이블에 존재하는 직원에 대한 조회
-- 메인쿼리로부터 MANAGER_ID 를 가져오고 그것을 EMP_ID와 비교해서 조회.
-- MANAGER_ID가 NULL인 경우 그것의 존재 여부가(EXISTS) FALSE이므로 아무것도 조회 안됨
-- 따라서 MANAGER_ID가 NULL이 아닌 경우에 조회 가능
-- 서브쿼리에서 조회한 값이 있으면 메인 쿼리 SELECT 수행
-- MANAGER_ID와 EMP_ID가 같은 값이 있느냐를 따져서 그것만 SELECT
SELECT
E.EMP_ID
, E.EMP_NAME
, E.DEPT_CODE
, E.MANAGER_ID
FROM EMPLOYEE E
WHERE EXISTS(SELECT
E2.EMP_ID
FROM EMPLOYEE E2
WHERE E.MANAGER_ID = E2.EMP_ID
);5) 스칼라 서브쿼리
- 단일행 서브쿼리 + 상관 쿼리
- SELECT 절에서 서브 쿼리 사용 시 결과 값은 반드시 1행으로 나와야 함(스칼라 서브쿼리 사용)
-- 동일 직급의 급여 평균보다 급여를 많이 받고 있는 직원의 직원명, 직급코드, 급여 조회
SELECT
EMP_NAME
, JOB_CODE
, SALARY
FROM EMPLOYEE E
WHERE SALARY > (SELECT
TRUNC(AVG(E2.SALARY), -5)
FROM EMPLOYEE E2
WHERE E.JOB_CODE = E2.JOB_CODE
);-- 모든 사원의 사번, 이름, 관리자 사번, 관리자명을 조회
-- SELECT 절에서 스칼라 서브쿼리 사용
SELECT
EMP_ID
, EMP_NAME
, MANAGER_ID
, NVL((SELECT EMP_NAME
FROM EMPLOYEE E2
WHERE E.MANAGER_ID = E2.EMP_ID
), '없음') 관리자명
FROM EMPLOYEE E
ORDER BY 1;
DREAM STARTER