DBMS - (10) sub-query[where]: in, exists, all, any

DBMS - (10) subquery : in, exist, all, any

<그림 1: relation example>

Q1) cs major에 지원한 학생들의 sID, sName
select sID, sName
from Student
where sID in (select sID from Apply where major = 'cs');

'in' keyword를 사용하여 sub-query를 구현한 모습이다. select sID from Apply where major = 'cs'를 통해 Apply instance에서 cs에 지원한 sID를 relation으로 반환하고, 반환된 relation에 속한 sID만을 대상 상위 query가 실행된다.

Q2)
select [disticnt] sID, sName
from  Student, Apply
where Student.sID = Apply.sID and major = 'cs';

해당 쿼리는 Apply에서 Berkerley, Stanford에 cs로 지원한 학생이 존재하는 경우 중복된 결과를 반환한다.

Q3)
select GPA
from Student
where sID in (select sID from Apply where major='cs');

Q4)
select [distinct] GPA
from Student, Apply
where Student.sID = Apply.sID and major = 'cs';

Q3, Q4의 결정적인 차이는 duplicate issue이다.

Q5) CS에 지원했고 EE에는 지원하지 않은 학생의 sID, sName ( difference set opration을 사용하지 않고 )
select sID, sName
from Student
where sID in (select sID from Apply where major = 'CS')
and sID not in (select sID from Apply where major = 'EE');

in keyword, not in keyword를 조합하여 difference set operation과 동일한 효과를 얻을 수 있다.

in operaton : sub-query의 결과로 반환된 relation에 속한 tuple만을 대상으로 부모 query를 실행

Q6)
select cName, state
from College C1
where exists (select * from College C2 where C2.state = C1.state and C1.cName <> C2.cName);

exists operation은 C1 instance의 각 tuple이 sub-query에 해당하는 condition의 결과가 True or False인지 체크하여 그 결과가 True이면 부모 query를 실행한다고 이해하면 된다.
예를 들어, Berkeley와 Stanford는 같은 State에 있으므로 sub query 결과가 True이지만 MIT는 그렇지 않아 부모 쿼리의 최종 쿼리 결과에는 Berkeley와 Stanford만 존재한다.

Q7)
select cName
from College C1
where not exists (select * from College C2 where C2.enrollment > C1.enrollment);

Q7는 enrollment(학생수)가 제일 많은 대학의 cName을 반환한다. 왜냐하면, not exists의 의미는 C1 instance의 해당 tuple이 sub query를 실행했을 때 반환되는 relation이 아무것도 없을 때 부모 쿼리를 실행한다는 뜻이다.
따라서, sub query의 결과에 아무것도 없으려면 해당 tuple의 enrollment가 max인 경우에만 해당한다.
즉, Q7의 의미는 max이다.

Q8)
select  sName, GPA
from Student S1
where not exists (select * from Student S2 where S2.GPA > S1.GPA);

Q8은 GPA가 가장 높은 학생을 반환하는 쿼리이다.

Q9)
select sName, GPA
from Student
where GPA >= all (select GPA from Student);

all keyword를 사용하여, 서브쿼리의 결과에 해당하는 릴레이션 모두(=all)보다 GPA가 같거나 큰 경우에 해당하는 tuple만을 반환한다.

Q10)
select sName
from Student S1
where not GPA <= any (select GPA from Student S2 where S1.sID <> S2.sID);

all keyword는 역부등호의 not ~ any와 의미가 동일하다.

Q11)
select sID, sName, sizeHS
from Student
where sizeHS > any (select sizeHS from Student);

any keyword의 의미는 서브 쿼리로 반환된 릴레이션에 속한 1개 이상의 튜플에 대해 condition의 결과가 true이면 부모 쿼리를 실행한다는 뜻이다.
따라서, Q11은 학생수(sizeHS)가 제일 적은 학교가 아닌 학생들을 반환한다.

Q12)
select sID, sName, sizeHS
from Student S1
where exists (select * from Student S2 where S2.sizeHS < S1.sizeHS);

Q12는 Q11과 결과가 동일하다.

Q13)
select sID, sName
from Student
where sID = any (select sID from Apply where major = 'cs')
and not sID = any (select sID from Apply where major = 'EE');

Q13은 Q5과 결과가 동일하다.

Summary
in : 서브 쿼리가 반환하는 릴레이션에 속한 경우 부모 쿼리 실행
exists : 서브 쿼리가 반환하는 릴레이션이 존재하면 실행, 존재하지 않으면 실행하지 않음
all : 서브 쿼리가 반환하는 릴레이션이 가진 모든 튜플에 대해 condition을 만족해야 실행
any : 서브 쿼리가 반환하는 릴레이션이 가진 1개 이상의 튜플에 대해 condition을 만족해야 실행

Reference
https://www.youtube.com/watch?v=IJPXosPGLTU&list=PL6hGtHedy2Z4EkgY76QOcueU8lAC4o6c3&index=13