[ DB ] 8. SQL (3) : Subqueries

1. Nested Subqueries

(1) Subquery

: Query 안의 Query로, select-from-where 절 어디에나 들어갈 수 있다.

 

(2) Set Membership

ⓐ in / not in

- query의 결과가 subquery에 속하는지, 속하지 않는지에 관한 operation

- in은 intersect operation과 유사하다.

- not in은 except operation과 유사하다.

 

ex. 2017년 가을학기와 2018년 봄학기에 모두 개설된 강좌를 찾으시오

select distinct course_id
from section
where semester='Fall' and year=2017 and course_id in 
(select course_id 
from section 
where semester='Spring' and year=2018)

 

ex. 2017년 가을학기에는 개설되었지만, 2018년 봄학기에는 개설되지 않은 강좌를 찾으시오

select distinct course_id
from section
where semester='Fall' and year=2017 and course_id not in 
(select course_id 
from section 
where semester='Spring' and year=2018);

 

ex. 교수의 ID가 10101인 강의를 들은 학생들의 수를 구하시오

select count(distinct ID)
from takes
where (course_id, sec_id, semester, year) in 
(select course_id, sec_id, semester, year
from teaches
where teaches.ID=10101);

 

 

ⓑ some

- 최소 하나 이상

출처 : Database System Concepts SILBERSCHATZ 7th edition

 

ex. 적어도 한명의 Biology 전공 교수의 연봉보다 많이 받는 모든 교수의 이름을 구하시오

select T.name
from instructor as S, instructor as T
where S.dept_name='Biology' and T.salary>S.salary;

select name
from instructor
where salary > some (select salary from instructor where dept_name='Biology');

 

 

ⓒ all

- 모두 다

출처 : Database System Concepts SILBERSCHATZ 7th edition

 

ex. 모든 Biology 전공 교수의 연봉보다 많이 받는 교수의 이름을 구하시오

select name 
from instructor
where salary>all (select salary from instructor where dept_name='Biology');

 

 

ⓓ exists

- 존재하는 relation을 대상으로 하기 위한 operation

 

- Correlation name : 바깥쪽 쿼리에 존재하는 변수 (아래의 예시에서 S)
- Correlated subquery : 안쪽 쿼리에 존재하는 변수 (아래의 예시에서 T)

 

ex. 2017년 가을학기와 2018년 봄학기에 모두 개설된 강좌를 찾으시오

select sourse_id
from section as S
where semester='Fall' and year=2017 and
exists (select * from section as T
		where semester='Spring' and year=2018 and S.course_id = T.course_id);

 

ⓔ In vs. Exists

- In : 조건에 해당하는 row의 컬럼을 비교하여 체크한다. 

서브 쿼리 ▶ 메인 쿼리

- select 절에서 조회한 컬럼 값으로 비교하여 exits에 비해 성능 떨어짐

 

- Exists : 조건에 해당하는 row의 존재 유무 체크하고 체크 후에 더이상 수행하지 않음.

메인 쿼리 ▶ 서브 쿼리

- exists는 메인 쿼리 먼저 실행되기 때문에, 바깥쪽 쿼리와 안쪽 쿼리가 같은 relation을 참고한다면, rename 해주어야 한다.

- exists는 일반적으로 select 절을 검토하지 않다보니 성능이 더 좋다.

 

 

ⓕ unique

- 단 하나만 존재할 때 True를 반환한다.

 

ex. 2017년에 단 한번 개설된 강좌를 찾으시오

select T.couse_id
from course as T
where unique (select R.course_id from section as R
where T.course_id = Rcourse_id and R.year=2017);

 

 

 

2. Subqueries in the From Clause

- from 절 내부의 select 절을 통해서 조건에 맞는 relation을 대상으로 바깥 query를 출력한다.

 

ex. 학과 별 평균 연봉이 42000보다 큰 학과의 이름과 평균 연봉을 출력하시오

select dept_name, avg_salary
from (select dept_name, avg(salary) as avg_salary
	from instructior
    group by dept_name)
 where avg_salary > 42000;

 

 

3. With Clause

- 바깥 쪽에서 subquery를 지정해주는 방식.

- 여러번의 연산이 필요할 때 with 절을 사용해주면, 좀 더 간결한 query를 구성할 수 있다.

 

ex. maximum budget을 가진 모든 학과를 출력하시오

with max_budget (value) as
	(select max(budget)
    from department)
 select department.name
 from department, max_budget
 where department.budget = max_budget.value;

 

ex. 모든 학과의 총 연봉의 평균보다 큰 모든 학과의 이름과 평균 연봉을 출력하시오

with dept_total (dept_name, value) as
(select dept_name, sum(salary)
from instructor
group by dept_name),

dept_total_avg(value) as
(select avg(value)
from dept_total)

select dept_name
from dept_total, dept_total_avg
where dept_total.value > dept_total_avg.value;