Oracle 기초 : 실전(8) Subquery (update: 2020/06/21)

codePark·2020년 6월 17일

Oracle

목록 보기

12/23

Introduction: Subquery

Main Query(Parent Query)안에 포함되어 있는 SQL문을 Subquery(= Nested Query)라고 하며, Subquery는 Main Query에 종속되어 있는 구조이다. 이때, Subquery가 Main Query보다 먼저 실행되고, 그 결과를 받아 main Query가 실행되어 Result Session을 산출한다.

Guidelines for Using Subqueries

Subquery는 Parentheses()로 묶어서 사용한다.
Comparison Condition의 오른편에 Subquery를 배치해야 한다.
통상적인 경우 Subquery내 order by는 지원되지 않는다.
Single-Row Subqueries는 Single-Row Operator와 사용하고, Multiple-Row Subqueries는 Multiple-Row Operators와 사용해야 한다. (혼용시 에러 발생)

Types of Subqueries

Single-Column with Single-Row Subquery
Single-Column with Multiple-Row Subquery
Multiple-Column Subquery
Correlated Subquery
SCALAR Subquery
Inline View

Syntax: Single-Col. Single-Row Subquery

where Condition1 SingleRowOperator (Subquery)

where dept_no = (select dept_no
		 from employee
         	 where dept_no = '200');

Subquery가 리턴한 결과가 1행, 1열의 값이며 단행 함수, 일반 컬럼등인 경우. 단행의 Subquery이므로 아래의 Single-Row Operator(단행 연산자)와 함께 사용해야 한다. (Multiple-Row Operator와 혼용시 에러발생)

Single-Row Operator:

Syntax: Single-Col. Multiple-Row Subquery

where Condition1 Multi-RowOperator (Subquery)

where emp_no in (select emp_no
		 from employee
        	 where emp_no = manager_id);

Subquery가 리턴한 결과가 1열, 여러 행의 값인 경우이다. 이 때, Subquery를 통해 리턴되는 결과값이 다중행이므로 조건문에 대입되는 비교연산자는 Multiple Rows Operator(다행연산자)이어야 한다.

Multiple-Row Operator:

Syntax: Multiple-Cols. Subquery

where Condition1 Multi-RowOperator (Subquery)

where emp_no, emp_name in (select emp_no, emp_name
		 from employee
        	 where emp_no = manager_id);

Subquery가 리턴한 결과가 다중 열의 값인 경우. 즉 리턴되는 값은 다중 행일 수 밖에 없으므로 다행 연산자를 비교 연산자로 대입하게 된다.

Syntax: Correlated Subquery (Single Row, Single Col)

from Table1 Table1Alias
where Col1 Operator (select Col2
from Table
where Col = Table1Alias.Col)

select emp_name
from employee E
where emp_id = (select manager_id
			from employee
            where E.emp_id = manager_id)

상호연관(Correlated) Subquery. Main Query의 값을 Subquery에 전달하여 실행하고 그 결과를 다시 Main Query로 리턴하여 처리한다.

Syntax: Correlated Subquery with exists Condition

from Table1 Table1Alias
where exists (select Cols
from Table
where Condition);

select *
from employee E
where exists (select emp_name
			from employee
            where E.emp_id = manager_id);

exists Condition: 일종의 조건문.
나열된 대입값 중 단 한 행이라도 조건문에 일치하는 값이 있다면 참, 없다면 거짓을 리턴한다.
이 때, 조건문 검사 단계에서 단 한 건이라도 일치하는 건이 있다면 바로 true를 리턴하므로 이 후의 조건문들은 검사하지 않는다. 참이 리턴되면 해당 조건문은 true가 되어 Correlated Subquery의 select Clause에 작성된 모든 Column들이 출력되며, 거짓이 리턴되면 해당 조건문 자체가 false 처리되어 단 한 행도 출력되지 않는다.

또한 Subquery내의 Select절에 어떠한 Column을 선언해도 전부 같은 결과가 도출되므로 통상적으로 수 1을 입력하여 자리만 채워준다. 직접적으로 출력되는 Column은 Main Query의 select절에서 결정되며, 여기서 선언된 Subquery는 값을 필터링하는 조건문으로써의 역할만을 수행한다.

Syntax: Correlated Subquery with not exists Condition

from Table1 Table1Alias
where not exists (select Cols
from Table
where Condition);

--GET MINIMUM VALUE
select * 
from employee E
where not exists(select 1 
		 from employee 
		 where E.salary > salary);
         
--GET MAXIMUM VALUE
select * 
from employee E
where not exists(select 1 
		 from employee 
		 where E.salary < salary);

not exists: 조건문에 해당하는 모든 값을 제한 값을 리턴한다.

이러한 원리를 이용하여 Aggregate Function인 max와 min을 group by절 선언없이 추려낼 수도 있다. 위에서 최솟값을 구하는 코드의 진행 순서를 보면 다음과 같다:

Main Query employee Table의 salary Column의 값이
Subquery employee Table의 salary Column의 값보다 크다면
해당하는 값을 전부 제외한다. (not exists)
남은 값의 모든 정보를 출력한다. (Main Query - select *)

여기서 Subquery 조건문의 부등호만 바꿔주면 손쉽게 최댓값도 구할 수 있다.
아주 유용한 코드이니 잘 눈여겨 봐두자.

Syntax: SCALAR Subquery

select Col, (select Col
from Table
where Condition-With-MainTableAlias.Col)

select salary, (select trunc(avg(sal))
		from empoloyee
        	where dept_cpde = 'D5')
from employee;

Correlated Subquery 중에 결과값이 단 하나, 즉 Single-Row, Single-Column이면서 통상적으로 select절에 선언된 경우를 의미한다. 결과로 도출된 Result Session은 Main Query에서 통합적으로 추가 조건문을 선언하여 필터링 후 정렬 또한 가능하다.

Introduction/Syntax: Inline View

view란?: 실제 테이블의 데이터를 바탕으로 특정 목적을 가지고 가공해낸 가상의 테이블을 말한다. 복잡한 처리를 간결하게 하고 싶을 때 사용하면 좋다. 크게는 2가지, 1. Inline View(1회용의 view)와 2. Stored View(Database 객체, 호출하여 반복적으로 사용되는 view)가 존재하나 여기서는 Inline View만 다뤄보도록 한다.

select Col1 Col1Alias
from (Subquery Includes-Col-Alias) SubqueryAlias
where SubqueryAlias ComparisonOperator Condition(add'l)

select *
from
(select E.*, decode(substr(emp_no, 8, 1), '2', '여', '4', '여')) gender
from employee E)
where gender = '여';

Subquery내에서 선언한 각 Column의 Alias가 각각 새로운 Column명으로 명명되었으므로, Main Query에서 Subquery내의 Column을 조회하려면 그들의 Alias를 선언해야 조회된다. 단, Subquery에 선언된 모든 Alias(즉 새롭게 명명된 Subquery내의 Column명들) 전부를 선언하지는 않아도 된다. (즉 Subquery의 조회 내용 전부를 Main Query에서 조회하지 않아도 된다.)