Chapter 8. SubQuery

김승현·2021년 10월 12일
국비수업오라클(Oracle)
0

서브쿼리(SubQuery)란

  • 메인 쿼리가 서브쿼리를 포함하는 종속적인 관계

    • 알려지지 않은 조건에 근거한 값들을 검색하는 SELECT 문장을 작성하는데 유용.

  • 조건

    • 서브쿼리는 반드시 소괄호로 묶어야 한다. (SELECT ... FROM ... WHERE ... )
    • 서브쿼리는 연산자의 오른쪽에 위치 해야 한다.
    • WHERE 구문에서 사용되는 서브쿼리 내에서 order by 문법은 지원 하지 않는다.



단일행 서브쿼리

  • 서브쿼리의 조회 결과 값(행, 열)의 개수 1개

  • EX)

    • D1, D2 부서에 근무하는 사원들 중에서 기본 급여(SALARY)가
      D5 인 부서 직원들의 평균급여 보다 많은 사람들만
    • 부서번호(DEPT_CODE), 사원번호(EMP_ID), 사원명(EMP_NAME), 급여(SALARY)을 출력
SELECT DEPT_CODE AS "부서번호", 
EMP_ID AS "사원번호", 
EMP_NAME AS "사원명", 
SALARY AS "급여"
FROM EMPLOYEE
WHERE DEPT_CODE IN ('D1', 'D2') AND SALARY > (SELECT AVG(SALARY) 
                                               FROM EMPLOYEE
                                               WHERE DEPT_CODE='D5');



다중행 서브쿼리

  • 서브쿼리의 조회 결과 값(행)이 여러개 일때(열은 1개이고, 행이 여러개인 경우)
  • 다중행 서브쿼리 앞에는 일반 비교 연산자 사용이 불가하다.
  • 사용 가능 연산자
    • IN
      • 쿼리의 비교 조건이 쿼리 결과 중에서 하나라도 일치하는 것 (OR)

    • NOT IN
      • 쿼리의 비교 조건 결과 중에서 하나라도 일치하지 않는 것

    • ANY
      • 서브 쿼리의 결과 중에서 하나라도 참이면 참 (범위를 표현하는 OR 개념)
    구분의미
    > ANY최소값보다 크면
    >= ANY최소값보다 크거나 같으면
    < ANY최대값보다 작으면
    <= ANY최대값보다 작거나 같으면
    = ANYIN과 같은 효과
    != ANYNOT IN과 같은 효과

    • ALL
      • 서브 쿼리의 결과 중 모두 참이면 참 (ALL은 AND 개념)
    구분의미
    > ALL최대값보다 크면
    >= ALL최대값보다 크거나 같으면
    < ALL최소값보다 작으면
    <= ALL최소값보다 작거나 같으면
    = ALL결과가 1개면 같은! 여러개면 오류 발생
    != ALL결과가 1개면 같지 않은! 여러개면 오류 발생

    • EXISTS
      • 데이터가 있는지 없는지를 비교하기 위한 연산자
      • 데이터가 있으면 참, 없으면 거짓
      • Table의 양이 많은 경우 모두 찾기 보다 있는지 없는지 여부를 먼저 파악



EX) 다중행 서브쿼리 / IN

  • 차태연, 전지연 사원의 급여등급(emplyee테이블의 sal_level컬럼) 이 같은 사원의 직급명, 사원명을 출력 (차태연, 전지연 제외)
SELECT JOB_NAME AS "직급명" , EMP_NAME AS "사원명"
FROM EMPLOYEE
LEFT JOIN JOB USING (JOB_CODE)
WHERE SAL_LEVEL IN (SELECT  SAL_LEVEL 
      		    FROM EMPLOYEE
      		    WHERE EMP_NAME IN ('차태연','전지연')) 
AND EMP_NAME NOT IN  ('차태연','전지연');

EX) 다중행 서브쿼리/ NOT IN

  • 직급이 대표, 부사장이 아닌 모든 사원을 이름,부서명,직급코드를 출력하고 부서별로 출력.
SELECT EMP_NAME, DEPT_TITLE, JOB_CODE
FROM EMPLOYEE 
LEFT JOIN DEPARTMENT ON(DEPT_CODE=DEPT_ID)
WHERE JOB_CODE NOT IN (SELECT JOB_CODE 
			FROM JOB
			WHERE JOB_NAME IN ('대표', '부사장'));

EX) 다중행 서브쿼리/ ANY

  • 'D1' 또는 'D5' 부서코드를 가지고 있는 직원 들의 최대 급여보다 작은 모든 직원들의 이름, 급여, 부서코드를 출력.
  • ( 비교 대상은 'D1'과 'D5' 중 어디에 속하더라도 상관 없음)
SELECT EMP_NAME, SALARY, DEPT_CODE
FROM EMPLOYEE
WHERE SALARY < ANY ( SELECT SALARY 
		     FROM EMPLOYEE
		     WHERE DEPT_CODE IN ('D1','D5') );

EX) 다중행 서브쿼리/ ALL

  • 부서별 평균 급여를 조사하였을때, 평균 급여가 가장 낮은 부서의 급여보다 낮거나 같은 모든 사원들의 이름, 급여, 부서명을 출력하시오 
SELECT EMP_NAME, SALARY, DEPT_TITLE
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON (DEPT_CODE=DEPT_ID)
WHERE SALARY <= ALL (SELECT AVG(SALARY) 
		     FROM EMPLOYEE
		     GROUP BY DEPT_CODE);

EX) 다중행 서브쿼리/EXISTS

  • 우리 회사의 급여가 8백만원이 넘는 직원이 있다면 전체 직원의 급여 출력
SELECT *
FROM EMPLOYEE
WHERE EXISTS(SELECT 1 FROM EMPLOYEE WHERE SALARY>8000000) ;



다중열 서브쿼리

  • 서브쿼리의 조회 결과 컬럼의 개수가 여러 개 일 때 (행은 1개이고, 열이 여러개인 경우)
  • 작성법
    SELECT 속성1, 속성2...
    FROM TABLE
    WHERE (속성_A, 속성_B) IN (SELECT 속성_A, 속성_B FROM TABLE
    WHERE 조건);

EX) 다중열 서브쿼리

  • 사원ID가 205번인 직원의 부서코드 및 직책 코드가 같은 직원의 사번, 이름, 부서명, 직책명을 출력하여라.
  • 단, 205번 직원도 출력하여라.
SELECT EMP_ID AS "사번", EMP_NAME AS "이름", DEPT_TITLE AS "부서명", JOB_NAME AS "직책명"
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON(DEPT_CODE=DEPT_ID)
LEFT JOIN JOB USING (JOB_CODE)
WHERE (DEPT_CODE, JOB_CODE)IN (SELECT DEPT_CODE, JOB_CODE  
				FROM EMPLOYEE
				WHERE EMP_ID = 205);



다중행 다중열 서브쿼리

  • 서브쿼리의 조회 결과 컬럼의 개수와 행의 개수가 여러개 일 때
  • 사용 방법은 다중열 서브쿼리와 같음

EX)

  • 직급별 최소 급여를 받는 직원의 이름, 사번, 부서코드, 입사일, 연봉을 출력
 SELECT EMP_NAME, EMP_ID, DEPT_CODE, HIRE_DATE, SALARY *12
 FROM EMPLOYEE
 WHERE (JOB_CODE , SALARY) IN ( SELECT JOB_CODE , MIN(SALARY)
                                FROM EMPLOYEE
                                GROUP BY JOB_CODE);



인라인 뷰(INLINE-VIEW)

  • FROM 절에서 서브쿼리를 사용하는 것

  • TOP-N 분석시 자주 활용 됨

  • EX) 사용법 예시

    • SELECT * FROM (SELECT EMP_NAME, EMP_NO FROM EMPLOYEE);



TOP-N 분석

  • 컬럼에서 상위 N 개의 값 또는 하위 N개의 값을 요청할 때 사용
  • 인라인 뷰를 사용

EX)

  • 보너스를 받은 직원들 중에 보너스 금액이 가장 높은 2명의 이름, 보너스 금액 출력
  • 보너스 금액 금여*보너스율
SELECT ROWNUM, EMP_NAME, "보너스 금액" --  BONUS*SALARY 쓴다고 같은거 아님, 별칭 사용해야함
FROM(
    SELECT EMP_NAME, (SALARY*BONUS) AS "보너스 금액"
    FROM EMPLOYEE
    WHERE BONUS IS NOT NULL
    ORDER BY  "보너스 금액" DESC)  --  BONUS*SALARY 쓴다고 같은거 아님, 별칭 사용해야함
WHERE ROWNUM<=2;

EX)

  • 3~5위 출력
  • 인라인 뷰 두번 적용
SELECT *
FROM(
    SELECT ROWNUM AS "RN", EMP_NAME, "보너스 금액" 	-- ROWNUM 별칭 적용
    FROM(
        SELECT EMP_NAME, (SALARY*BONUS) AS "보너스 금액"
        FROM EMPLOYEE
        WHERE BONUS IS NOT NULL
        ORDER BY  "보너스 금액" DESC))  
WHERE "RN" BETWEEN 3 AND 5;		--  ROWNUM 별칭으로 조건 설정



WITH

  • 서브쿼리에 이름을 붙여주고, 인라인뷰로 사용시 서브쿼리의 이름으로 FROM 절에 기술할 수 있음
  • 장점
    • 같은 서브쿼리가 여러번 사용될 경우 중복 작성 방지
    • 가독성과 성능면에서 좋아짐(실행속도 향상)
  • 작성법
    WITH 가상의테이블명 AS (서브쿼리)

    SELECT 컬럼명
    FROM 가상의테이블명 ;

EX)

WITH NEW_DATA AS (SELECT ROWNUM AS "RN", EMP_NAME, "보너스 금액" 
    		  FROM(
                      SELECT EMP_NAME, (SALARY*BONUS) AS "보너스 금액"
                      FROM EMPLOYEE
                      WHERE BONUS IS NOT NULL
                      ORDER BY  "보너스 금액" DESC))
        
SELECT *
FROM NEW_DATA
WHERE "RN" BETWEEN 3 AND 5;



RANK() OVER / DENSE_RANK() OVER / ROW_NUMBER() OVER

구분의미
RANK() OVER중복되는 순위는 동일 순위로 출력하고, 다음 순위는 그만큼 건너뛰고 반환
ex) 1,2,3,4,5,6,6,6,9,10
DENSE_RANK() OVER중복되는 순위는 동일 순위로 출력하고, 다음 순위 순차적으로 반환
ex) 1,2,3,4,5,6,6,6,7,8,9,10
ROW_NUMBER() OVER중복과 관계 없이 무조건 순서대로 반환 /동일 값이라면 출력되는 순서가 우선순위로 표기
ex) 1,2,3,4,5,6,7,8,9,10

EX) RANK() OVER

SELECT *
FROM(
    SELECT 
    RANK() OVER(ORDER BY SALARY DESC) AS "RANK", 
    DENSE_RANK() OVER(ORDER BY SALARY DESC) AS "DENSE_RANK",
    ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS "ROW_NUMBER",
    EMP_NAME, SALARY
    FROM EMPLOYEE);
profile
김승현
개발자로 매일 한 걸음
이전 포스트

Chapter 7. Set Operator

다음 포스트

Chapter 9. DDL (CREATE)

0개의 댓글