mysql中關於exists的深入講解
- 2020 年 4 月 11 日
- 筆記
mysql中關於exists的講解
我認為exists語法是mysql中一個很強大的工具，可以簡單地實現某些複雜的數據處理。
下面我談談與exists有關的三個方面。
all 與 any
首先，看到了exists，難免還會想到all和any，它們比exists容易理解一些。all 和 any都能讓一行數據與多行數據進行比較，這是它們的主要功能。
create table T(X int); insert into T(X) values(1),(2),(3),(4); # eg.1 select * from T where X > all( select * from T where X < 3 ); #輸出3，4 # eg.2 select * from T where X > any( select * from T where X > 1 ); #輸出3，4 先看eg.1， 顯然select * from T where X < 3結果是1，2；而all要求存在X大於集合{1,2}內的任意元素，即3,4。
同理，對於eg.2，select * from T where X > 1結果是2,3,4；any的要求是存在X大於集合{2,3,4}內的某個元素即可，即3,4。
劃分表
在說exists之前，再看看一個比較特別的語句，關於表（table）的「劃分」用法。
eg.1
# fruitTable Id Name Class Count Date 1 蘋果 水果 10 2011-7-1 1 桔子 水果 20 2011-7-2 1 香蕉 水果 15 2011-7-3 2 白菜 蔬菜 12 2011-7-1 2 青菜 蔬菜 19 2011-7-2 現在要求進行篩選，條件是Id唯一,Date選最近的一次
這種篩選條件潛藏着對於表的劃分要求。以fruitTable為例，需要劃分為2個子表，Id為1的為一個子表、Id為2的為另一個子表，再從各自子表裏面選出時間最大的那個元組。
先看看下面一個錯誤的解法
SELECT DISTINCT Id, Name, Class, Count, Date FROM fruitTable t1 WHERE (Date IN (SELECT MAX(Date) FROM fruitTable t2 GROUP BY Id)); # 結果 1 桔子 水果 20 2011-7-2 1 香蕉 水果 15 2011-7-3 2 青菜 蔬菜 19 2011-7-2 這周解法在邏輯上有漏洞。它將不同Id的最大時間混在了一起，沒有真正地劃分表格。
再來看看正確的解法
劃分表格的思路是正確的，但問題是怎麼劃分，如果另外創建2個新的table，那這樣顯然太麻煩了，於是有了下面這種寫法。
SELECT DISTINCT Id, Name, Class, Count, Date FROM fruitTable t1 WHERE (Date = (SELECT MAX(Date) FROM fruitTable t2 WHERE t2.Id=t1.Id)); 注意WHERE t2.Id=t1.Id 很巧妙地 對錶t2 基於t2.Id=t1.Id這個標準 進行了劃分。可以推導一下，比如遍歷表t1，先是第1個元組： 1 蘋果 水果 10 2011-7-1， 可以知道t1.Id=1, 帶入第2個select: (SELECT MAX(Date) FROM fruitTable t2 WHERE t2.Id=1) , 觀察這個select語句的篩選條件WHERE t2.Id=1， 發現它的範圍限定在了Id為1的元組內，聚集函數MAX(Date)返回Id為1的所有元組中Date最大的值（2011-7-3）。
因此對於表t1, 當t1.Id=1時，只有Date=2011-7-3的元組才會被選出來；而當tl.Id=2時，第2個select又變為SELECT MAX(Date) FROM fruitTable t2 WHERE t2.Id=2, 返會所有Id=2的元組中Date的最大值(2011-7-2)。
可以發現，表t2是受t1.Id控制的，根據t1.Id的不同而被劃分為不同的子表，這就是表的劃分，並且不需要另外創建新的表。
exists
先說說exists的基本用法
create table R( X int, Y varchar(5), Z varchar(5) ); create table S( Y varchar(5), Z varchar(5), Q int ); insert into R(X,Y,Z) values( 1,'a','A' ),( 1,'b','B' ),( 1,'a','B' ),( 1,'c','C' ),( 2,'a','B' ),( 2,'b','B' ),( 2,'c','A' ),( 3,'z','Z' ); insert into S(Y,Z,Q) values( 'b','B',1 ),( 'a','B',2 ); ----------------------------- select * from R where exists( select * from S where S.Y='b' and R.Y=S.Y ); # 結果 '1', 'b', 'B' '2', 'b', 'B' 對於exists可以先簡單地理解為if判斷。
比如語句select * from R where exists( select * from S where S.Y='b' and R.Y=S.Y );就可以理解為 從表R中篩選出滿足條件 S.Y=’b’ and R.Y=S.Y (select * from S where S.Y=’b’ and R.Y=S.Y) 的元組。
這個性質可以看出2個特性
- 首先exists()括號內的表不會影響最終返回的結果。比如上面的例子，返回的結果始終是關於表R的元組，和表S沒有任何關係
- 對於exists()語句，關鍵的是括號內的where子句。對於exists( select * from S where S.Y=’b’ and R.Y=S.Y ) 這種語句，可以直接當作 if( S.Y== ‘b’ and R.Y ==S.Y )。當然也不是說select不重要，比如exists( select 1 from S where S.Y=’b’ and R.Y=S.Y )是永遠為真的條件。
理清上面2點，我們就更能意識到exists非常像是一個關於條件判斷的語句。
下面例子類似
# 選了張三老師課的學生 select distinct sc.sid from sc where exists ( select * from course c,teacher t where sc.cid = c.cid and c.tid = t.tid and t.tname = "張三"); 但僅僅只有exists還不夠，因為很多其它語句也能實現這個功能，真正強大的是not exists。
找最值
SELECT DISTINCT Id, Name, Class, Count, Date FROM fruitTable t1 WHERE (Date = (SELECT MAX(Date) FROM fruitTable t2 WHERE t2.Id=t1.Id)); #用not exists SELECT DISTINCT Id, Name, Class, Count, Date FROM fruitTable t1 WHERE NOT EXISTS( SELECT * FROM fruitTable t2 WHERE t2.Id=t1.Id and t2.Date > t1.Date ); 這裡not exists同樣可以看作not if，關鍵是明白哪部分條件被否定（not）。根據之前的理論，這裡條件明顯是t2.Id=t1.Id and t2.Date > t1.Date , 而t2.Id=t1.Id不能作為否定的對象，因為這是必然存在的（自己想想，t1和t2內容一樣），用來限定表t2的範圍（即之前說的劃分子表）,再看t2.Date > t1.Date，這才是否定的部分，即對於t2中Id為t1.Id的所有元組的Date都不大於t1.Date，而此時的t1.Date也即最大值。
嵌套not exists
還有更複雜的情況，多層not exists嵌套使用。比如實現關係代數里的除法運算。
# 表R，S的定義上面已經給出 下面計算 R除以S select distinct R1.x from R R1 where not exists ( select * from S where not exists ( select * from R R2 where R1.X=R2.X and R2.Y=S.Y and R2.Z=S.Z )); 這裡有3個select，2個not exists。
最裏面的not exists是用來否定R2.Y=S.Y and R2.Z=S.Z （因為R1.X=R2.X一定成立，這個是用來劃分子表的）, 最外層的not exists就用來表示不存在這個意思，你會發現最後這個句子表達的意思就是關係代數裏面除法的定義。
