▶ SUBQUERY
SELECT 문장 안에 포함된 또 다른 SELECT 문장으로 메인 쿼리가 실행되기 전 한 번만 실행됨
비교 연산자의 오른쪽에 기술해야 하며 반드시 괄호로 묶어야 함
서브쿼리와 비교할 항목은 반드시 서브쿼리의 SELECT한 항목의 개수와 자료형을 일치시켜야 함
✓ 예시
전 직원의 평균 급여보다 많은 급여를 받고 있는 직원의 사번, 이름, 직급코드 ,급여 조회
SELECT EMP_ID, EMP_NAME, JOB_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY >= (SELECT AVG(SALARY) FROM EMPLOYEE);
✓ 유형
1. 단일행 서브쿼리
: 서브쿼리의 조회 결과 값의 개수가 1개인 서브쿼리
2. 다중행 서브쿼리
: 서브쿼리의 조회 결과 값의 행이 여러 개인 서브쿼리
3. 다중열 서브쿼리
: 서브쿼리의 조회 결과 컬럼의 개수가 여러 개인 서브쿼리
4. 다중행 다중열 서브쿼리
: 서브쿼리의 조회 결과 컬럼의 개수와 행의 개수가 여러 개인 서브쿼리
5. 상(호연)관 서브쿼리
: 서브쿼리가 만든 결과 값을 메인 쿼리가 비교 연산할 때 메인 쿼리 테이블의 값이 변경되면 서브쿼리의 결과 값도 바뀌는 서브쿼리
6. 스칼라 서브쿼리
: 상관쿼리이면서 결과 값이 한 개인 서브쿼리
▶ 단일 행 서브쿼리(SINGLE ROW SUBQUERY)
서브쿼리의 조회 결과값의 개수가 1개일 때 단일행 서브쿼리 앞에는 일반 연산자 사용 ( < , >, <=, >=, =, !=/<>/^= (서브쿼리) )
- 전 직원의 급여 평균보다 많은 급여를 받는
직원의 이름, 직급, 부서, 급여 조회
SELECT EMP_NAME, JOB_CODE, DEPT_CODE, SALARY
FROM EMPLOYEE E
WHERE SALARY >= (SELECT AVG(SALARY)
FROM EMPLOYEE)
ORDER BY 2;
서브쿼리의 조회 결과 값의 행이 여러 개인 서브쿼리
- 부서 별 최고 급여를 받는 직원의 이름, 직급, 부서, 급여 조회
SELECT EMP_NAME, JOB_CODE, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY IN (SELECT MAX(SALARY)
FROM EMPLOYEE
GROUP BY DEPT_CODE)
ORDER BY 3;＊ 다중 행 서브쿼리 앞에는 일반 비교 연산자 사용 불가
(사용 가능 연산자 : IN/NOT IN, >ANY/<ANY, >ALL/<ALL, EXIST/NOT EXIST 등)
▶ 다중 열 서브쿼리
서브쿼리의 조회 결과 컬럼의 개수가 여러 개인 서브쿼리
- 퇴사한 여직원과 같은 부서, 같은 직급에 해당하는 사원의 이름, 직급, 부서, 입사일 조회
SELECT EMP_NAME, JOB_CODE, DEPT_CODE, HIRE_DATE
FROM EMPLOYEE
WHERE (DEPT_CODE, JOB_CODE) IN (SELECT DEPT_CODE, JOB_CODE
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO, 8, 1)=2 AND ENT_YN=‘Y’);
▶ 다중 행 다중 열 서브쿼리
서브쿼리의 조회 결과 컬럼의 개수와 행의 개수가 여러 개인 서브쿼리
- 직급별 최소 급여를 받는 직원의 사번, 이름, 직급, 급여 조회
SELECT EMP_ID, EMP_NAME, JOB_CODE, SALARY
FROM EMPLOYEE
WHERE (JOB_CODE, SALARY) IN (SELECT JOB_CODE, MIN(SALARY)
FROM EMPLOYEE
GROUP BY JOB_CODE)
ORDER BY 3;
▶ 상(호연)관 서브쿼리
서브쿼리가 만든 결과 값을 메인 쿼리가 비교 연산할 때
메인 쿼리 테이블의 값이 변경되면 서브쿼리의 결과 값도 바뀌는 서브쿼리
- 관리자가 있는 사원들 중 관리자의 사번이 EMPLOYEE테이블에 존재하는 직원의 사번, 이름, 소속 부서, 관리자 사번 조회
SELECT EMP_ID, EMP_NAME, DEPT_CODE, MANAGER_ID
FROM EMPLOYEE E
WHERE EXISTS (SELECT EMP_ID
FROM EMPLOYEE M
WHERE E.MANAGER_ID = M.EMP_ID);
▶ 스칼라 서브쿼리
✓ SELECT절 예시
상관쿼리이면서 결과 값이 한 개인 서브쿼리
- 모든 사원의 사번, 이름, 관리자 사번, 관리자 명 조회
SELECT E.EMP_ID, E.EMP_NAME, E.MANAGER_ID, NVL((SELECT M.EMP_NAME
FROM EMPLOYEE M
WHERE E.MANAGER_ID = M.EMP_ID), ‘없음’) AS 관리자명
FROM EMPLOYEE E
ORDER BY 1;
✓ WHERE절 예시
- 자신이 속한 직급의 평균 급여보다 많이 받는 직원의 이름, 직급, 급여 조회
SELECT EMP_NAME, JOB_CODE, SALARY
FROM EMPLOYEE E1
WHERE SALARY >= (SELECT AVG(SALARY)
FROM EMPLOYEE E2
WHERE E2.JOB_CODE = E1.JOB_CODE)
ORDER BY 2;✓ ORDER BY절 예시
- 모든 직원의 사번, 이름, 소속 부서를 조회 후 부서명 내림차순 정렬
SELECT EMP_ID, EMP_NAME, DEPT_CODE
FROM EMPLOYEE
ORDER BY (SELECT DEPT_TITLE
FROM DEPARTMENT
WHERE DEPT_CODE = DEPT_ID)
DESC NULLS LAST;
▶ 인라인 뷰(INLINE-VIEW)
FROM절에 서브쿼리 사용한 것
✓ 예시
SELECT ROWNUM, EMP_NAME, SALARY
FROM EMPLOYEE
WHERE ROWNUM <= 5
ORDER BY SALARY DESC;＊ ROWNUM은 FROM절을 수행하면서 붙여지기 때문에 top-N분석 시 SELECT절에 사용한 ROWNUM이 의미 없게 됨
SELECT ROWNUM, EMP_NAME, SALARY
FROM (SELECT *
FROM EMPLOYEE
ORDER BY SALARY DESC)
WHERE ROWNUM <= 5;＊ FROM절에 이미 정렬된 서브쿼리(인라인 뷰) 적용 시 ROWNUM이 top-N분석에 사용 가능
▶ WITH
서브쿼리에 이름을 붙여주고 인라인 뷰로 사용 시 서브쿼리의 이름으로 FROM절에 기술 가능
같은 서브쿼리가 여러 번 사용될 경우 중복 작성을 피할 수 있고 실행속도도 빨라진다는 장점이 있음
✓ 예시
WITH TOPN_SAL AS (SELECT EMP_NAME, SALARY
FROM EMPLOYEE
ORDER BY SALARY DESC)
SELECT ROWNUM, EMP_NAME, SALARY
FROM TOPN_SAL;
▶ RANK() OVER
SELECT 순위, EMP_NAME, SALARY
FROM (SELECT EMP_NAME, SALARY,
RANK() OVER(ORDER BY SALARY DESC) AS 순위
FROM EMPLOYEE
ORDER BY SALARY DESC);
▶ DENSE_RANK() OVER
SELECT 순위, EMP_NAME, SALARY
FROM (SELECT EMP_NAME, SALARY,
DENSE_RANK() OVER(ORDER BY SALARY DESC) AS 순위
FROM EMPLOYEE
ORDER BY SALARY DESC);
