23/01/02
📌 7. Subquery
SELECT select_list
FROM table
WHERE expr operator
(SELECT select_list
FROM table);📂 Subquery란?
- 직원 중 최소급여를 받는 사원들의 정보를 출력하시오.
select last_name, job_id, salary
from employees
where salary = (salary Min(salary)
from employees);
- 부서별 50번 부서의 최소급여보다 큰 부서들의 최소급여를 구하시오.
select department_id, MIN(salary)
from employees
group by department_id
having MIN(salary) > (select MIN(salary)
from employees
where department_id = 50);
📂 Inline View - From절에 Subquery가 작성된 경우
- 자신이 속한 부서의 평균급여보다 본인의 급여가 더 많은 사원을 출력하시오 (즉, 부서에서 평균 이상의 급여를 받고 있는 사원)
<연습문제>
- employees 테이블로부터 평균 이상의 급여를 받는 모든 사원의 employee_id, last_name, salary를 출력하되 salary를 기준으로 오름차순 정렬하여 출력하시오.
// salary에 null값이 있는 테이블의 경우
select employee_id, last_name, salary
from employees
where salary >= (select avg(nvl(salary,0))
from employees)
order by salary;
📌 고급 Subquery 유형
🔷 단일행 서브쿼리
🔷 다중행 서브쿼리
🔷 단일컬럼 서브쿼리
🔷 다중컬럼 서브쿼리
📂 1. 단일 행 서브쿼리
서브쿼리로부터 단일행이 반환되는 유형
메인쿼리에 단일행 비교연산자 사용
❌ 잘못된 예제
⭕ 올바른 예제
select last_name, job_id, salary
from employees
where job_id = (select job_id
from employees
where last_name = 'Bell')
and salary > (select salary
from employees
where last_name = 'Bell');
- 부서별 최소급여와 동일한 급여를 받는 사원을 출력하시오.
❌ 잘못된 예제
⭕ 올바른 예제
select employee_id, last_name
from employees
where salary in (select min(salary)
from employees
group by department_id);
- 'Haas'와 동일한 업무 담당자를 출력하시오.
select last_name, job_id
from employees
where job_id = (select job_id
from employees
where last_name = 'Haas');
📂 2. 다중 행 서브쿼리
서브쿼리로부터 다중행이 반환되는 유형
메인쿼리에 다중행 비교연산자 사용
// <any : 최대값보다 작은지를 비교하는 연산자
select employee_id, last_name, job_id, salary
from employees
where salary <any(select salary
from employees
where job_id = 'IT_PROG')
and job_id <> 'IT_PROG';
// < all : 최소값보다 작은지를 비교하는 연산자
select employee_id, last_name, job_id, salary
from employees
where salary <all (select salary
from employees
where job_id = 'IT_PROG')
and job_id <> 'IT_PROG';
- 다중행 서브쿼리로부터 null값이 포함된 값리스트가 반환되는 경우
[case1] 메인쿼리에 OR의 성격을 가지는 비교연산자 사용 시에는 정상결과 출력된다.
[case2] 메인쿼리에 AND의 성격을 가지는 비교연산자 사용 시에는 메인쿼리 결과도 null이다.
=> 정상 결과 출력을 위해서는 서브쿼리구문에 null값 제외 조건문을 작성해야 함.
- 자기 자신이 매니저가 아닌, 즉 최하위 직원을 출력하시오. [case2]
select emp.last_name
from employees emp
where emp.employee_id <> all
(select mgr.manager_id
from employees mgr
where manager_id is not null);📂 3. 단일컬럼 서브쿼리
서브쿼리로부터 하나의 컬럼을 기준으로 값이 반환되는 유형
비쌍 비교 방식 사용
📂 4. 다중컬럼 서브쿼리
서브쿼리로부터 여러 컬럼을 기준으로 값이 반환되는 유형
쌍 비교 방식 사용하기 (좌변과 우변의 컬럼 개수 일치해야 함)
- 부서별 최소급여를 받는 사원을 정보를 출력하시오.
<연습문제>
-
employees 테이블로부터 last_name에 "u"가 포함된 사원과 같은 부서에 근무하는 모든 사원의 employee_id와 last_name을 출력하는 구문을 작성하시오.
-
employees 테이블로부터 평균보다 많은 급여를 받고 성에 "u"가 포함된 사원이 있는 부서에서 근무하는 모든 사원의 employee_id, last_name 및 salary를 출력하는 구문을 작성하시오
Hello