[Oracle] SQL문법 4. SUBQUERY

서브쿼리 (SUBQUERY)

서브쿼리란

하나의 주된 SQL(SELECT, CREATE, UPDATE, INSERT) 안에 포함된 또 하나의 SQL문
=> SELECT 문장 안에 포함된 또다른 SELECT 문장이다
전체 SQL문장을 메인 쿼리라고 하며, 메인 쿼리에 종속된 내부 쿼리를 서브 쿼리라고 한다
메인 쿼리가 실행되기 전 한 번만실행되며 반드시 괄호로 묶어야 한다
서브쿼리와 비교할 항목은 반드시 서브쿼리의 SELECT한 항목의 개수와 자료형을 일치시켜야 한다
메인 SQL문을 위해서 보조 역할을 하는 SELECT문이다
=> 서브쿼리의 결과를 메인 쿼리로 전달하여 전체적인 쿼리를 수행하게 된다
다만 서브 쿼리가 많으면 많을수록 속도나 복잡도에서 성능이 별로 좋지 않음
=> JOIN으로 처리가 가능하다면 JOIN을 사용하는 것을 권장한다

[ 결과에 따른 구분 ]

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

단일행 (단일열) 서브쿼리 : 서브쿼리를 수행한 결과값이 오직 1개일 경우
=> 한칸의 컬럼값으로 출력될때
다중행 (단일열) 서브쿼리 : 서브쿼리를 수행한 결과값이 여러행일 경우
(단일행) 다중열 서브쿼리 : 서브쿼리를 수행한 결과값이 여러 열일 경우
다중행 다중열 서브쿼리 : 서브쿼리를 수행한 결과값이 여러행 여러열일 경우

=> 서브 쿼리를 수행한 결과가 몇행 몇열인가에 따라 사용가능한 연산자가 달라진다

단일행 (SINGEL ROW SUBQUERY)

서브쿼리의 조회 결과값이 오직 1개일 경우
일반 연산자(=, !=, >, <, >=, <= …) 사용이 가능하다

--> 부서별 급여 합이 가장 큰 부서 하나만을 조회하는 예시
-- (DEPT = 부서, SALARY = 급여)

-- 1) 부서별 급여 합을 구하는 쿼리
SELECT DEPT_CODE, SUM(SALARY)
FROM EMPLOYEE
GROUP BY DEPT_CODE;

--2) 부서별 최고 급여합을 구하는 쿼리 (결과값 1개)
SELECT MAX(SUM(SALARY))
FROM EMPLOYEE
GROUP BY DEPT_CODE;

-- 3)  1 + 2 : 위의 두 쿼리문을 합친다
SELECT DEPT_CODE , SUM(SALARY)
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON DEPT_CODE = DEPT_ID
GROUP BY DEPT_CODE
HAVING SUM(SALARY) = ( SELECT MAX(SUM(SALARY))
                       FROM EMPLOYEE
                       GROUP BY DEPT_CODE);

다중행 (MULTI ROW SUBQUERY)

서브쿼리의 조회 결과값이 여러행일 경우
다중행 서브쿼리 앞에는 일반 단일행 연산자는 사용이 불가하다
=> 다중행 연산자(IN, NOT IN, ANY, ALL, EXISTS)만 사용 가능하다
사용 가능 연산자
- 이곳에서는 연산자들의 인자로 서브쿼리를 넣어 사용한단
- IN( ) : 여러개의 결과값중 하나라도 일치하는 것이 있는지 확인
- NOT IN( ) : 여러개의 결과값중 하나라도 일치하는 것이 없는지 확인
- > ANY( ) : 여러개의 결과값중에서 하나라도 클 경우 => 결과값들 중 가장 작은 값보다 클 경우
- < ANY( ) : 여러개의 결과값 중에서 하나라도 작을 경우 => 결과값들 중 가장 큰 값보다 작을 경우
- > ALL( ) : 여러개의 결과값의 모든 값보다 클 경우
  => 결과값들 중 가장 큰값보다 클 경우
- < ALL( ) : 여러개의 결과값의 모든 값보다 작을 경우
  => 결과값들 중 가장 작은값보다 작은경우
- EXISTS( ) : 데이터가 존재하는지 여부를 먼저 따져, 존재하는 값들만 결과로 출력
- NOT EXISTS( ) : 데이터가 존재하는지 여부를 먼저 따져, 존재하지 않는 값들만 결과로 출력
위의 연산자들을 가지고 연산중 NULL값을 가지고 있는 행을 연산하게 되었을 때
=> SQL에서는 NULL값을 가지고 동등비교를 수행할 수 없기 때문에 해당 행간의 비교는 결과값에 포함되지 않는다 => 위의 연산자 안에서 NVL()로 처리를 해주어야 NULL값을 포함한 결과를 도출해낼 수 있다

-- 부서별 최고 급여를 받는 직원의 정보를 조회하는 예시
-- DEPT_CODE - 부서코드, SALARY - 급여
SELECT EMP_NAME, JOB_CODE, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY IN ( SELECT MAX(SALARY)
                  FROM EMPLOYEE
                  GROUP BY DEPT_CODE)
ORDER BY 3;

다중열

서브쿼리 조회 결과의 값은 한 행 이지만 나열된 컬럼의 갯수가 여러개인 경우
=> 서브쿼리의 SELECT문에서 조회하는 컬럼의 갯수가 2개 이상인 경우
비교할 값의 순서를 맞추는 것이 중요하게 작용한다
=> ex) (비교대상칼럼 1, 비교대상컬럼 2) = (비교할값 1, 비교할값 2)
=> 여기서 비교할 값들은 서브쿼리로 조회되는 결과 값들이다
=> 비교할 값에는 ‘D5’, ‘J5’와 같이 리터럴한 값은 넣을 수 없고 서브쿼리로 제시해야 한다

-- 박나라 사원과 같은 직급코드, 같은 사수사번을 가진 사원들을 조회하는 예시
-- JOB_CODE - 직급코드 , MANAGER_ID - 사수사번
SELECT EMP_ID, EMP_NAME, JOB_CODE, MANAGER_ID
FROM EMPLOYEE
WHERE (JOB_CODE, MANAGER_ID) = (SELECT JOB_CODE, MANAGER_ID
                                FROM EMPLOYEE
                                WHERE EMP_NAME = '박나라');

다중행 다중열

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

-- 각 직급별 최소 급여를 받는 사원들을 조회하는 예시
-- JOB_CODE - 직급, SALARY - 급여
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);

-- NULL처리 예시
-- 각 직급별 최고 급여를 받는 사원들을 조회하는 예시
-- 직급이 없는 사원의 경우 '없음'이라는 직급으로 출력된다
SELECT EMP_ID 사번, EMP_NAME 이름, NVL(JOB_CODE, '없음') 직급코드, SALARY 급여
FROM EMPLOYEE
WHERE (NVL(JOB_CODE, '없음'), SALARY) IN( SELECT NVL(JOB_CODE, '없음') , MAX(SALARY)
                                          FROM EMPLOYEE
                                          GROUP BY JOB_CODE )
ORDER BY SALARY DESC;

상(호연)관 서브쿼리

상관 서브쿼리
- 서브쿼리가 만든 결과 값을 메인 쿼리가 비교 연산할 때, 메인 쿼리 테이블의 값이 변경되면 서브쿼리의 결과값도 바뀔경우
- 메인 쿼리의 특정 컬럼을 조인 조건으로 사용하는 서브 쿼리이다

-- 상관 서브쿼리 예시
-- 서브쿼리에서 메인쿼리의 테이블을 참조하고 있어,
-- 메인쿼리의 테이블의 값이 변경되면 서브쿼리의 결과값도 바뀐다
SELECT EMP_ID, EMP_NAME, DEPT_CODE, MANAGER_ID
FROM EMPLOYEE E
WHERE EXIST ( SELECT EMP_ID
              FROM EMPLOYEE M
              WHERE E.MANEGER_ID = M.EMP_ID )

[ 위치에 따른 구분 ]

서브 쿼리	사용 위치	설명
스칼라 서브 쿼리	SELECT 절	단일 행을 반환 (1개의 값)
인라인 뷰	FROM 절	View와 사용적인 측면에서 동일함 (임시 뷰, 임시 테이블)
중첩 서브 쿼리	WHERE 절, HAVING 절	다중 칼럼 또는 다중 행을 반환

스칼라 서브쿼리

스칼라 서브쿼리
- SELECT절에서 쓰이는, 결과값이 한개인(단일행) 서브쿼리를 말한다
  => 결과 데이터가 없을 경우 NULL값을 리턴한다 => 결과가 단일행으로 나오지 않는다면 에러 발생
- SQL에서는 단일 값을 스칼라(Scala)값이라고 표현한다
  => 스칼라 서브쿼리(Scala Subquery)는 결과값으로 1행만 반환하는 것을 말하는 것이다
- 단순한 JOIN을 대체할 목적으로 사용되는 경우가 많다
- 다만 풀 스캔을 유발하는 등 JOIN에 비해 알고리즘적 성능이 떨어지므로 JOIN 사용을 권장함

-- 스칼라 서브쿼리 예시
-- 아래와같이 대응되는 단 하나의 리턴값을 가지는 서브쿼리를 말한다
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절에 서브쿼리를 사용한 것이다
FROM절에서 다른 테이블들을 불러오는 것과 같이 서브쿼리의 결과를 테이블처럼 불러와 사용하는 개념이다
따라서 1회성으로 사용하는 서브쿼리이자 테이블처럼 취급하는 서브쿼리이다

-- 연봉이 3천만 이상인 사원들의 이름을 조회하는 예시
-- SALARY - 월급
SELECT EMP_NAME -- [, SALARY] -> 인라인 뷰에 SALARY 컬럼을 불러오는 구문이 없기 때문에 SALARY를 적을경우 에러가 발생한다
FROM (SELECT EMP_ID, EMP_NAME, SALARY *12 연봉, DEPT_CODE
      FROM EMPLOYEE
      WHERE SALARY * 12 >= 30000000);

- ROWNUM

인라인 뷰를 활용하여 주로 사용하는 컬럼중 하나이다
TOP-N 분석을 제공한다
=> 데이터베이스 상에 있는 자료 중 최상위 N개의 자료를 조회 하기 위해 사용
오라클에서 내부적으로 제공해주는 컬럼으로 조회된 순서대로 1번부터 순번을 부여해준다
=> 이때 정렬된 순서대로 순번을 부여하고 싶다면 ORDER BY로 이미 정렬한 테이블을 가지고 순번을 매겨주어야 한다
=> 정렬하지 않은 상태에서는 순번을 매겨도 의미가 없으므로 선 정렬 후 순번을 매겨주는 것이다 => 따라서 우선적으로 인라인 뷰로 ORDER BY 절을 불러준 후 사용하는 것

-- ROWNUM 활용 예시
-- 급여가 가장 높은 상위 5명을 조회
SELECT ROWNUM, EMP_NAME, SALARY
FROM ( SELECT * FROM
       EMPLOYEE
       ORDER BY SALARY DESC
)
WHERE ROWNUM <= 5;

- WITH

서브쿼리에 이름을 붙여주고 인라인 뷰로 사용 시 서브쿼리의 이름으로 FROM절에 기술이 가능하다
같은 서브쿼리가 여러 번 사용될 경우 중복 작성을 피할 수 있고 실행속도도 빨라진다는 장점이 있다

-- WHIT 활용 예시
WITH TOPN_SAL AS (SELECT EMP_NAME, SALARY
                  FROM EMPLOYEE
                  ORDER BY SALARY DESC)
SELECT ROWNUM, EMP_NAME, SALARY
FROM TOPN_SAL;

- RANK()

공통
- 윈도우 함수(WINDOW FUNCTION) 중 그룹 내 각 행의 순서를 할당하는 함수이다
  => 윈도우 함수는 행과 행간의 관계를 정의하기 위한 함수이다
- RANK() 함수는 ANSI/ISO SQL표준/ORACLE 에서 지원한다
- 파티션 내에서 첫번째 순위는 1부터 시작한다
- 동일한 값을 갖는 파티션 내의 각 행은 동일한 순위를 받는다
- 오직 SELECT 절에서만 사용 가능하다
- 정렬 기준 : ORDER BY절을 기준으로 삼는다, 다만 NULLFIRST/NULLLAST는 기술 불가하다
RANK() OVER(정렬기준)
- 같은 순위에 연결된 행의 수를 더하여 다음 행의 순위를 계산한다
  => 공동 1위가 3명이라고 한다면 그 다음순위는 4위가 된다
DENSE_RANK() OVER(정렬기준)
- 동일한 순위가 여러개 있어도 다음 순위는 중복 순위와 상관없이 순차적으로 할당한다
  => 공동 1위가 3명이라고 한다면 그다음 순위는 2위가 된다

-- RANK 함수 활용 예시

-- 1) RANK 함수로 순위를 매기고 정렬까지 완료
-- 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;

-- 2) 인라인 뷰로 변환
SELECT *
FROM (SELECT RANK() OVER(ORDER BY SALARY DESC) 순위, EMP_NAME, SALARY
        FROM EMPLOYEE)
WHERE 순위 <= 5;

Gom's

[Oracle] SQL문법 4. SUBQUERY

서브쿼리 (SUBQUERY)

서브쿼리란

[ 결과에 따른 구분 ]

단일행 (SINGEL ROW SUBQUERY)

다중행 (MULTI ROW SUBQUERY)

다중열

다중행 다중열

상(호연)관 서브쿼리

[ 위치에 따른 구분 ]

스칼라 서브쿼리

인라인 뷰

- ROWNUM

- WITH

- RANK()

Oracle 카테고리 내 다른 글 보러가기

댓글남기기

최근 글 10 개