[ORACLE] SQL - Sub Query (하위 쿼리)

Sub Query 란? 

           - 하나의 SQL 문안에 포함되어 있는 다른 SQL 문을 말한다. 즉 종속관계 

           - 검색할 데이터를 추가로 제한하기 위한 조건으로 기본 쿼리에서 사용할 데이터를 반환하는데 사용하는 것 

 

01. 조건

• 괄호로 묶어야 함
• 단일행 또는 복수행 비교연산자와 함께 사용 가능
• ORDER BY 는 사용하지 못한다. 

--114 번 사원과 같이 근무하는 사원명단은? 
select department_id from employees where employee_id= 114; 
-- 30 -- where 절에 비교하는 컬럼이 pk로 비교시(not null, 중복 x) 결과는 1개 이다. 
select * from employees where department_id=(30);

-- 중첩 => Sub Query 
select * 
from employees 
where department_id=
    (select department_id from employees where employee_id= 114);

 

02. 종류 

 

(1) Single Row SubQuery : 단일행 서브쿼리 

: 실행결과가 단 하나의 행(튜플)으로 결과를 하는 Query 문 

: 대표적인 예로, 조건문에 pk 컬럼을 equl 조건으로 질의한 경우 

: 단일행 연산자로 비교가 가능함    =, <, >, >=, <=, !=, IN, ANY

select * 
from employees 
where department_id <
    (select department_id from employees where employee_id= 114); -- 단일행 SUB QUERY

--105번 사원의 직급(JOB_ID)과 같은 사원의 명단
SELECT * FROM employees
WHERE Job_id =
    (SELECT JOB_ID FROM EMPLOYEES WHERE Employee_id = 105);

--60번 부서의 사원들의 평균 급여(salary)보다 적은 사원의 명단 
SELECT * FROM employees
WHERE Salary < -- 60번 부서의 평균 
    (SELECT avg(Salary) FROM EMPLOYEES WHERE DEPARTMENT_ID= 60)
    ORDER BY DEPARTMENT_ID;

SELECT * FROM EMPLOYEES
WHERE 
    DEPARTMENT_ID =(SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE EMPLOYEE_ID =150)
    AND 
    SALARY > (SELECT SALARY FROM EMPLOYEES WHERE EMPLOYEE_ID = 150);

 

 

(2) Multiple Row SubQuery: 다중행 서브쿼리 

: 서브쿼리의 실행결과가 하나 이상일때 

: 다중행 연산자로 비교 가능: in (여러개) ,  any(), all(), exists()

: 다중행 결과가 나오는데 단일행 연산자로 비교하면 오류

-- city = seattle 에 위치한 부서에 소속된 사원 명단 
select * from departments
where location_id in 
    (select location_id from locations where city= 'Seattle');

select email from employees
where 
    employee_id in (select distinct manager_id from employees 
                   where manager_id is not null);

 

(3) Mulitple column SubQuery

- 단일 컬럼 

: 서브쿼리의 실행결과가 하나의 컬럼 값을 조회하는 쿼리

Select * from employees e
    where 
        department_id= 60
        and 
        exists(select manager_id from departments d where e.employee_id = d.manager_id);

- 다중 컬럼

: 서브쿼리의 실행결과가 여러개의 컬럼 값을 조회하는 쿼리

: 메인쿼리의 조건절에 컬럼수도 여러개(서브 쿼리 컬러수 많큼 존재해야 함 ) : pairwise: 컬럼을 쌍으로 묶어 동시에 비교: uppairwise: 컬럼을 나누어서 비교한 후 and 연산 

select * from employees
where department_id in
    (select department_id from employees group by department_id)
    and
    salary in (select max(salary) from employees group by department_id)
order by department_id;
--상호 연관 서브쿼리(메인쿼리 테이블과 서브쿼리 테이블과 연관관계()

-- 단일행 결과만 허용
select employee_id, email, (select 10 from dual) 
from employees;
--
select employee_id, email, salary, salary*coalesce(commission_pct,0) 보너스,
    (select salary*(1+nvl(commission_pct,0)) 총급여 from employees ii
    where oo.employee_id= ii.employee_id) 총지급급여 
from employees oo;

 

** 

-- from 절 뒤에 서브쿼리가 적용되면:인라인 뷰 
select *
from (select rownum rn, v.* from 
            (select *from employees order by hire_date desc) v )
where rn between 11 and 20
;

---- ------------------------------------------------------------------------
--최상위 노드 -> 최하위 노드에 이르기까지 경로 모두: 순방향 전개 
select employee_id, email, level
from employees
start with manager_id is null -- 최상위 노드
connect by prior employee_id = manager_id;

----107 부터 ~ 부모까지의 경로 : 역방향 전개 
select employee_id, email, manager_id, level le,
    connect_by_isleaf ISLEAF
    FROM EMPLOYEES
    START WITH MANAGER_ID  =107  -- 최상위 노드  
    CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID; -- 부모pk = 자식 fk 
----
select employee_id, email, manager_id, level le,
    connect_by_isleaf ISLEAF -- 단말노드(하위노드가 존재하지 않는 노드) 이면 1, 아니면 하위모드 존재) 0
    FROM EMPLOYEES
    START WITH MANAGER_ID IS NULL  -- 최상위 노드  
    CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID; -- 부모pk = 자식 fk 
------------------------------------------------------------------------------