SUBQUERY
- SELECT 문장 안에 포함된 또 다른 SELECT 문장으로 메인 쿼리가 실행되기 전 한 번만 실행됨
- 비교 연산자의 오른쪽에 기술해야 하며 반드시 괄호로 묶어야 함
- 서브쿼리와 비교할 항목은 반드시 서브쿼리의 SELECT한 항목의 개수와 자료형을 일치시켜야 함
- 단일행 서브쿼리
서브쿼리의 조회 결과 값의 개수가 1개인 서브쿼리- 다중행 서브쿼리
서브쿼리의 조회 결과 값의 행이 여러 개인 서브쿼리- 다중열 서브쿼리
서브쿼리의 조회 결과 컬럼의 개수가 여러 개인 서브쿼리- 다중행 다중열 서브쿼리
서브쿼리의 조회 결과 컬럼의 개수와 행의 개수가 여러 개인 서브쿼리- 상(호연)관 서브쿼리
서브쿼리가 만든 결과 값을 메인 쿼리가 비교 연산할 때
메인 쿼리 테이블의 값이 변경되면 서브쿼리의 결과 값도 바뀌는 서브쿼리- 스칼라 서브쿼리
상관쿼리이면서 결과 값이 한 개인 서브쿼리
단일 행 서브쿼리(SINGLE ROW SUBQUERY)
- 서브쿼리의 조회 결과값의 개수가 1개일 때
단일행 서브쿼리 앞에는 일반 연산자 사용
( < , >, <=, >=, =, !=/<>/^= (서브쿼리) )
EX) 전 직원의 급여 평균보다 많은(초과) 급여를 받는 직원의
이름, 직급, 부서명, 급여를 직급 순으로 정렬하여 조회
SELECT EMP_NAME, JOB_NAME, DEPT_TITLE, SALARY FROM EMPLOYEE
JOIN JOB USING (JOB_CODE)
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
WHERE SALARY > (SELECT AVG(SALARY) FROM EMPLOYEE)
ORDER BY JOB_CODE;다중행 서브쿼리 (MULTI ROW SUBQUARY)
- 서브쿼리의 조회 결과 값의 개수가 여러행일 때
다중행 서브쿼리 앞에는 일반 비교연산자 사용 X
IN/NOT IN: 여러 개의 결과값 중에서 한 개라도 일치하는 값이 있다면
혹은 없다면 이라는 의미 ( 가장 많이 사용! )> ANY,< ANY: 여러 개의 결과 값 중에서 한 개라도 큰 / 작은 경우
가장 작은 값 보다 큰가 ? / 가장 큰 값 보다 작은가?> ALL,< ALL: 여러 개의 결과 값의 모든 값 보다 큰/ 작은 경우
가장 큰 값 보다 큰가? / 가장 작은 값 보다 작은가?EXISTS/NOT EXISTS: 값이 존재하는가? / 존재하지 않는가?
EX) 부서별 최고 급여를 받는 직원의 이름, 직급, 부서, 급여를 부서 순으로 정렬하여 조회
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절에 나열된 컬럼수가 여러 개일 때
EX) 퇴사한 여직원과 같은 부서, 같은 직급에 해당하는
사원의 이름, 직급, 부서, 입사일을 조회
SELECT EMP_NAME, JOB_CODE, DEPT_CODE, HIRE_DATE FROM EMPLOYEE
WHERE (DEPT_CODE, JOB_CODE) = (SELECT DEPT_CODE, JOB_CODE FROM EMPLOYEE
WHERE ENT_YN = 'Y'
AND SUBSTR(EMP_NO, 8,1) ='2');
다중행 다중열 서브쿼리
- 서브쿼리의 조회 결과 행 수와 열 수가 여러 개일 때
EX) 본인 직급의 평균 급여를 받고 있는 직원의 사번, 이름, 직급, 급여를 조회
단, 급여와 급여 평균은 만원단위로 계산 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);상(호연)관 서브쿼리
- 서브쿼리가 만든 결과 값을 메인 쿼리가 비교 연산할 때
메인 쿼리 테이블의 값이 변경되면 서브쿼리의 결과 값도 바뀌는 서브쿼리
EX) 관리자가 있는 사원들 중 관리자의 사번이 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절 EX)
모든 사원의 사번, 이름, 관리자 사번, 관리자 명 조회
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;인라인 뷰 (INLINE-VIEW)
- FROM절에 서브쿼리 사용한 것
WITH
- 서브쿼리에 이름을 붙여주고 인라인 뷰로 사용 시 서브쿼리의 이름으로 FROM절에 기술 가능
- 같은 서브쿼리가 여러 번 사용될 경우 중복 작성을 피할 수 있고 실행속도도 빨라진다는 장점이 있음
RANK() OVER
- 동일한 순위 이후의 등수를 동일한 인원 수만큼 건너뛰고 순위 계산
EX) 공동 1위가 2명이면 다음 순위는 2위가 아니라 3위
DENSE_RANK() OVER
- 동일한 순위 이후의 등수를 동일한 인원 수를 건너뛰지 않고 순위 계산
EX) 공동 1위가 2명이어도 다음 순위는 2위
