SUBQUERY(서브쿼리)

SUBQUERY ?

하나의 주된 SQL(SELECT, CREATE, INSERT, UPDATE, ...)안에 포함된 또 하나의 SELECT문
메인 SQL문을 위해 보조 역할을 하는 SELECT문- 주로 조건절에서 사용

간단한 서브쿼리 예시 :

• 홍길동 사원의 부서코드를 조회

SELECT DEPT_CODE
FROM EMPLOYEE
WHERE EMP_NAME='홍길동';

• D1 부서에 해당하는 사원들을 조회

SELECT EMP_NAME, DEPT_CODE
FROM EMPLOYEE
WHERE DEPT_CODE='D1';

• 두 쿼리를 하나로 합치기

SELECT DEPT_CODE
FROM EMPLOYEE
WHERE EMP_NAME=(SELECT EMP_NAME, DEPT_CODE
				FROM EMPLOYEE
				WHERE DEPT_CODE='D1');

서브쿼리 구분

서브쿼리를 수행한 결과값이 몇 행 몇 열이냐에 따라 분류

• 단일행(단일열) 서브쿼리 : 서브쿼리를 수행한 결과값이 오로지 1개

• 다중행(단일열) 서브쿼리 : 서브쿼리를 수행한 결과값이 여러 행

• (단일행)다중열 서브쿼리 : 서브쿼리를 수행한 결과값이 여러 열

• 다중행 다중열 서브쿼리 : 서브쿼리를 수행한 결과값이 여러 행 여러 열

• 서브쿼리를 수행한 결과가 몇 행 몇 열이냐에 따라 사용가능한 연산자가 달라진다.

단일 행, 단일 열 서브쿼리

서브쿼리의 조회 결과값이 오로지 1개일 때 일반 연산자 사용 가능
(=, !=, >=, <=, <, >)

• 예시 :

SELECT EMP_ID,EMP_NAME,DEPT_TITLE,SALARY
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON (DEPT_CODE=DEPT_ID)
WHERE SALARY > (SELECT SALARY
                FROM EMPLOYEE
                WHERE EMP_NAME='노옹철');

다중행 서브쿼리

서브쿼리의 조회 결과가 여러 행일 경우

• IN (10, 20, 30) 서브쿼리 : 여러 개의 결과값 중에서 하나라도 일치하는 것이 있다면 / NOT IN 없다면

• < ANY (10, 20, 30) 서브쿼리 : 여러 개의 결과값 중에서 하나라도 작은 경우, 즉 여러 개의 결과값 중에서 조건에 가장 큰 값보다 작을 경우

• > ANY (10, 20, 30) 서브쿼리 : 여러 개의 결과값 중에서 하나라도 클 경우, 즉 여러 개의 결과값 중에서 조건에 가장 작은 값보다 클 경우

• > ALL : 여러 개의 결과값 중의 모든 값보다 클 경우

• < ALL : 여러 개의 결과값 중에 모든 값보다 작을 경우

• 예시 :

SELECT EMP_ID,EMP_NAME,JOB_NAME,SALARY
FROM EMPLOYEE E,JOB J
WHERE E.JOB_CODE=J.JOB_CODE
AND SALARY > ANY (SELECT SALARY
                FROM EMPLOYEE E,JOB J
                WHERE E.JOB_CODE=J.JOB_CODE
                AND JOB_NAME = '과장')
AND JOB_NAME = '대리';

단일행 다중열 서브쿼리

서브쿼리 조회 결과가 값은 한 행이지만, 나열된 컬럼의 개수가 여러개일 경우

• 단일행 서브쿼리 :

SELECT EMP_NAME,DEPT_CODE,JOB_CODE,HIRE_DATE
FROM EMPLOYEE
WHERE DEPT_CODE = (SELECT DEPT_CODE 
                   FROM EMPLOYEE
                   WHERE EMP_NAME='하이유')
AND JOB_CODE = (SELECT JOB_CODE
                FROM EMPLOYEE
                WHERE EMP_NAME='하이유');

• 다중열 서브쿼리 :

SELECT EMP_NAME,DEPT_CODE,JOB_CODE,HIRE_DATE
FROM EMPLOYEE
WHERE (DEPT_CODE,JOB_CODE) = (SELECT DEPT_CODE,JOB_CODE
                              FROM EMPLOYEE
                              WHERE EMP_NAME='하이유');

다중행 다중열 서브쿼리

서브쿼리 조회 결과가 여러 행, 여러 열일 경우

• 조건에 만족하는 사원 찾기(OR사용)

SELECT EMP_ID,EMP_NAME,JOB_CODE,SALARY
FROM EMPLOYEE
WHERE (JOB_CODE,SALARY) = ('J2',3700000)
OR (JOB_CODE,SALARY) = ('J7',1380000)
OR (JOB_CODE,SALARY) = ('J3',3400000)
OR (JOB_CODE,SALARY) = ('J6',2000000)
OR (JOB_CODE,SALARY) = ('J5',2200000)
OR (JOB_CODE,SALARY) = ('J1',8000000)
OR (JOB_CODE,SALARY) = ('J4',1550000);

• 서브쿼리로 바꿔보기

SELECT EMP_ID,EMP_NAME,JOB_CODE,SALARY
FROM EMPLOYEE
WHERE (JOB_CODE,SALARY) IN (SELECT JOB_CODE,MIN(SALARY)
                            FROM EMPLOYEE
                            GROUP BY JOB_CODE);

인라인 뷰

FROM절에 서브쿼리를 제시하는 것
서브쿼리 수행 결과를 테이블 대신 사용한다.

• 변경 전 :

SELECT EMP_ID,EMP_NAME,(SALARY+(SALARY * NVL(BONUS,0)))*12 "보너스포함연봉",DEPT_CODE
FROM EMPLOYEE
WHERE (SALARY+(SALARY * NVL(BONUS,0)))*12 >= 30000000;

• 인라인뷰 사용하여 사원명만 골라내기

SELECT EMP_NAME
FROM (SELECT EMP_ID,EMP_NAME,(SALARY+(SALARY * NVL(BONUS,0)))*12 "보너스포함연봉",DEPT_CODE
      FROM EMPLOYEE
      WHERE (SALARY+(SALARY * NVL(BONUS,0)))*12 >= 30000000);

• 인라인뷰에서 사용한 별칭을 메인 SELECT절에서 컬럼명으로 이용 :

SELECT 보너스포함연봉
FROM (SELECT EMP_ID,EMP_NAME,(SALARY+(SALARY * NVL(BONUS,0)))*12 "보너스포함연봉",DEPT_CODE
      FROM EMPLOYEE
      WHERE (SALARY+(SALARY * NVL(BONUS,0)))*12 >= 30000000);

TOP-N 분석(ROWNUM)

데이터베이스 상의 자료 중 최상위 N개의 자료를 보기위한 기능
ROWNUM : 오라클에서 제공하는 컬럼, 조회된 순서대로 1번부터 순번 부여

SELECT ROWNUM 순위,EMP_NAME 사원명,SALARY 급여
FROM (SELECT *
      FROM EMPLOYEE
      ORDER BY SALARY DESC)
WHERE ROWNUM <=5;

인라인뷰로 넣는 이유는 FROM절에서 순서가 정리되어야 한다.

순위를 매기는 함수

• RANK() OVER(정렬 기준) :

  공동 1위가 3명이라 한다면 그 다음 순위는 4위

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

• DENSE_RANK() OVER(정렬 기준) :

  공동 1위가 3명이라 해도 그 다음 순서는 2위

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

• 순위에 제한을 둬서 조회

SELECT EMP_NAME,SALARY,RANK() OVER(ORDER BY SALARY DESC) "순위"
FROM EMPLOYEE
WHERE RANK() OVER(ORDER BY SALARY DESC) <=5;