Oracle Subquery (Scalar, Inline View, Nested, Correlated)

Subquery 종류

1. SELECT 절에 위치: Scalar Subquery
2. FROM 절에 위치 : Inline View
3. WHERE 절에 위치 : Nested Subquery

*Correlated Subquery : Nested Subquery의 하위 개념. 참조 테이블이 Parent, Child 관계일 때 성립함.

Oracle에서는 이 경우 기본적으로 outer query를 inner query보다 먼저 처리함. 처리 경로는 후에 Optimizer에 의해 조정될 수 있음 (작은 테이블부터 먼저 접근)

Non-Correlated Subquery 예시
select * from departments
where employee_id in (select employee_id from employees);

Correlated Subquery 예시
select * from departments dep
where employee_id in
(select employee_id from employees emp where emp.employee_id = dep.employee_id)

Correlated Subquery는 매 행마다 outer query의 값을 사용하여 처리하는 방식이므로 성능저하 우려가 있다 (하단 다이어그램 참고). 따라서 JOIN을 사용한 튜닝을 고려할 수 있다.

Subquery 유의사항
1. Subquery는 GROUP BY 절에 사용될 수 없다.
2. Subquery는 GROUP BY, HAVING 절을 포함할 수 있다.
3. Subquery는 ORDER BY 절을 제한적으로 포함할 수 있다.*
4. Subquery는 255레벨까지 중첩할 수 있다 (Inline View는 제한 없음)

*간혹 다른 reference에서 Subquery는 ORDER BY 절을 포함할 수 없다고 하는데, 이는 잘못된 정보다.  정확한 정보는 ‘ORDER BY는 전체 쿼리 문에서 한 번 사용될 수 있다’ 이다. 이 제한조건에 따라 Main Query에서 ORDER BY가 사용되지 않을 경우 Subquery에서 ORDER BY를 사용할 수 있다. 반대로 Main Query에서 ORDER BY가 사용되었을 경우, Subquery에서 ORDER BY를 사용할 수 없다.

출처:
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9534134100346876724
https://en.wikipedia.org/wiki/Correlated_subquery
https://www.geeksforgeeks.org/sql-correlated-subqueries/
http://www.gurubee.net/lecture/2380
Oracle 시험 가이드, 황순신