[SQL] 서브 쿼리(SUBQUERY)(2) - 상(호연)관 서브쿼리, 스칼라 서브쿼리, 인라인 뷰(INLINE-VIEW), ROWNUM, RANK() OVER

상(호연)관 서브쿼리

o  서브 쿼리에서 메인 쿼리의 값을 이용해서 결과 값을 출력하며 이러한 서브 쿼리의 결과는 메인 쿼리에 영향을 미친다.

o  메인 쿼리에 있는 컬럼 값을 서브 쿼리(WHERE)에 가져다 쓰면 상관 쿼리이다.

o  일종의 이중 루프문 (메인 쿼리 : 외각 FOR문, 서브 쿼리 : 내각 FOR문)

    -> 서브 쿼리의 WHERE절에서 MAIN쿼리의 값을 가져와 비교를 한다.

    -> MAIN쿼리의 Row컬럼 별 서브 쿼리를 수행한다. (MAIN쿼리의 하나 Row당 서브 쿼리 전체 Row 비교 수행)

o  서브 쿼리는 단독으로 수행이 되지 않기에 서브 쿼리의 결과를 직접적으로 확인할 수는 없다.

 

o  EXISTS : 서브 쿼리의 결과(ROW)가 하나라도 있으면 TRUE /  결과(ROW)가 0개면 FALSE

관리자가 있는 사원들 중 관리자의 사번이 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);
상관서브쿼리로 DEPT_CODE가 있는 사원만 조회	상관서브쿼리로 급여를 가장 많이 받는 사원을 조회 사원명, 급여 조회
SELECT EMP_NAME, DEPT_CODE FROM EMPLOYEE E WHERE EXISTS(SELECT DEPT_CODE FROM EMPLOYEE WHERE DEPT_CODE=E.DEPT_CODE);	SELECT EMP_NAME,SALARY FROM EMPLOYEE E WHERE NOT EXISTS (SELECT SALARY FROM EMPLOYEE WHERE SALARY>E.SALARY);

 

스칼라 서브쿼리

o  상관 쿼리이면서 결과 값이 한 개(단일행)인 서브 쿼리

o  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;

 

o  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;

 

o  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)

o  FROM절에 서브쿼리를 작성하며 서브 쿼리의 결과를 테이블로 사용하는 것이다.

o  테이블은 실제 물리적 테이블에 있는 값이 아닌 가상의 테이블 값이다.

    -> 실제 테이블에서 추출한 가상의 테이블 (실제 DB에 저장되어 있지 않은 테이블)

o  1번만 사용할 수 있는 테이블로 쓰이는 서브 쿼리 문 -> 자바의 익명 클래스와 같은 개념

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분석에 사용 가능
여사원의 사번, 사원명, 부서코드, 성별 출력하기   SELECT * FROM     (SELECT EMP_ID, EMP_NAME, DEPT_CODE,         DECODE(SUBSTR(EMP_NO,8,1),'1','남','2','여') AS 성별         FROM EMPLOYEE) WHERE 성별='여';   FROM절 먼저 실행 후 WHERE절이 실행되기에 별칭을 사용할 수 있다.

 

스토어드 뷰(STORED VIEW)

o  테이블로 쓰이는 서브 쿼리문을 작성하고 이름을 부여해서 재호출을 가능하게 하는 것이다.

 

TOP-N분석 & 정렬순위 기준 잡기

o  TOP-N분석이란 ROW에 대해 순위를 부여하는 것 예) 급여를 많이 받는 TOP 3

o  만약 급여를 많이 받는 사원 1~3위 까지 조회하려고 하면 1~3위까지의 기준이 있어야 한다.

SELECT EMP_NAME, SALARY

FROM EMPLOYEE

WHERE ???기준???

ORDER BY SALARY DESC;

 

o  기준을 잡는 방법에는 2가지 방법이 있다.

1.     ROWNUM 컬럼을 이용하는 방법 -> 오라클이 자동으로 생성해주는 컬럼으로 모든 SELECT문에 포함되어있다.

2.     오라클 제공함수 이용하기 -> RANK() OVER()

 

1. ROWNUM

    ROW에 대한 번호(컬럼)를 출력순으로 부여해준다.

SELECT ROWNUM EMP_NAME, SALARY FROM EMPLOYEE WHERE ROWNUM<=3;

o  ROWNUM은 SELECT문에 사용되기에 WHERE문이 끝난 후 ROW에 대한 순번을 부여한다.

o  만약 ORDER BY를 사용할 경우 ORDER BY는 SELECT문 보다 실행 순서가 늦기에 ROWNUM의 순번이 부여된 후 정렬이 이루어진다. 그러나 ROW마다 부여가 된 번호는 변함이 없기에 정렬 시 원하는 결과가 나오지 않을 수 있다.

SELECT ROWNUM, EMP_NAME, SALARY FROM EMPLOYEE ORDER BY 3 DESC;

 o  따라서 인라인 뷰를 이용해 정렬된 가상의 테이블을 만들어 정렬된 ROW를 기준으로 ROWNUM을 부여하면 된다.

SELECT ROWNUM, E.* FROM (         SELECT EMP_NAME,SALARY         FROM EMPLOYEE         ORDER BY SALARY DESC     ) E WHERE ROWNUM<=3;

 o  ROWNUM의 또 다른 문제점은 ROWNUM 값을 호출하여 사용 시 1부터 센다는 점이다.

o  그래서 ROWNUM BETWEEN 2 AND 5 식으로 사용하게 되면 1부터 조회되지 않았기에 ROWNUM 2의 값을 알 수 없어 결과값이 출력되지 않는다.

SELECT ROWNUM, EMP_NAME, SALARY FROM EMPLOYEE WHERE ROWNUM BETWEEN 5 AND 10;

o  이럴 때 스토어드 뷰를 이용하는데 FROM절 내부 SELECT문에 별칭을 부여해 외부 SELECT문에서 해당 별칭으로 WHERE 조건을 작성한다.

SELECT * FROM (     SELECT ROWNUM AS RNUM, E.*     FROM (         SELECT EMP_NAME, SALARY         FROM EMPLOYEE         ORDER BY SALARY DESC         ) E ) WHERE RNUM BETWEEN 5 AND 10;

-  3중 SELECT문 사용으로 첫 SELECT문(제일 안쪽)은 데이터를 정렬시키고 두 번째 SELECT문은 정렬된 테이블을 받아 ROWNUM 및 별칭을 부여함으로써 ROWNUM으로 부여된 값을 고정시킨다. 마지막 세번째 SELECT문에서는 별칭을 이용해 두 번째 SELECT문을 호출하여 데이터를 필터링할 수 있게 된다.

-  스토어드 뷰 사용 및 가상으로 부여된 ROWNUM 컬럼에 별칭을 부여한다. 하나의 컬럼을 새롭게 만들어 중간 값 조회가 가능하게 된다. -> 상위 SELECT에서 사용할 수 있도록 한다.

 

 2. RANK() OVER

o  각 ROW에 순서를 부여하는 함수로 RANK() OVER(정렬기준)의 형식으로 사용한다.

o  정렬 기준에 따라 ROW를 정렬 후 순서를 부여한다.

SELECT * FROM(     SELECT EMP_NAME, SALARY,         RANK() OVER(ORDER BY SALARY DESC) AS 순위     FROM EMPLOYEE     ) WHERE 순위 BETWEEN 1 AND 3;

 

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

 

3. DENSE_RANK() OVER

o  중복 값이 있을 때 중복 값의 다음 순번을 이어서 출력한다.

o  RANK() OVER : 중복 값 번호 출력 후 순번 하나를 건너뛴다.

o  DENSE_RANK() OVER : 중복 값 번호 출력 후 다음 번호 이어서 출력한다.

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