오늘부터는 수업 핵심 키워드나 중요한 내용들 위주로 정리해나가려고 한다.
📝 목차
- 단일 행 서브쿼리
- 다중 행 서브쿼리
- 다중 행 서브쿼리
- 다중 열 서브쿼리
- 다중 행 다중 열 서브쿼리
- 상관 서브쿼리
- 스칼라 서브쿼리
- 인라인 뷰
- 기타
서브쿼리란 하나의 SQL 문 안에 포함된 또다른 SQL 문이다.
서브쿼리 유형에 따라 서브쿼리 앞에 붙는 연산자가 다르다.
1. 단일 행 서브쿼리
서브쿼리의 조회 결과 값의 개수가 한 개인 서브쿼리다.
서브 쿼리 앞에 비교 연산자를 사용한다.
-- 부서별 급여 합이 큰 부서의 부서명과 급여 합 조회
SELECT DEPT_TITLE, SUM(SALARY)
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
GROUP BY DEPT_TITLE
HAVING SUM(SALARY) >= (SELECT MAX(SUM(SALARY))
FROM EMPLOYEE
GROUP BY DEPT_CODE);참고로 GROUP BY 는 SELECT 절에 작성된 그룹 함수 이외의 컬럼을 모두 GROUP BY 절에 작성해야 그룹이 구성된다.
2. 다중 행 서브쿼리
서브쿼리의 조회 결과 값의 행이 여러 개인 서브쿼리다.
서브 쿼리 앞에 비교 연산자를 사용할 수 없다.
- IN 과 NOT IN : 여러 개의 결과값 중에서 한 개라도 일치하는 값이 있다면
혹은 없다면 - > ANY 와 < ANY : 여러개의 결과값 중에서 한 개라도 큰 또는 작은 경우
가장 작은 값보다 큰지 혹은 가장 큰 값 보다 작은지 비교 - > ALL 과 < ALL : 여러개의 결과값의 모든 값보다 큰 또는 작은 경우
가장 큰 값 보다 큰지 혹은 가장 작은 값 보다 작은지 비교 - EXISTS 와 NOT EXISTS : 값이 존재하는지 혹은 존재하지 않는지 비교
3. 다중 열 서브쿼리
서브쿼리의 조회 결과 컬럼의 개수가 여러 개인 서브쿼리다.
-- 퇴사한 여직원과 같은 부서와 직급에 해당하는 사원의 이름, 직급, 부서, 입사일 조회
SELECT EMP_NAME, JOB_NAME, DEPT_TITLE, HIRE_DATE
FROM EMPLOYEE
NATURAL JOIN JOB
JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
WHERE (DEPT_TITLE, JOB_NAME) = (SELECT DEPT_TITLE, JOB_NAME
FROM EMPLOYEE
NATURAL JOIN JOB
JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
WHERE ENT_YN = 'Y'
AND SUBSTR(EMP_NO, 8, 1) = '2');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);5. 상관 서브쿼리
상관 쿼리는 메인쿼리가 사용하는 테이블값을 서브쿼리가 이용해서 결과를 만든다.
메인쿼리의 테이블값이 변경되면 서브쿼리의 결과값도 바뀌게 되는 구조이다.
먼저 메인쿼리 한 행을 조회하고 해당 행이 서브쿼리의 조건을 충족하는지 확인하여 SELECT 를 진행한다.
-- 부서별 입사일이 가장 빠른 사원의 사번, 이름, 부서명, 직급명, 입사일을 조회하고
-- 입사일이 빠른 순으로 조회 (퇴사한 직원은 제외)
-- 1) 간단하지만 신뢰도가 낮은 결과
SELECT EMP_ID 사번, EMP_NAME 이름, NVL(DEPT_TITLE, '소속없음') 부서명, JOB_NAME 직급명, HIRE_DATE 입사일, DEPT_CODE
FROM EMPLOYEE MAIN
JOIN JOB USING(JOB_CODE)
LEFT JOIN DEPARTMENT ON (DEPT_ID = DEPT_CODE)
WHERE HIRE_DATE IN (SELECT MIN(HIRE_DATE) FROM EMPLOYEE SUB
WHERE ENT_YN = 'N'
GROUP BY DEPT_CODE) -- NULL 포함 X
ORDER BY HIRE_DATE;
-- 2) 어렵지만 신뢰도가 높은 결과
SELECT EMP_ID 사번, EMP_NAME 이름, NVL(DEPT_TITLE, '소속없음') 부서명, JOB_NAME 직급명, HIRE_DATE 입사일, DEPT_CODE
FROM EMPLOYEE MAIN
JOIN JOB USING(JOB_CODE)
LEFT JOIN DEPARTMENT ON (DEPT_ID = DEPT_CODE)
WHERE HIRE_DATE = (SELECT MIN(HIRE_DATE) FROM EMPLOYEE SUB
WHERE (ENT_YN = 'N' AND SUB.DEPT_CODE = MAIN.DEPT_CODE) -- 부서 6개 (NULL 포함 X)
OR (ENT_YN = 'N' AND MAIN.DEPT_CODE IS NULL AND SUB.DEPT_CODE IS NULL)) -- 부서 7개 (NULL 포함 O)
ORDER BY HIRE_DATE; 6. 스칼라 서브쿼리
상관쿼리이면서 결과 값이 한 개인 서브쿼리다. SQL 에서 단일 값을 스칼라라고 한다.
-- 모든 사원의 사번, 이름, 관리자사번, 관리자명을 조회
SELECT EMP_ID, EMP_NAME, MANAGER_ID,
NVL((SELECT EMP_NAME FROM EMPLOYEE SUB WHERE SUB.EMP_ID = MAIN.MANAGER_ID), '없음') 관리자명
FROM EMPLOYEE MAIN;7. 인라인 뷰
FROM 절에 서브쿼리 사용한 것으로 서브쿼리가 만든 RESULT SET 을 테이블 대신 사용한다.
인라인뷰를 활용한 TOP N 분석이 중요하고 많이 사용된다.
-- 전 직원 중 급여가 높은 상위 5명의 순위, 이름, 급여 조회
SELECT ROWNUM, EMP_NAME, SALARY
FROM (SELECT EMP_NAME, SALARY
FROM EMPLOYEE
ORDER BY SALARY DESC) -- 메인쿼리에 포함된 VIEW 생성 구문
WHERE ROWNUM <= 5;8. 기타
- WITH
서브쿼리에 이름을 붙여주고 사용 시 이름을 사용하게 한다.
인라인뷰로 사용될 서브쿼리에 주로 사용되며 실행 속도도 빨라진다는 장점이 있다.
- RANK ( ) OVER
동일한 순위 이후의 등수를 동일한 인원 수 만큼 건너뛰고 순위 계산한다.
예를 들어 공동 1위가 2명이면 다음 순위는 2위가 아니라 3위가 된다.
- DANSE_RANK ( ) OVER
동일한 순위 이후의 등수를 이후의 순위로 계산한다.
예를 들어 공동 1위가 2명이어도 다음 순위는 2위가 된다.
