05.20(SUBQUERY)

서브쿼리(SUBQUERY)란?

하나의 SQL문(main query)에 포함되어 있는 또다른 SQL문(sub query)

서브쿼리는 order by 문법 지원 안됨
①서브쿼리는 메인쿼리가 실행되기 이전에 한번만 실행

②비교연산자의 오른쪽에 기술해야 함

③반드시 괄호로 묶어야 함

④또한 서브쿼리와 비교할 항목은 반드시 서브쿼리의 select한 항목의 개수와 자료형을 일치시켜야 함

 

예제1)관리자 아이디로 관리자 이름 조회

select emp_name
from employee
where emp_id = (select manager_id from employee where emp_name = '권정열');

 

예제2)급여 평균보다 급여를 많이 받는 사원 조회

select emp_name,
       salary
from employee
where salary > (select avg(salary) from employee);

 

SUBQUERY의 유형 

1. 단일행 서브쿼리

서브쿼리의 조회 결과 값의 개수가 1개 일 때 사용

 

예제)급여가 제일 많은 사원과 제일 적은 사원정보 조회(사번, 사원명, 급여)

select emp_id, emp_name, salary
from employee
where salary in ((select max(salary) from employee), (select min(salary) from employee));

 

2. 다중행 서브쿼리

서브쿼리의 조회 결과 값의 행이 여러 개 일 때 사용

 

예제)직급이 대표, 부사장이 아닌 모든 사원의 사원명, 직급코드 조회

select emp_name, dept_code
from employee
where job_code in (select job_code
                    from job
                    where job_name not in('대표', '부사장'));

 

3. 다중열 서브쿼리

서브쿼리의 조회 결과 컬럼의 개수가 여러 개 일 때 사용

 

예제)퇴사한 여직원과 같은 부서, 같은 직급에 해당하는 사원의 이름, 직급, 부서, 입사일을 조회

select emp_name, job_code, dept_code, hire_date 
from employee 
where (dept_code, job_code) in (select dept_code, job_code 
                                from employee 
                                where substr(emp_no, 8, 1) = 2 and quit_yn = 'Y');   

 

4. 다중행 다중열 서브쿼리 

서브쿼리의 조회 결과 컬럼의 개수와 행의 개수가 여러 개 일 때 사용

 

예제)직급별 최소 급여를 받는 직원의 사원명, 직급, 급여 조회

select emp_name, job_code, salary
from employee 
where (job_code, salary) in (select job_code, min(salary)
                             from employee
                             group by job_code)
order by job_code; 

 

5. 상(호연)관 서브쿼리

별칭 꼭 필요함

서브쿼리가 만든 결과값을 메인쿼리가 비교 연산 할 때 사용

메인쿼리의 값을 서브쿼리에 전달하고, 서브쿼리를 수행 후 결과를 리턴

메인쿼리 테이블의 값이 변경되면 서브쿼리의 결과값도 바뀜(행단위로 처리)

 

예제)직급별 평균 급여보다 많은 급여를 받는 사원 조회

select emp_name,
       job_code,
       salary
from employee E
where salary > (select avg(salary)
                from employee
                where job_code = E.job_code)  
order by job_code;

 

6. 스칼라 서브쿼리 

결과값이 한 개인(1행 1열) 상관 서브쿼리
select절, where절, order by절에서 사용가능

 

예제)모든 사원의 사번, 이름, 관리자 사번, 관리자명 조회

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

 

 

exists

서브쿼리 결과, 행이 하나라도 존재하면  true, 존재하지 않으면 false 리턴

=에 비하여 효율적임

select * 
from employee 
where 1=1; --true 리턴매행마다 where절이 실행되는데 모든행의 true를 리턴하기때문에 모든행이 조회됨

select *
from employee
--where exists (select * from employee where dept_code = 'D5');  -- 1=1 행이 있으면 true이기때문에 모든행 리턴
where exists (select * from employee where dept_code = 'D100');