Table Variables and Set Operators, Subqueries in WHERE clause

출처

https://www.edx.org/learn/relational-databases/stanford-university-databases-relational-databases-and-sql

StanfordOnline: Databases: Relational Databases and SQL | edX

 

https://www.edx.org/learn/relational-databases/stanford-university-databases-relational-databases-and-sql

 

select cName as name from College
union all
select sName as name from Student;

name 컬럼 아래 중복 제거 없이 이름들이 출력된다

union all: multi-set operator(중복을 허용한 집합 연산자) that retains duplicates

 

소팅하려면

select cName as name from College
union all
select sName as name from Student
order by name;

 

 

* intersect

 

CS와 EE에 둘 다 지원한 학생 구하기

select sID from Apply where major = 'CS'
intersect
select sID from Apply where major = 'EE';

 

어떤 Database system들은 intersec operator를 지원하지 않는 경우도 있다.

그때는 다음과 같이 self join 쿼리를 만들 수 있다.

select distinct A1.sID
from Apply A1, Apply A2
where A1.sID = A2.sID and A1.major = 'CS' and A2.major = 'EE';

 

* except

 

except는 관계대수에서 difference다

minus라고도 불린다.

except는 SQL standard 용어

 

CS에는 지원했지만 EE에는 지원하지 않은 학생 구하기

select sID from Apply where major = 'CS'
except
select sID from Apply where major = 'EE';

 

어떤 DB system은 except operator를 지원하지 않는다.

단순 where절로는 표현할 수 없다.

subquery를 이용해야 한다.

sub queries are nested, select statements within the condition.

select sID, sName
from Student
where sID in (select sID from Apply where major = 'CS');

 join 형식으로도 쓸 수 있다

select distinct Student.sID, sName # Stduent.을 써야 한다. Apply.sID와 값이 같더라도!
from Student, Apply
where Student.sID = Apply.sID and major = 'CS';

 

 

* sub query를 써야만 하는 경우

 

I. in, not in을 써서 testing membership in the sets that are produced by sub-queries

(checking whether values are in the subquery)

II. exist operator를 써서 sub query set이 empty인지 아닌지 구하기

 

I. in, not in

 

(1) CS를 지원한 학생들의 GPA 평균을 구할 때

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

join 형식으로는 distinct를 쓰든 안 쓰든 정확하게 학생들의 GPA를 출력할 수 없어서 sub query를 써야만 한다.

distinct를 쓰면: 다른 학생인데도 GPA가 같다는 이유로 중복이 제거되고 1개만 반환된다

distinct를 안 쓰면: 한 학생이 여러 대학의 'CS'에 지원한 경우, 한 학생의 GPA가 여러 번 카운트된다

 

(2) except 연산자가 필요한 경우

CS 전공에는 지원했으나 EE 전공에는 지원하지 않은 학생 구하기

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

not의 위치를 바꿀 수도 있다.

select sID, sName
from Student
where sID in (select sID from Apply where major = 'CS')
	and not sID in (select sID from Apply where major = 'EE');

 

any를 활용해서 다음과 같이 바꿀 수도 있다.

 

하지만 다음은 틀렸다.

select sID, sName
from Student
where sID = any (select sID from Apply where major = 'CS')
	and sID <> any (select sID from Apply wehre major = 'EE');

왜?

sID <> any (~) 부분 때문에 틀렸다.

sub query 결과 집합에 여러 학생의 sID가 있을 것이라 다 통과하기 때문

 

다음과 같이 바꾸면 정답

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

For each member of the set of the EE application,

that value is not equal to the SID of the student we're going to retrieve.

 

II. exist

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

 

(2) finding the college that has the largest enrollment

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

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

select sName, GPA
from Student S1
where GPA >= all (select GPA from Student S2 where S1.sID <> S2.sID)

 

 

* any

 

must satisfy the condition with at least one element of the set.

 

대학들의 enrollment가 unique하고, 최대 등록수를 가진 대학을 구할 때

select cName
from College C1
where not enrollment <= any (select enrollment from College C2 where S2.cName <> s1.cName);

 

고등학교 학생 수가 제일 적지 않은 학생 구하기

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

 

SQLite는 any와 all operator를 지원하지 않는다.

same query written without using any

exist, not exist를 활용한다.

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