Oracle - 서브쿼리(SUBQUERY) - Part2

다중 행 다중 열 서브쿼리

다중행 다중열 서브쿼리란 서브쿼리의 조회 결과 컬럼의 개수와 행의 개수가 여러 개인 서브쿼리이다.

 

[ 다중행 다중열 서브쿼리 예시 ]

- 직급별 최소 급여를 받는 직원의 사번, 이름, 직급, 급여 조회

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;

여기의 WHERE절에 있는 SELECT문인

SELECT JOB_CODE, MIN(SALARY)
FROM EMPLOYEE
GROUP BY JOB_CODE

이 코드를 실행하면 직급코드별 최저시급이 나타난다.

이 서브쿼리의 실행 결과를 가지고 메인쿼리의 값을 구하면

이렇게 매치가 되어 나타난다.

 

 

상(호연)관 서브쿼리

상관 서브쿼리란 서브쿼리가 만든 결과 값을 메인 쿼리가 비교 연산할 때 메인 쿼리 테이블의 값이 변경되면 서브쿼리의 결과 값도 바뀌는 서브쿼리이다.

상관 쿼리라고도 부른다. 상관커리는 먼저 메인쿼리의 한 행을 조회하고 해당 행이 서브쿼리의 조건을 충족하는 확인하여 SELECT 를 진행한다.

 

[ 상관 쿼리 예시]

- 관리자가 있는 사원들 중 관리자의 사번이 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 관리자명 -- SELECT절에 사용
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) -- WHERE절의 사용 예시
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) -- ORDER BY 사용 예시
DESC NULLS LAST;

 

 

 

 

인라인 뷰(INLINE-VIEW)

인라인 뷰란 FROM절에 서브쿼리 사용하는 것으로 서브쿼리가 만든 결과의 집합(RESULT SET)을 테이블 대신 사용한다.

 

[ 인라인 뷰 예시]

SELECT ROWNUM, EMP_NAME, SALARY
FROM (SELECT *
FROM EMPLOYEE
ORDER BY SALARY DESC) -- 인라인뷰를 이요하여 미리 SALARY 내림차순 정렬
WHERE ROWNUM <= 5; 

 

 

WITH

WITH란 서브쿼리에 이름을 붙여주고 인라인 뷰로 사용 시 서브쿼리의 이름으로 FROM절에 기술이 가능하다.

같은 서브쿼리가 여러 번 사용될 경우 중복 작성을 피할 수 있고 실행속도도 빨라진다는 장점이 있다.

 

[ WITH 예시 ]

WITH TOPN_SAL AS (SELECT EMP_NAME, SALARY
FROM EMPLOYEE
ORDER BY SALARY DESC)
SELECT ROWNUM, EMP_NAME, SALARY
FROM TOPN_SAL;

 

 

RANK() OVER / DENSC_RANK() OVER

RANK() OVER란 동일한 순위 이후 등수를 동일한 인원 수 만큼 건너 뛰고 순위를 계산한다.

즉, 공동 1위가 2명이면 다음순위는 3위로 나타난다.

 

DENSC_RANK() OVER란 동일한 순위 이후 등수를 건너 뛰지 않고 순위를 계산한다.

즉, 공동 1위가 2명이면 다음순위는 2위로 나타난다.

 

[ RANK() OVER / DENSC_RANK() OVER 예시 ]

 

RANK() OVER

SELECT 순위, EMP_NAME, SALARY
FROM (SELECT EMP_NAME, SALARY,
RANK() OVER(ORDER BY SALARY DESC) AS 순위
FROM EMPLOYEE
ORDER BY SALARY DESC);

 

DENSC_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);