专题：SQL之EXISTS 供应商和学生考试 的解说为例

专题：SQL之EXISTS

以下面的供应商的解说为例：进行说明EXISTS用法

（例一）.设供应商供应零件的关系模式为SP(Sno,Pno,Qty),

其中Sno表示供应商号，Pno表示零件号，Qty表示零件数量。整个数据库如下表：
Sno Pno Qty
168  r1  3
168  r2  4
168  r3  7
169  r2  1
169  r3  5
170  r4  8
171  r7  5
172  r2  1
172  r7  3
（A）请问下面的SQL语句返回值是什么？
Select * from SP SPY where exists (select * from SP SPZ where Sno = ‘168’)
解析：在exists 中的子查询在这个例子中只返回一个值。因为从子查询中返回的行数至少有一行exits返回为true,这使得表中的所有记录都被显示了出来。

结果如下：

168  r1  3
168  r2  4
168  r3  7
169  r2  1
169  r3  5
170  r4  8
171  r7  5
172  r2  1
172  r7  3

（B）请问下面的SQL语句返回值是什么？
Select * from SP SPY where exists (select * from SP SPZ where Sno=’168’ and SPZ.Pno=SPY.Pno);
解析：返回与168号供应商所提供零件相同编号的情况。

168  r1  3
168  r2  4
168  r3  7
169  r2  1
169  r3  5
172  r2  1

（C）若想得到与168号供应上所提供零件相同的全部供应商的全部产品情况？
解析：Select * from SP where Sno in (select Sno from SP where Pno in (select Pno from SP where Sno=’168’));
或者：Select * from SP SPX where exists (select * from SP SPY where SPY.Sno=’168’ and exists (select * from SP SPZ where SPZ.Sno=SPX.Sno and SPZ.Pno=SPY.Pno));

SQL之EXISTS

 

      exists是用来判断是否存在的，当exists(查询)中的查询存在结果时则返回真，否则返回假。not exists则相反

      它所在的查询属于相关子查询，即子查询的查询条件依赖于外层父查询的某个属性值，处理过程一般为：

      取外层查询的第一个元组，根据它与内层查询相关的属性值处理内层查询，若where子句返回true，则将此元组放入结果表，然后取下一个元组，重复这个过程直到全部检查完为止

      即：exists做为where 条件时，是先对where 前的主查询进行查询，然后用主查询的结果一个一个的代入exists的子查询进行判断，如果为真则输出当前这一条主查询的结果，否则不输出

      建立程序循环的概念，这是一个动态的查询过程。如 FOR循环 。

      流程为首先取出外层中的第一个元组， 再执行内层查询，将外层表的第一元组代入，若内层查询为真，即有结果时，返回外层表中的第一元 组，接着取出第二元组，执行相同的算法。一直到扫描完外层整表 。

 

for(int i =0; i<EOFout;i++)

{

            for (int j = 0 ; j<EOFin;j++)

 }

 

 

      使用EXISTS，首先检查主查询，然后运行子查询直到它找到第一个匹配项，这就节省了时间。

      而执行IN子查询时，首先执行子查询，并将获得的结果列表存放在一个加了索引的临时表中。在执行子查询之前，系统先将主查询挂起，待子查询执行完毕，存放在临时表中以后再执行主查询。

      因此，使用EXISTS比使用IN通常查询速度快。

 

（一）解说

例一 A

 Select * from SP SPY where exists (select * from SP SPZ where Sno = ‘168’);

要将他看成

Select * from SP SPY

where exists (select * from SP SPZ where  SPZ .Sno = ‘168’);

按照流程可知：

先去取外层主查询相关表SPY的第一个元组（168  r1  3），

再执行内查询，将 SPY的第一个元组（168  r1  3）作为一个已知的限制条件带入；

下面看内查询：

select * from SP SPZ where  SPZ .Sno = ‘168’；

发现内查询 只和 子查询相关表SPZ有关，

而和SPY的第一个元组（168  r1  3）  没有任何关系，也就是说主查询对子查询没有限制。

  那么此时若内层查询为真，即有结果时，返回外层表中的第一元 组，

通过已知的整个数据库数据发现，内查询语句肯定有值，即exists返回true，

故将外层表中的第一元 组 放入结果表。

 

接着取出第二元组，执行相同的算法（由于外循环对内循环没有影响，而内循环始终为true，所以第二元组 也肯定放到 结果表中）。

一直到扫描完外层整表 。（发现 外层表所有记录 都放入到了 结果表中）

 

（二）解说

          例一 B

 

Select * from SP SPY where exists (select * from SP SPZ where Sno=’168’ and SPZ.Pno=SPY.Pno);

将之看成

Select * from SP SPY

where exists (select * from SP SPZ

where   SPZ .Sno=’168’

and  SPZ.Pno=SPY.Pno );

 

发现较之 例一 A 内循环多了一个 限制条件 SPZ.Pno=SPY.Pno，

也就是说 在内循环中  引入了 外层主查询相关表SPY的零件号Pno 。

将 SPY的第一个元组（168  r1  3）带入内循环。

 

由select * from SP SPZ where SPZ .Sno =’168’

知内表SPZ查询到三条记录

168  r1  3
168  r2  4
168  r3  7

从上面知道 SPZ.Pno的值在集合（r1,r2,r3）中

 

SPY的第一个元组（168  r1  3）的SPY.Pno=r1，在SPZ.Pno的集合（r1,r2,r3）中，

然后将外层表中的第一元 组 放入结果表。

 

SPY的第二个元组（168  r2  4）的SPY.Pno=r2，在SPZ.Pno的集合（r1,r2,r3）中，

然后将外层表中的第二元 组 放入结果表。

 

SPY的第三个元组（168  r3  7）的SPY.Pno=r3，在SPZ.Pno的集合（r1,r2,r3）中，

然后将外层表中的第三元 组 放入结果表。

 

SPY的第四个元组（169  r2  1）的SPY.Pno=r3，在SPZ.Pno的集合（r1,r2,r3）中，

然后将外层表中的第四元 组 放入结果表。

 

SPY的第五个元组（169  r3  5）的SPY.Pno=r3，在SPZ.Pno的集合（r1,r2,r3）中，

然后将外层表中的第五元 组 放入结果表。

 

SPY的第六个元组（170  r4  8）的SPY.Pno=r4，不在SPZ.Pno的集合（r1,r2,r3）中，  将外层表中的第六元 组   丢弃。

 

SPY的第七个元组（171  r7  5）的SPY.Pno=r7，不在SPZ.Pno的集合（r1,r2,r3）中，  将外层表中的第七元 组   丢弃。

 

SPY的第八个元组（172  r2  1）SPY.Pno=r2，在SPZ.Pno的集合（r1,r2,r3）中，

然后将外层表中的第八元 组 放入结果表。

 

SPY的第九个元组（172  r7  3）的SPY.Pno=r7，不在SPZ.Pno的集合（r1,r2,r3）中，  将外层表中的第九元 组   丢弃。

 

（三）解说

例一 C

若想得到与168号供应上所提供零件相同的全部供应商的全部产品情况？

1>要想知道全部供应商的全部产品，必须知道全部供应商的供应商号。

2>要想知道全部供应商号，必须知道 全部供应商的零件号

3>要想知道全部的零件号，必须知道 168号供应商的全部零件号。

 

解析：

Select * from SP where Sno

 in

    (select Sno from SP where Pno

in

(select Pno from SP where Sno=’168’));

即如下：
Select * from SP SPX where SPX .Sno

in

(select SPY .Sno from SP SPY where SPY .Pno

in

(select SPZ .Pno from SP SPZ where SPZ .Sno=’168’));

然后将 外层的限制条件 逐层内移，用exists代替in：

1>

Select * from SP SPX where exists

 (select SPY .Sno from SP SPY where SPY.Sno=SPX.Sno

and  SPY .Pno  in

(select SPZ .Pno from SP SPZ where SPZ .Sno=’168’));

2>

Select * from SP SPX where

exists (select SPY .Sno from SP SPY where SPY.Sno=SPX.Sno

and

exists (select SPZ .Pno from SP SPZ where SPZ .Sno=’168’

and  SPZ.Pno=SPY.Pno)  );

 

由于第二个exists（即内exists）需要第一个exists中的SPY.Pno

所以要将之改成

Select * from SP SPX where

exists (select * from SP SPY where SPY.Sno=SPX.Sno

and

exists (select * from SP SPZ where SPZ .Sno=’168’

and  SPZ.Pno=SPY.Pno)  );

 

3>重新组合后，就如答案所示

Select * from SP SPX where

exists (select * from SP SPY where SPY.Sno=’168’

and

exists (select * from SP SPZ where

SPZ.Sno=SPX.Sno

and  

SPZ.Pno=SPY.Pno));

 

 

第一个 exists存在的意义就是 取 168号供应商的全部零件号，作为第二个exists的限制条件。

 

（例二）下面是另一个例子

1、查询选修了全部课程的学生姓名

 

 

思路一（in）

      首先学生的选课信息存在于SC表中， 要想知道某个学生是否选修了全部课程，至少我们需要知道一共有几门课程。

      其次，学生选修了与否，我们又要扫描SC全表，统计出选修了所有课程的学生号

      最后在STUDENT表中根据学生号打出姓名

 

select Sname from student             

where Sno IN

   (select Sno from SC

group by Sno  //根据Sno分组，统计每个    

/ /学生选修了几门课程。如果等于course的

//总数，就是我们要找的Sno

    having count(*) =

             (select count(*) from course )

   )    //统计course中共有几门课程

 

思路二（EXISTS）

解二：
select sname from student where

not exists (select * from course where  

not exists (select * from sc where

sc.cno = course.cno

and sc.sno=student.sno)  )

exists做为where 条件时，是先对where 前的主查询询进行查询，然后用主查询的结果一个一个的代入exists的查询进行判断，如果为真则输出当前这一条主查询的结果，否则不输出。

上面这个列子，先查询出student表的结果，然后将结果代入到student.sno然后再查询出sc表中的结果，再一条一条的代入，感觉有点像for的嵌套循环，第一轮外循环中，满足内部的not exists条件的course表中的结果集被保留，然后再判断外部的not exists，这次判断是根据内部Course表中被保留的结果集的情况，如果结果集为空，则输出当前这一条S表的结果集；然后再进行第二轮大的循环，即取出student表的第二条结果代入运算。

以上的sql还可以这样理解，

最内部的

select * from sc where sc.cno = course.cno and sc.sno=student.sno

是查询出所有已经选择过课程的学生及相应课程，

select * from course where   not exists 则是所有没有被选择的课程，

在这个基础上的 select sname from student where not exists

则是选取所有没有未选择课程的学生，即选择了所有课程的学员名称。

 

 

      引入：将题目换为 查找学号为 00003 没有选修的科目  

      思路：

      将已知学号00003代入

      把每一个科目代入（循环）

      将上述两个条件组合，一一与SC表中的学号（ 00003 ）和科目进行比对，找不到匹配的就是00003 没有选修的科目

 

select Cname from course

where not exists//找不到的组合，提交course

        (select * from SC where course.cno = cno        and sno = ''00003'')  //在SC中匹配

 

      换个题目： 查找没有 没有选修科目的学生姓名

      思路：学号未知 ， 科目未知，说明有两个未知变量。应该有两个EXISTS。

      可以扫描student 和 course共有 s * c 中组合，将这些组合与SC的每一项进行匹配，注意s*c组合已经包含所有可能。如果全部找到 ，就说明已经选修了全部课程。找不到就说明有课程没选修 。再将没选修的的提交给上一exists 循环 。若上一exists 不存在的再提交给外循环。

 

select Sname from student

     where NOT exists        //

            (select * from course

             where NOT exists //不存在的提 

//交给course

                     (select * from SC where

                Sno = student.sno                                             and cno = Course.Cno)  // 代//入两个未知变量

        )

      回头看，就是我们第一个引出的题目:

      选修了全部课程的学生姓名

      “选了全部课程的学生” 变成“找这样的学生 ：不存在一门课他没选的，也就是没有课程他没有选

      举一反三,请写出

      被全部学生都选的课程

 

select Cname from sc

where cno  in

 (select cno from coure

group by cno//根据cno分组，统计每个    

/ /课程有几个学生选修。如果等于student的

//总数，就是我们要找的Sno

       having count(*) = (select count(*) from  student )

)

 

      被全部学生都不选的课程

 

select Cname from sc

where cno  in

 (select cno from coure

group by cno//根据cno分组，统计每个    

/ /课程有几个学生选修。如果等于student的

//总数，就是我们要找的Sno

       having count(*) = 0

)

 

2、查询至少选修了学生00002选修的全部课程的学生号码

3、 它表示的语义为：不存在这样的课程y，学生95002选了，而学生x没有选

 

select distinct sno

from sc scx

where

not exists(--------------不存在如下情况

      

select *-------------------课程y，学生95002选了

       from sc scy

       where sno='95002'

 

       and

not exist(-------------------但学生x 没有选 课程y

              select *

              from sc scz

              where scz.sno = scx.sno

              and scz.cno=scy.cno))  

 

 

SQL语句嵌套与EXISTS谓词

首先我们要了解一下SQL语句的执行过程。

SELECT 字段
FROM 表名
WHERE 条件表达式

那它们是按什么顺序执行呢？分析器会先看语句的第一个词，当它发现第一个词是SELECT关键字的时候，它会跳到FROM关键字，然后通过FROM关键字找到表名并把表装入内存。接着是找WHERE关键字，如果找不到则返回到SELECT找字段解析，如果找到WHERE，则分析其中的条件，完成后再回到SELECT分析字段。最后形成一张我们要的虚表。

其它的先不说了，只说WHERE。

WHERE关键字后面的是条件表达式。如果学过C语言等编程语言就会知道，条件表达式计算完成后，会有一个返回值，即非0或0，非0即为真(true)，0即为假(false)。同理WHERE后面的条件也有一个返回值，真或假，来确定接下来执不执行SELECT。

例:
SELECT *
FROM STUDENT
WHERE SNO = '1';

分析器先找到关键字SELECT，然后跳到FROM关键字将STUDENT表导入内存，并通过指针p1找到第一条记录，接着找到WHERE关键字计算它的条件表达式，如果为真那么把这条记录装到一个虚表当中，p1再指向下一条记录。如果为假那么p1直接指向下一条记录，而不进行其它操作。一直检索完整个表，关把虚表返回给用户。

再说EXISTS谓词，EXISTS谓词也是条件表达式的一部分。当然它也有一个返回值(true或false)。

例:
SELECT Sname
FROM Student
WHERE EXISTS
(SELECT *
FROM SC
WHERE SC.Sno = Student.Sno AND SC.Cno = '1');

这是一个SQL语句的嵌套使用，但和上面说的SQL语句的执行过程也是相同的。嵌套的意思也就是说当分析主SQL语句(外面的那个SELECT，我们权且先这么叫它)到WHERE关键字的时候，又进入了另一个SQL语句中。那么也就是说，分析器先找到表Student并装入内存，一个指针(例如p1)指向Student表中的第一条记录。然后进入WHERE里分析里面的SQL语句，再把SC表装入内存，另一个指针(例如p2)指向SC表中的第一条记录，分析WHERE后面的条件表达式，依次进行分析，最后分析出一个虚表2，也就变成

SELECT Sname
FROM Student
WHERE EXISTS 虚表2

如果虚表为空表，EXISTS 虚表2 也就为false，不返回到SELECT，而p1指向下一条记录。如果虚表2不为空也就是有记录，那么EXISTS 虚表2 为true同，返回到SELECT并把p1指向的记录添加到主SQL语句的虚表1当中。（这也是为什么嵌套的SQL语句SELECT 后面为一般为*的原因，因为它EXISTS返回的只是真或假，字段的名没有意义，用*就行，当然用别的也不会错。 ）

注意，这里虽然嵌套的SQL语句分析完了，但主SQL语句只执行了一遍，也就是说p1指向Student的第一条记录，p1还要再指向Student表的下一条记录并分析，这样又进入了嵌套中的SQL语句，同上面说的一样分析。当p1也到了Student表的结尾，整个SQL语句结束。返回虚表1Sname这一列。

嵌套就像:

for(int i = 0,i < n, ++i)
for(int j = 0, j < n, ++j)