[SQL] 7. 서브쿼리(Subquery)

서브쿼리

* 서브쿼리 : 다른 SELECT 문장의 절에 내장된 SELECT문장이다.

SELECT select_list
FROM table
WHERE expr operator 
		(SELECT select_list
        FROM table);

* 서브쿼리 사용시 규칙

- 서브쿼리는 괄호로 둘러싸여야 함(CTAS 제외)

- 비교 연산자의 오른쪽에 위치

- 두 종류의 비교연산자 사용(단일행, 다중행)

select first_name, salary
from employees
where salary > (select salary from employees where first_name='Nancy');

 

단일행 서브쿼리

- 내부 select 문장으로부터 하나의 행을 반환하는 질의

연산자	설명
=	같다
>	보다 크다
>=	보다 크거나 같다
<	작다
<=	작거나 같다
<>, !=	같지 않다

select first_name, job_id, hire_date
from employees
where job_id = (select job_id from employees where employee_id=103);

 

다중행 서브쿼리

- 서브쿼리의 결과가 2개 행 이상일 경우

연산자	설명
IN	목록의 어떤 값과 같은지 확인
EXISTS	값이 있는지 확인. + 서브쿼리만 가능
ANY, SOME	비교 연산자. 하나만 만족
ALL	비교 연산자. 모든 값과 비교해서 만족
ALL / ANY 차이점	< ANY : 가장 큰 값보다 작으면 됨 > ANY : 가장 작은 값보다 크면 됨 < ALL :  가장 작은 값보다 작으면 됨 > ALL : 가장 큰 값보다 크면 됨 = ANY : IN과 같은 역할

select first_name, salary
from employees
where salary > (select salary from employees where first_name='David');


ORA-01427: single-row subquery returns more than one row
01427. 00000 -  "single-row subquery returns more than one row"
*Cause:    
*Action

select first_name, salary
from employees
where salary > any (select salary from employees where first_name='David');

select first_name, department_id, job_id
from employees
where department_id in (select department_id
                        from employees
                        where first_name = 'David');
                        
                        --in(60,80)과 같음

 

상호연관 서브쿼리

- 한 개의 행을 비교할 때마다 결과가 메인쿼리로 반환됨

- 한 행을 처리할 때마다 서브쿼리로 주고 값을 처리한 후 결과를 다시 메인쿼리로 전달=> 성능 저하

- 메인쿼리와 서브쿼리 사이에 결과를 교한하기 위해 서브쿼리의 where 조건절에서 메인쿼리 테이블과 연결

select first_name, salary
from employees a 
where salary > (select avg(salary)
from employees b
where b.department_id = a.department_id);

 

 

스칼라 서브쿼리

- SELECT 절에 사용하는 서브쿼리

select first_name, (select department_name from departments d where d.department_id = e.department_id) department_name
from employees e
order by first_name;

select first_name, department_name
from employees e
join departments d on (e.department_id = d.department_id)
order by first_name;

 

인라인 뷰

- FROM절에 서브쿼리가 온 것

select row_number, first_name, salary
from (select first_name, salary,
row_number() over(order by salary desc) as row_number
from employees)
where row_number between 1 and 10;

 

 

3중 쿼리와 Top-N 쿼리

- 서브쿼리 안에 또 다른 서브쿼리를 갖는 것

select rownum, first_name, salary
from (select first_name, salary from employees
order by salary desc)
where rownum between 1 and 10;

 

 

계층형 쿼리

- 수직적 관게를 맺고 있는 행들의 계층형 정보를 조회할 때 사용

SELECT select_list
FROM table
WHERE conditions
START WITH top_level_cindition
CONNECT BY [NOCYCLE] [PRIOR] connect_condition
ORDER SIBLING BY order_condition;

select employee_id,
lpad(' ',3*(level-1)) || first_name || ' ' ||last_name,
level
from employees
start with manager_id is null
connect by prior employee_id = manager_id;

select employee_id,
lpad(' ',3*(level-1)) || first_name || ' ' ||last_name,
level
from employees
start with employee_id=113
connect by prior manager_id=employee_id ;