SQL - NOT EXIST usage

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