[SQL] DQL : SUBQUERY

1. SUBQUERY

SELECT문에 포함된 또다른 SELECT문

• 메인쿼리가 실행되기 전 한번만 수행
• 반드시 소괄호 ()로 묶어야 함
• 주로 WHERE절에서 사용
• 서브쿼리와 비교할 항목은 반드시 서브쿼리에서 SELECT한 항목의 개수와 자료형을 일치시켜야

2. 유형

1) 단일 행 서브쿼리

한 컬럼에 한 행만 포함되어 있는 서브쿼리

-- 월급을 가장 적게 받는 직원 정보 조회
SELECT *
FROM EMPLOYEE
WHERE
	SALARY =
	(SELECT MIN(SALARY) FROM EMPLOYEE);

2) 다중 행 서브쿼리

한 컬럼에 여러 행이 포함되어 있는 서브쿼리
메인쿼리의 비교조건과 비교하는 데에 주로 사용

IN, NOT IN

메인쿼리의 비교조건이 서브쿼리의 결과값 중 한개라도 일치하는 경우 IN
한개도 일치하지 않는 경우 NOT IN

-- EMPLOYEE 테이블에서 관리자인 직원만 조회
SELECT EMP_NAME, '관리자' AS 구분
FROM EMPLOYEE
WHERE EMP_ID
	IN(SELECT DISTINCT MANAGER_ID FROM EMPLOYEE
	WHERE MANAGER_ID IS NOT NULL);

-- EMPLOYEE 테이블에서 사원인 직원만 조회
SELECT EMP_NAME, '사원' AS 구분
FROM EMPLOYEE
WHERE EMP_ID
	NOT IN (SELECT DISTINCT MANAGER_ID FROM EMPLOYEE
	WHERE MANAGER_ID IS NOT NULL);

ANY

메인쿼리의 비교조건이 서브쿼리의 결과값 중 한개라도 일치하는 경우

-- 대리 직급의 직원들 중 과장 직급의 최소 급여보다 많이 받는 직원 정보 조회
SELECT *
FROM EMPLOYEE JOIN JOB USING(JOB_CODE)
WHERE
	JOB_NAME = '대리'
	AND
    SALARY >= ANY (SELECT SALARY FROM EMPLOYEE JOIN JOB USING(JOB_CODE)
        WHERE JOB_NAME='과장');

ALL

메인쿼리의 비교조건이 서브쿼리의 모든 결과값과 일치하는 경우

-- - 차장 직급의 급여 중 가장 큰 값보다 많이 받는 과장 직급의 정보 조회
SELECT *
FROM EMPLOYEE JOIN JOB USING(JOB_CODE)
WHERE
	JOB_NAME='과장'
    AND
    SALARY > ALL (SELECT SALARY FROM EMPLOYEE JOIN JOB USING(JOB_CODE)
	WHERE JOB_NAME='차장');

3) 다중 열 서브쿼리

여러 컬럼을 포함하고 있는 서브쿼리
여러 컬럼을 한줄로 비교 가능
서브쿼리의 컬럼은 메인쿼리의 컬럼과 1:1 대응되어야 함

-- 특정 사원과 같은 부서, 같은 직급인 사원 정보 조회
SELECT *
FROM EMPLOYEE
WHERE (DEPT_CODE, JOB_CODE)
	IN (SELECT DEPT_CODE, JOB_CODE FROM EMPLOYEE
    WHERE SUBSTR(EMP_NAME='김ㅇㅇ');

4) 다중 열 다중 행 서브쿼리

여러 컬럼과 여러 행을 포함하고 있는 서브쿼리
서브쿼리의 컬럼은 메인쿼리의 컬럼과 1:1 대응되어야 함

-- 직급별로 가장 적은 월급을 받는 사원 정보 조회
SELECT *
FROM EMPLOYEE
WHERE
	(JOB_CODE, SALARY)
	IN (SELECT JOB_CODE, MIN(SALARY) FROM EMPLOYEE
	GROUP BY JOB_CODE);

3. 인라인 뷰 INLINE VIEW

FROM절에 서브쿼리를 사용한 것

ROWNUM

행에 일련번호를 붙여 가져올 행의 개수를 정하기 위해 사용

ROWNUM은 WHERE절에서 사용되기 때문에 ORDER BY에 의해 데이터가 정렬되기 전 이미 순서가 매겨진다.

-- 이름순으로 상위 5명 정보 조회
SELECT * FROM EMPLOYEE
WHERE ROWNUM<=5
ORDER BY EMP_NAME;

FROM절의 서브쿼리에서 데이터를 정렬하면 데이터가 정렬된 후 ROWNUM이 작동할 수 있다.

-- 이름순으로 상위 5명 정보 조회
SELECT *
FROM
    (SELECT *
    FROM EMPLOYEE
    ORDER BY EMP_NAME)
WHERE ROWNUM<=5
ORDER BY EMP_NAME;

WITH

서브쿼리에 이름을 붙여주는 것

• 인라인뷰로 사용할 시 서브쿼리의 이름으로 FROM절에 기술 가능
• 같은 서브쿼리가 여러번 사용될 시 코드의 간결성과 빠른 실행속도 보장

-- 이름 순으로 정렬한 서브쿼리 SUB_NAME
WITH SUB_NAME AS
	(SELECT * FROM EMPLOYEE
	ORDER BY SALARY DESC)
SELECT * FROM SUB_NAME
WHERE ROWNUM<=5;

RANK() OVER, DENSE_RANK() OVER

SELECT절에 하나의 컬럼처럼 사용되어 인자로 설정한 기준에 따라 행에 순위를 매겨주는 것

RANK() OVER

동순위 인원수와 상관없이 순차적으로 순위를 매김

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

DENSE_RANK() OVER

동순위 인원이 있을 경우 다음 등수를 인원수만큼 뒤로 보냄

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