[Oracle] 서브 쿼리(Subquery)

yourjin·2022년 3월 14일

inline view oracle scalar subquery subquery

dev.log

목록 보기

2/14

➕ Topic

회원 수 통계를 위한 배치를 개발하면서, 한 달 넘게 쿼리를 짜고 수정하고 반복하고 있다...

이번에 쿼리를 짜면서 서브 쿼리(Subquery)를 사용했는데, 새롭게 안 내용이 많아 정리하고 간다.

➕ Contents

서브 쿼리(Subquery)라면 “Select 문 안에 또 다른 Select 문이 있는 것?” 정도로 이해하고 있었다. 하지만 서브 쿼리에도 여러 종류가 있다. 어느 위치에서 쓰이느냐에 따라 구분되는데, 이 용어를 기억하고 있으면 다른 개발자/DBA 분들과 소통하기가 편해진다!

1. 서브 쿼리(Subquery)

출처: http://www.gurubee.net/lecture/1501

sub(하위) + query(질문)
즉, 하나의 쿼리 안에 포함되어 있는 또 다른 쿼리를 의미한다.
보통 Subquery는 Main Query 이전에 한 번 실행된다.
단일 행 연산자(=, >, >=, <, <=, <>, !=)와 다중 행 연산자(IN, NOT IN, ANY, ALL, EXISTS)들이 서브 쿼리에 사용되며, Subquery는 연산자의 오른쪽에 위치해야 한다.

2. (Nested) Subquery (= Where 절 서브 쿼리)

Where 절에서 쓰이는 서브 쿼리를 말한다.
- 서브 쿼리의 결과를 메인 쿼리의 조건을 설정하는 데 사용한다.
흔히 서브 쿼리라고 하면, Where 절 서브 쿼리를 의미한다.
반환하는 행의 개수에 따라서 다른 연산자를 사용한다.
- Single-Row Subquery (= 단일 행 서브쿼리)
- Multiple-Row Subquery (=다중 행 서브쿼리)
서브 쿼리에서 여러 컬럼을 반환 (Pairwise)해도 된다. 하나의 컬럼을 반환(Nonpairwise)하는 방식으로 수정할 수도 있다.
- Multiple-Column Subquery (=다중 컬럼 서브쿼리)

3. Inline View (= From 절 서브 쿼리)

From 절에서 쓰이는 서브 쿼리를 말한다.
- 서브 쿼리의 결과를 메인 쿼리의 테이블/뷰(View)로 사용한다.
Inline View 안에 또 다른 Inline View가 올 수 있다.

4. Scalar Subquery (= Select 절 서브 쿼리)

Select 절에서 쓰이는 서브 쿼리를 말한다.
- 서브 쿼리의 결과를 메인 쿼리의 컬럼 값(Scalar)으로 사용한다.
반드시 단일 행/단일 값을 반환해야 한다.
일치하는 값이 없는 경우 NULL을 반환 한다.
메인 쿼리에서 추출되는 데이터 건 수 만큼 수행되기 때문에, 조인으로 수행 될 때보다 수행횟수가 적을 수 있다. 따라서 불필요한 조인을 하지 않기 위해 많이 사용한다.

참고: Scalar Subquery 값은 바로 계산식에 사용할 수 없다.

이 내용을 쓰기 위해 이번 포스팅을 작성했다고 봐도 과언이 아니다.

즉, Scalar Subquery로 가져온 값을 추가적인 연산을 하는데 사용 할 수 없다는 것이다.

내가 작성한 쿼리를 전부 다 첨부하기는 어려워, 구루비 강좌에서 사용한 예시를 조금 수정해 보았다.

SELECT d.deptno, d.dname,
       (SELECT MAX(sal) FROM emp WHERE deptno = d.deptno) - (SELECT MIN(sal) FROM emp WHERE deptno = d.deptno) as diff
  FROM dept d;

여기서는 부서별 최대 연봉과 최소 연봉의 차이를 diff라는 컬럼으로 표현하고 있다.
이런 식으로 Scaler Subquery 값을 계산식에 사용하면 오류가 난다!

SELECT d.deptno, d.dname,
       (SELECT MAX(sal) - MIN(sal) FROM emp WHERE deptno = d.deptno) as diff
  FROM dept d;

이렇게 애초에 Scalar Subquery에서 계산이 완료된 값을 반환하거나

SELECT d.deptno, d.dname,
       d.max_sal - d.min_sal as diff
  FROM (
            SELECT deptno, dname, MAX(sal) max_sal, MIN(sal) min_sal
              FROM dept 
       ) d;