轉載自 mydotnet
最終編輯 mydotnet

網上有一些關於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的內查詢語句返回的結果集的含義是, 某一個供貨商不能供應的貨物. 而連起來使用,就是用排除法得到"沒有不能供應的貨物的供貨商", 也就是能供應所有貨物的供貨商.

=========================

後記: 高點的資料庫都沒講那麼清楚.....終於弄懂了....

創作者介紹

Vanilla Sky

colin3703 發表在 痞客邦 PIXNET 留言(2) 人氣()


留言列表 (2)

發表留言
  • allen
  • 版大你好,
    小弟也正為這個問題困擾,對於你的說明,我理解成這樣不知對否?
    step 1:
    v1+v2,帶入第3個select查詢傳回非空值(true),第2個select not exist判斷因有值回傳,判定為false。
    第1個select not exist檢查第2個select傳回之布林值(false),因此not exist(false)成立,所以v1暫時為true。
    接著v1+v4的判斷同上,所以第1個seelct 結果v1成立。
    step 2 :
    v4+v2 判斷同上,第1個select判斷v4暫時成立;v4+v3傳入第3 select查詢,傳回空值,第2個select判斷後為not exist(空值),因此第2個select 布林值為true,再經過第1個select檢查,因此not exist(true),所以不成立v4為false。
    因此最外層查詢只會列出v1。
    不知是否正確,請版大指正,謝謝!
  • 你的想法沒錯~所以我想這裡應該是你不小心敲錯~

    接著v1+v4的判斷同上,所以第1個seelct 結果v1成立。<==應該是 v1+v3

    ^__^

    colin3703 於 2011/03/23 15:14 回覆

  • allen
  • 不小心打錯了><"
    謝謝版大的指正,我終於搞懂了,謝謝!