[Oracle SQL] Ch18. 고급 서브 쿼리

 

목표

• 여러 열 서브 쿼리 작성
• 널 값이 검색되는 경우 서브 쿼리의 기능 설명
• From 절에 서브 쿼리 작성
• SQL에서 스칼라 서브 쿼리 사용
• 상관 서브 쿼리로 해결할 수 있는 문제 유형 설명
• 상관 서브 쿼리 작성
• 상관 서브 쿼리를 사용한 행 갱신 및 삭제
• Exists 및 Not exists 연산자 사용
• With 절 사용

---

1. 서브쿼리

서브 쿼리(내부 질의)는 기본 질의 실행 전에 한 번 실행됨

서브 쿼리의 결과는 기본 질의(메인쿼리)에 사용됨

 

149번 사원이 받는 사원의 급여보다 높은 급여를 받는 사원의 마지막 성을 보여주시오

 

10500 보다 큰 금액

 

1.1 서브쿼리

Q. 회사의 평균 급여보다 적은 급여를 받는 사원의 이름을 모두 표시하시오

 

 

 

 

 

2. 다중 열 서브쿼리

- 쌍(pairwise)비교

- 비쌍(nonpairwise)비교

 

 

 

 

---

150번 사원이 근무하는 부서에 근무하는 사원중 149번 사원보다 더 많은 급여를 받는 사워느이 이름과 급여 그리고 부서번호를 찾으시오.

 

 

우선 150번 사원이 근무하는 부서?

select department_id
from employees
where employee_id=150

 

 

149번 사원이 받는 급여

select salary
from employees
where employee_id =149

 

이 두개의 쿼리를 합쳐야하는 경우이다.

 

 

 

 

쿼리 역 해석

 

174번 사원은 149번 사원에게 관리를 받고 현재 80번 부서에 배정받아있다

178번 사원은 149번 사원에게 관리를 받고 현재 부서에 배정받아 있지는 않다.

 

 

 

 

 

비쌍 비교 서브 쿼리

Q.사원 번호가 174또는 141인 사원과 관리자가 같으며

사원번호가 174또는 141인 사원과 부서가 같은 사원 정보를 표시하기

 

*사원정보는 사원번호, 관리자, 사원 부서이다.

employee_id, manager_id, department_id

 

이렇게 찾으면 149번 124번

select manager
from employeees
where employee_id in (174,141)

 

이렇게 찾으면 부서번호가 50번, 80번

select department_id
from employees
where employee_id in (174,141)

 

 

첫번째 sql문 쿼리 결과

 

 

 

---

 

order by 절의 스칼라 서브 쿼리

 

 

 

10. 상호관련 서브 쿼리(correlated subquery)

특이한 구조

메인 쿼리가 먼저 수행되고 이후에 서브쿼리가 시행됨

 

Q. 소속 부서의 평균 급여보다 많은 급여를 받는 사원을 모두 찾으시오.

 

method1. 상호 관련 서브 쿼리를 쓰지 않는 경우

부서가 10인 경우, 20인 경우,... 이렇게 모든 부서에 대해서.

method2. 상호 관련 서브 쿼리를 쓴 경우

 

 

10.1 상호관련 서브 쿼리(correlated subquery)

업무를 두 번 이상 바꾼 사원에 대한 정보를 표시하기

 

질의

select e.employee_id, last_name, e.job_id
from employees e
where 2 <= (select count(*)
from job_history
where employee_id = e.employee_id)

 

11. exists 연산자 사용

-서브쿼릴의 결과 집합에 행이 있는지 여부를 검사

-서브쿼리 행 값이 발견되는 경우 :내부 질의에서 더 이상 검색하지 않음

조건플래그가 true가 됨

 

-서브쿼리 행 값이 발견되지 않는 경웅 : 조건 플래그가 false가 됨

내부 질의에서 검색을 게속함

 

 

12. 상호 관련 update

상호 서브 쿼리를 사용하몀ㄴ 다른 테이블의 행에 따라 테이블의 행을 갱신할 수 있다!

 

Q. 부서 이름을 저장하기 위한 열을 추가하여 employees 테이블을 비정규화하기

상호 관련 update를 사용하여 테이블을 채운다