📘 SUBQUERY

• 하나의 SQL문 안에 포함된 또다른 SQL문
• 메인쿼리(기존쿼리)를 위해 보조 역할을 하는 쿼리문
• SELECT, FROM, WHERE, HAVGIN 절에서 사용가능

📜 SUBQUERY 유형

• 단일행 (+ 단일열) 서브쿼리 == 서브쿼리의 조회 결과 값의 개수가 1개일 때
• 다중행 (+ 단일열) 서브쿼리 == 서브쿼리의 조회 결과 값의 개수가 여러개일 때
• 다중열 서브쿼리 == 서브쿼리의 SELECT 절에 자열된 항목수가 여러개 일 때
• 다중행 다중열 서브쿼리 == 조회 결과 행 수와 열 수가 여러개일 때
• 상관 서브쿼리 == 서브쿼리가 만든 결과 값을 메인 쿼리가 비교 연산할 때
  메인 쿼리 테이블의 값이 변경되면 서브쿼리의 결과값도 바뀌는 서브쿼리
• 스칼라 서브쿼리 == 상관 쿼리이면서 결과 값이 하나인 서브쿼리

!! 서브쿼리 유형에 따라 서브쿼리 앞에 붙은 연산자가 다르다 !!

---

✏ 단일행 서브쿼리 (SINGLE ROW SUBQUERY)

서브쿼리의 조회 결과 값의 개수가 1개인 서브쿼리이며 단일행 서브쿼리 앞에는 비교 연산자를 사용한다.
ex) <, >, <=, >=, =, !=/^=/<>

-- 전 직원의 급여 평균보다 많은 급여를 받는 직원의 
-- 이름, 직급, 부서, 급여를 직급 순으로 정렬하여 조회
SELECT EMP_NAME, JOB_CODE, DEPT_CODE, SALARY FROM EMPLOYEE
WHERE SALARY >= (SELECT AVG(SALARY) FROM EMPLOYEE)
ORDER BY JOB_CODE;

---

✏ 다중행 서브쿼리 (MULTI ROW SUBQUERY)

!! 다중행 서브쿼리 앞에는 일반 비교연산자 사용 x !!

• IN / NOT IN : 여러 개의 결과값 중에서 한 개라도 일치하는 값이 있다면 혹은 없다면 이라는 의미(가장 많이 사용!)
• ANY, < ANY : 여러개의 결과값 중에서 한개라도 큰 / 작은 경우
  가장 작은 값보다 큰가? / 가장 큰 값 보다 작은가?
• ALL, < ALL : 여러개의 결과값의 모든 값보다 큰 / 작은 경우 가장 큰 값 보다 큰가? / 가장 작은 값 보다 작은가?
• EXISTS / NOT EXISTS : 값이 존재하는가? / 존재하지 않는가?

-- 부서별 최고 급여를 받는 직원의 
-- 이름, 직급, 부서, 급여를 부서 순으로 정렬하여 조회
SELECT EMP_NAME, JOB_CODE, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY IN (SELECT MAX(SALARY) FROM EMPLOYEE
                 GROUP BY DEPT_CODE)
ORDER BY DEPT_CODE;

---

✏ 다중열 서브쿼리 (단일행 = 결과값은 한 행)

!! 서브쿼리 SELECT 절에 나열된 컬럼 수가 여러개 일 때 !!

-- 퇴사한 여직원과 같은 부서, 같은 직급인 직원 조회 (다중 열 서브쿼리)
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 JOB_CODE
                FROM EMPLOYEE
                WHERE SUBSTR(EMP_NO, 8, 1) = 2
                AND ENT_YN = 'Y');                

---

✏ 다중행 다중열 서브쿼리

서브쿼리 조회 결과 행 수와 열 수가 여러개 일 때

-- 본인 직급의 평균 급여를 받고 있는 직원의
-- 사번, 이름, 직급, 급여를 조회하세요
-- 단, 급여와 급여 평균은 만원단위로 계산하세요 TRUNC(컬럼명, -4)  
SELECT EMP_ID, EMP_NAME, JOB_CODE, SALARY
FROM EMPLOYEE
WHERE (JOB_CODE, SALARY) IN (SELECT JOB_CODE, TRUNC(AVG(SALARY), -4)
                             FROM EMPLOYEE
                             GROUP BY JOB_CODE);

---

✏ 상[호연]관 서브쿼리 (메인쿼리 1행씩 우선 해석, 서브쿼리 나중에 해석)

상관 쿼리는 메인쿼리가 사용하는 테이블값을 서브쿼리가 이용해서 결과를 만들고
메인쿼리의 테이블값이 변경되면 서브쿼리의 결과값도 바뀌게 되는 구조이다.

상관쿼리는 먼저 메인쿼리 한 행을 조회하고
해당 행이 서브쿼리의 조건을 충족하는지 확인하여 SELECT를 진행함

-- 부서별 입사일이 가장 빠른 사원의
-- 사번, 이름, 부서명(NULL이면 '소속없음'), 직급명, 입사일을 조회하고
-- 입사일이 빠른 순으로 조회하세요
-- 단, 퇴사한 직원은 제외하고 조회하세요
SELECT EMP_ID, EMP_NAME, NVL(DEPT_TITLE, '소속없음'), JOB_NAME, HIRE_DATE
FROM EMPLOYEE MAIN
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
JOIN JOB USING(JOB_CODE)
WHERE HIRE_DATE = (SELECT MIN(HIRE_DATE) FROM EMPLOYEE SUB
                   WHERE SUB.DEPT_CODE = MAIN.DEPT_CODE)
ORDER BY HIRE_DATE;

---

✏ 스칼라 서브쿼리

SELECT절에 사용되는 서브쿼리 결과로 1행만 반환하고 SQL에서 단일 값을 가르켜 '스칼라'라고 함

-- 각 직원들이 속한 직급의 급여 평균 조회
SELECT EMP_NAME, SALARY, (SELECT FLOOR(AVG(SALARY)) FROM EMPLOYEE SUB
                          WHERE SUB.JOB_CODE = MAIN.JOB_CODE) 평균
FROM EMPLOYEE MAIN;

---

✏ 인라인 뷰(INLINE-VIEW)

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

-- 급여 평균이 3위 안에 드는 부서의 부서코드와 부서명, 평균급여를 조회
SELECT * 
FROM (SELECT DEPT_CODE, DEPT_TITLE, FLOOR(AVG(SALARY)) 평균급여
      FROM EMPLOYEE
      LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
      GROUP BY DEPT_CODE, DEPT_TITLE
      ORDER BY 3 DESC)
      
WHERE ROWNUM <= 3;

---

✏ WITH

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

-- 전 직원의 급여 순위 
-- 순위, 이름, 급여 조회
SELECT ROWNUM, EMP_NAME, SALARY
FROM (SELECT EMP_NAME, SALARY
      FROM EMPLOYEE
      ORDER BY SALARY DESC);

---

✏ RANK() OVER / DENSE_RANK() OVER

RANK() OVER

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

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

DENSE_RANK() OVER

-> 동일한 순위 이후의 등수를 이후의 순위로 계산한다.
EX) 공동 1위가 2명이어도 다음 순위는 2위

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