SQL中EXISTS的使用

网上有一些关于EXISTS 说明的例子,但都说的不是很详细.比如对于著名的供货商数据库,查询:找出供应所有零件的供应商的供应商名,对于这个查询,网上一些关于EXISTS的说明文章都不能讲清楚.

       我先解释本文所用的数据库例子,'供货商' 数据库,共3个表. 供货商表 S(S#,SNAME), 货物表 P(P#,PNAME), 供货商-货物表 SP(S#,P#).  字段S#,P#分别代表供货商和货物的ID.

       在C.J.Date的数据库系统导论第八版中文版第147页给出了, EXISTS的比较正规的解释, "EXISTS( SELECT ... FROM ...)取真值,当且仅当 SELECT ... FROM ... 取非空值.在作为相关子查询的例子中,SQL涉及子查询,因此它包含了一范围变量的引用,即隐式范围变量S, 它在外查询中定义."  

      我个人认为,此处所指的外查询定义的隐式范围变量S,  可以用另外一种方法来解释:  将外查询表的每一行,代入内查询作为检验, 如果内查询返回的结果取非空值,则EXISTS子句返回TRUE, 这一行行可作为外查询的结果行, 否则不能作为结果.

     至此可以明确,EXISTS(包括 NOT EXISTS )子句的返回值是一个BOOL值. EXISTS内部有一个子查询语句(SELECT ... FROM...), 我将其称为EXIST的内查询语句.其内查询语句返回一个结果集. EXISTS子句根据其内查询语句的结果集空或者非空,返回一个布尔值.

      举一例子说明: 找出供应所有零件的供应商的供应商名

SELECT   DISTINCT  S.SNAME
FROM  S
WHERE   NOT   EXISTS
        ( SELECT   *  
          FROM  P
          WHERE   NOT   EXISTS
                 ( SELECT   *
                   FROM  SP
                   WHERE  SP.S# = S.S#
                   AND  SP.P# = P.P#) );

假设数据如下:

S

S#	SNAME
1	S1
2	S2

 

 

 

 

 

P

P#	PNAME
1	P1
2	P2

 

 

 

 

 

SP

S#	P#
1	1
1	2
2	1

 

这个查询过程如下:

STEP1:      将S表第一行(1,S1) 作为隐式变量V1, 代入第一个NOT EXISTS子句. 由于这个子句嵌套一个NOT EXISTS子句, 再将 P表第一行(1,P1) 作为隐式变量V2, 和V1一起代入第二个NOT EXISTS子句中, 这时第二个NOT EXISTS的内查询子句变成

SELECT   *
FROM  SP
WHERE  SP.S# = 1
AND  SP.P# = 1

 其返回结果集为

S#	P#
1	1

这个返回结果集非空,注意NOT EXISTS子句返回的是EXISTS子句的非,因此 第二个NOT EXISTS 子句返回FALSE. 因此V2不能加入第一个NOT EXISTS子句的内查询子句返回结果.

同理,将P表第二行(2,P2)作为隐式变量V3, 与V1一起代入第二个NOT EXISTS子句中,内查询返回结果集非空(返回 行(1,2) ), 因此V3也不能加入第一个NOT EXISTS子句的内查询返回结果集.

至此, 对于隐式变量V1(也就是S的第一行), P表的每一行都已代入第二个NOT EXISTS子句中进行检验,返回结果是一个空集, 因此对于第一个NOT EXISTS子句,其内查询子句返回结果为空.因此,第一个NOT EXISTS子句返回TRUE.因此, V1(1,S1)加入外查询的结果集.

STEP 2:    将S表的第二行(2,S2)作为隐式变量 V4, 代入第一个 NOT EXISTS 子句. 将 V4,V2, 一起代入第二个NOT EXISTS子句. 第二个NOT EXISTS子句内查询结果集返回非空(2,1),第二个NOT EXISTS子句返回FALSE.V2 不能加入第一个NOT EXISTS子句的内查询结果集.

 将V4,V3 一起代入第二个NOT EXISTS子句,  这时第二个NOT EXISTS子句的内查询子句变成:

SELECT   *
FROM  SP
WHERE  SP.S# = 2

     AND  SP.P# = 2

 在SP表中,并没有S#=2 AND P#=2 的一行,因此,第二个NOT EXISTS子句的内查询子句返回空集,第二个NOT EXISTS子句返回 TRUE. 因此V3, 可以插入第一个NOT EXISTS子查询结果集.

至此, 对于隐式变量V4(也就是S的第2行), P表的每一行都已代入第二个NOT EXISTS子句中进行检验.第一个NOT EXISTS子查询语句返回结果集为:

P#	PNAME
2	P2

非空,因此第一个NOT EXISTS子句返回false,V4(2,S2) 不能加入外查询的结果集.

至此S表的每一行都代入第一个NOT EXISTS子句中进行检验, 外查询的返回结果是

 

SNAME

S1

 查询结束.

        从上述查询过程来可以得知, 第二个NOT EXISTS子句的内查询语句返回的结果集的含义是, 一个供货商能否供应某种货物. 第一个NOT EXISTS的内查询语句返回的结果集的含义是, 某一个供货商不能供应的货物. 而连起来使用,就是用排除法得到"没有不能供应的货物的供货商", 也就是能供应所有货物的供货商.