[데이터베이스] SQL - (3)

중첩 하위 질의(Nested Subuery)

---

 

subquery는 다른 query 안에 중첩된 select-from-where 표현이다.

 

- 멤버십 비교

 

in 접속사는 집합 멤버십을 테스트하는데, 집합은 select 절에 의해 생성된 값들의 집합이다.

not in 접속사는 집합 멤버십의 부재를 테스트한다.

 

예시)

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);

 

in과 not in 연산자는 열거형 집합(enumerated set)에서 사용할 수 있다.

 

예시)

select distinct name

from instructor

where name not in ('Mozart', 'Einstein')

 

 

 

- 집합 비교

 

'생물학과의 적어도 한 교수보다 급여가 많은 모든 교수의 이름을 구하라'는 query는 다음과 같이 작성할 수 있다.

 

select distinct T.name

from instructor as T, instructor as S

where T.salary > S.salary and S.dept_name = 'Biology'

 

여기서 "하나 이상보다 큰"이 "> some"으로 표현할 수 있다.

 

select name

from instructor

where salary > some (select salary

                                    from instructor

                                    where dept_name = 'Biology');

 

'생물학과의 각 교수보다 급여가 많은 모든 교수의 이름을 구하라'는  query를 "> all"을 사용해서 다음과 같이 나타낼 수 있다. 이때, "> all"은 "모든 것보다 큰"이라는 의미를 가진다. 

 

select name

from instructor

where salary > all (select salary

                               from instructor

                               where dept_name = 'Biology');

 

 

 

- 빈 테이블에 대한 테스트

 

exists 구문은 인자의 subquery가 비어있지 않을  때 true를 반환한다.

 

예시)

2017년 가을 학기와 2018년 봄 학기 둘 다 있는 과목을 구하라.

 

select course_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);

 

not exists 구문을 이용하여 subquery의 결과에 tuple이 존재하지 않음을 검사할 수 있다.

 

 

 

- From 절의 subquery

 

SQL은 from 절에서  subquery 표현을 허용한다.

 

select-from-where 표현은 테이블을 결과로 반환하고, 따라서 select-from-where 표현은 테이블이 나타나는 어디에서나 적용 가능하다.

 

예시)

select dept_namt, avg_salary

from (select dept_name, avg (salary) as avg_salary

          from instructor

          group by dept_name)

where avg_salary > 42000;

 

-> from 절의 subquery가 평균 급여를 계산하고, 이전(SQL - (2)에서의 예시 참조)의 having 절에 있던 predicate가 바깥 query의 where 절에 있으므로 having 절을 사용할 필요가 없다.

 

위의 query와 같은 표현으로는 다음 같은 query들이 있다.

 

select dept_namt, avg_salary

from (select dept_name, avg (salary)

          from instructor

          group by dept_name) as dept_avg(dept_name, avg_salary)

where avg_salary > 42000;

 

or

 

select dept_namt, avg_salary

from (select dept_name, avg (salary) as avg_salary

          from instructor

          group by dept_name) as dept_avg

where avg_salary > 42000;