단일 값(single value)이 다수 값에 속하는가를 검사
Select name, salary
from professor
where pID in (10, 21, 22); // 이 안에 존재하면 참```
값 하나 간의 비교는 간단하나, 값 하나와 여러 값 간의 비교는 간단하지 않다. 그래서 some, any, all연산자를 지원한다
// ver1
Select name, salary
from professor as T, professor as S
where T.salary > S.salary and S.deptName='CS'
//ver2
Select name
from professor
where salary > some (select salary
from professor
where deptName = 'CS');
Select name
from professor
where salary > all (select salary
from professor
where deptName='CS');
내부 중첩질의에서 외부 테이블을 참조하면, 이를 correlated subquery라고 부른다.
인자 형태로 subquery 결과가 존재하면 참
정의
예시
Find all course numbers taught in both the Fall 2009 semester and in the Fall 2010 semester
Select s.cID
from teaches as S
where S.semester='Fall' and S.year=2009 and
exists (select *
from teaches as T
where T.semester='Fall' and T.year=2010
and S.cID = T.cID);```
not-correlated 된 subquery에 exists를 쓰는것은 무의미하다
Select distinct sID
from student
where exists (select cID
from course
whree deptName='CS');
// 아래 문장과 동일하다
select distinct sID from stuent;```
sql에서 for all은 제공하지 않으므로, not exists(...except ...) 로 표현한다
for all = not exists(반드시 공집합이 나오는 것)
Select sID, name
from student as S
where not exists( (select cID
from course
where deptName='CS')
except (select T.cID
from takes as T
where S.sID = T.sID)
);// CS가 제공하는 강의 주 어떤건 듣고, 어떤걸 안들으면 거짓 리턴```
인자형식으로 표현되는 서브질의 결과에 중복성이 있는지를 검사
unique(r)에서 만약 r이 공집합이면(즉, 질의 결과가 없으면) true
//ver1
Select C.cID
from course as C
where unique(select T.cID
from teaches as T
where C.cID=T.cID and T.year=2009);
// ver2
Select C.cID
from course as C
where 1 >= (select count(T.cID)
from teaches as T
where C.cID=T.cID and T.year=2009);```
Find department name and the average salary of the professors of the department where the average salary is greater than 6900
// ver 1
Select deptName, avg(salary)
from (select deptName, avg(salary) as avgSalary
from professor
group by deptName)
where avgSalary > 6900;
// ver 2
Select deptName, avgSalary
from (select deptName, avg(salary)
from professor
group by deptName) as deptAvg(deptName, avgSalary)
where avgSalary > 6900;```
Find the maximum total salary of department across all departments
// ver1
Select max(totalSalary)
from (select deptName, sum(salary)
from professor
group by deptName) as deptTot(deptName, totalSalary);
//ver2
Select sum(salary)
from professor
group by deptName
having sum(salary) >= all(select sum(salary)
from professor
group by deptName);```
SQL 문장의 결과를 임시 저장
Find the department name with the maximum budget, along with its budget
//ver 1
With maxBudget(value) as
(select max(budget)
from department)
select deptName, budget
from department, maxBudget
where department.budget = maxBudget.value;
// ver2
Select deptName, budget
from department
where budget=(select max(budget) from department);```
Find all departments where the total salary is greater than the average of the total salary at all departments
with deptTotal(deptName, value) as
(select deptName, sum(salary)
from professor
group by deptName),
deptTotalAvg(value) as
(select avg(value)
from deptTotal)
select deptName
from deptTotal, deptTotalAvg
where deptTotal.value > deptTotalAvg.value;```
결과 테이블이 하나의 속성으로 이루어지고 동시에 하나의 튜플만을 가지면 서브질의가 연산 식에서 값이 반환되는 어떤 곳이라도 나타날 수 있게 한다. 이러한 서브질의를 scalar subquery라고 함
Select deptName, (select count(*)
from professor p1
where d1.deptName = p1.deptName)
from department d1;```