SQL - in/exists/not in/not exist

有如下三个表：

college(cName, state, enrollment)
student(sID, sName, GPA, sizeHS)
apply(sID, cName, major, deision)

In:

select sID, sName, sizeHS
from student s1
where sID in (select s2.sID from student s2, student s1 where s2.sizeHS > s1.sizeHS)

234 Bob 1500
543 Craig 2000
567 Edward 2000
765 Jay 1500
123 Amy 1000
456 Doris 1000
654 Amy 1000
789 Gary 800
987 Helen 800
345 Craig 500
876 Irene 400

in 先执行()中的查询，查询得到当s2.sizeHS > s1.sizeHS时所有的s2.sID， 缓存()的sub结果s2.sID到计算机内存，再判断()外的s1中的每一行s1.sID是否与缓存s2.sID相匹配，如果匹配，则将该行放入最终的结果集。

所以一共执行了len(s1.rows) * len(s2.rows)的计算。

exists:

select sID, sName, sizeHS
from student s1
where exists (select * from student s2 where s2.sizeHS < s1.sizeHS) 

123 Amy 1000
234 Bob 1500
345 Craig 500
456 Doris 1000
543 Craig 2000
567 Edward 2000
654 Amy 1000
765 Jay 1500
789 Gary 800
876 Irene 400
987 Helen 800

exists先提取表s1，选取s1中的每一行，将其代入()中，查看改行对()的条件True or False，若果是True，则将改行添加到结果集，如果是False，则放弃改行，查找下一行。

过程如下：
step 1: s1.row1
step 2: is it true or false that any s2.sizeHS < s1.row1.sizeHS
step 3: if T, then append (s1.row1) to result;
if F, then do nothing
step 4: s1.row2
step 5: is it true or false that any s2.sizeHS < s1.row2.sizeHS
step 6: if T, then append (s1.row1) to result;
if F, then do nothing
step 7: s1.row3
step 8: ……

因此运算执行次数为len(s1.row)

同样的：
not in 与 not exists

select sID, sName, sizeHS
from student s1
where sID not in (select s2.sID from student s2, student s1 where s2.sizeHS > s1.sizeHS);

select sID, sName, sizeHS
from student s1
where not exists (select * from student s2 where s2.sizeHS < s1.sizeHS);

皆return如下结果

678 Fay 200

not in 主要判断s1中的sID是否与所有()中得到的，所有s1.sizeHS大于其他所有s1.sizeHS的结果s2.ID，如果不匹配，则return改行。

not exists则将s1中的每一行一一代入()中的条件，当判断为False，则return改行。

过程如下：
step 1：s1.rowN
step 2: is it T or F that any s2.sizeHS < s1.rowN.sizeHS?
step 3: if T, do nothing;
if F, return rowN