美文网首页
SQL - in/exists/not in/not exist

SQL - in/exists/not in/not exist

作者: Mani曼妮 | 来源:发表于2018-03-12 14:25 被阅读17次

    有如下三个表:

    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

    相关文章

      网友评论

          本文标题:SQL - in/exists/not in/not exist

          本文链接:https://www.haomeiwen.com/subject/lvynfftx.html