双重NOT EXISTS经典分析

CREATE TABLE  J(JNO VARCHAR(5) NOT NULL PRIMARY KEY,
                 JNAME VARCHAR(20) NOT NULL,
                 LEADER VARCHAR(10),
                 BG INT)

CREATE TABLE  SPJ(SNO VARCHAR(5) NOT NULL,
                 PNO VARCHAR(5) NOT NULL,
                 JNO VARCHAR(5) NOT NULL,
                 QTY INT,
CONSTRAINT PR_SPJ PRIMARY KEY(SNO,PNO,JNO))


CREATE TABLE P(PNO VARCHAR(5) NOT NULL PRIMARY KEY,
                 PNAME VARCHAR(20) NOT NULL,
                 SPEC VARCHAR(20),
                 CITY NVARCHAR(20);
                 COLOR NVARCHAR(2))
以上是创建的三个表
   找出使用供应商S2供应的全部零件的工程号。
SELECT JNO
FROM J 
WHERE NOT EXISTS
     (SELECT *
      FROM SPJ X
      WHERE X.SNO=’S2’ AND NOT EXISTS
                           (SELECT * 
                              FROM SPJ Y
                              WHERE Y.JNO=J.JNO AND Y.PNO=X.PNO))

网上有一些关于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的内查询语句返回的结果集的含义是, 某一个供货商不能供应所有的货物. 而连起来使用,就是用排除法得到"没有不能供应的货物的某一供货商", 也就是能供应所有货物的供货商.

        深度分析:从上面的分析可知,第一个NOT EXTSTS的作用是逐行扫描供应商表S的供应商与货物表P的所有商品(元组)进行匹配,这样匹配的结果检查出“没有不能供应所有货物的某一供应商”。那么我们能否这样设想:直接用2个EXISTS找出提供了所有货物的供应商。也就是将语句改为:
SELECT DISTINCT S.SNAME
FROM S
WHERE EXISTS
        (SELECT * 
         FROM P
         WHERE EXISTS
                 (SELECT *
                  FROM SP
                  WHERE SP.S#=S.S#
                  AND SP.P#=P.P#) );
        这样改的结果:会将S1,S2这2个供应商都选择出来。为什么?在这里,需要解释一下EXISTS和NOT EXIST对于查询结果的不同处理。同样对于S的第一行,第二个EXISTS的子查询返回结果(1,1),(1,2),可以加入第一个EXISTS的子查询结果,同样可以加入外查询结果,外查询返回S1;关键是对于S的第二行,第二个EXISTS的子查询返回(2,1),null,这样的结果能否加入第一个EXISTS的子查询结果?答案是肯定的,也就是在EXISTS的子查询结果中是可以包含是空行的,只要其中有非空行就可以加入父查询结果;但是NOT EXISTS是不可以包含空行的,只要其中有空行就不能加入父查询结果。实际上,从语义上也很好理解:“存在”只要有就表明存在;“不存在”全部都没有才能表明不存在。所以这个双重EXISTS查询的结果实际上是:找出所有提供了零件的供应商的供应名。

相关推荐
©️2020 CSDN 皮肤主题: Age of Ai 设计师:meimeiellie 返回首页