상[호연]관 서브쿼리

상관 쿼리는 메인쿼리가 사용하는 테이블값을 서브쿼리가 이용해서 결과를 만듦
메인쿼리의 테이블값이 변경되면 서브쿼리의 결과값도 바뀌게 되는 구조임!
상관쿼리는 먼저 메인쿼리를 한 행 조회하고
해당 행이 서브쿼리의 조건을 충족하는지 확인하여 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 MAIN
JOIN JOB USING(JOB_CODE)
LEFT JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
WHERE ENT_YN = 'N'
AND HIRE_DATE = (SELECT MIN(HIRE_DATE)
FROM EMPLOYEE SUB
WHERE SUB.DEPT_CODE = MAIN.DEPT_CODE
)
ORDER BY HIRE_DATE;

1) MAIN 의 1행의 DEPT_CODE를 SUB 대입
2) SUB를 수행
3) SUB의 결과를 이용해서 MAIN의 조건절 수행

---

스칼라 서브쿼리

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, '없음'),
(SELECT EMP_NAME FROM EMPLOYEE SUB
WHERE SUB.EMP_ID = MAIN.MANAGER_ID
)
FROM EMPLOYEE MAIN;

SELECT EMP_ID, EMP_NAME, MANAGER_ID,
NVL((SELECT EMP_NAME
FROM EMPLOYEE SUB
WHERE SUB.EMP_ID = MAIN.MANAGER_ID
), '없음') 관리자명
FROM EMPLOYEE MAIN;

---

인라인 뷰(INLINE-VIEW)

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

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

-- 인라인뷰를 활용한 TOP-N 분석
-- 전직원 중 급여가 높은 상위 5명의
-- 순위, 이름, 급여 조회

-- ROWNUM 컬럼 : 행 번호를 나타내는 가상 컬럼
SELECT ROWNUM, EMP_NAME, SALARY
FROM EMPLOYEE
WHERE ROWNUM <= 5
ORDER BY SALARY DESC;
-- SELECT 해석 순서 때문에
-- 급여 상위 5명이 아니라
-- 조회 순서 상위 5명의 급여 순위 조회가 됨

--> 인라인뷰를 이용해서 해결 가능

-- 1) 이름, 급여를 급여 내림차순으로 조회한 결과를
-- 인라인뷰로 사용
--> FROM 절에 작성되기 때문에 해석 1순위

-- 2) 메인쿼리 조회 시
-- ROWNUM을 5 이하 까지만 조회

SELECT ROWNUM, EMP_NAME, SALARY
FROM (
SELECT EMP_NAME, SALARY
FROM EMPLOYEE
ORDER BY SALARY DESC )
WHERE ROWNUM <= 5;

---

RANK() OVER / DENSE_RANK() OVER

RANK() OVER(정렬)

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

-- 사원별 급여 순위
-- 1) ROWNUM
SELECT ROWNUM, EMP_NAME, SALARY
FROM ( SELECT EMP_NAME, SALARY
FROM EMPLOYEE
ORDER BY SALARY DESC
);

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

• 19 전형돈 2000000
• 19 윤은해 2000000
• 21 박나라 1800000
  

DENSE_RANK() OVER(정렬순서)

동일한 순위 이후의 등수를 이후의 순위로 계산

-- EX) 공동 1위가 2명이어도 다음순위는 2위
SELECT DENSE_RANK() OVER(ORDER BY SALARY DESC) 순위,
EMP_NAME, SALARY
FROM EMPLOYEE;

/* 19 전형돈 2000000

• 19 윤은해 2000000
• 20 박나라 1800000
• */

---

WITH

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

-- 전 직원의 급여 순위
-- 순위, 이름, 급여 조회

WITH TOP_SAL AS (SELECT EMP_NAME, SALARY
FROM EMPLOYEE
ORDER BY SALARY DESC
)
SELECT ROWNUM, EMP_NAME, SALARY
FROM TOP_SAL
WHERE ROWNUM <= 10;