MySQL學習分享-->查詢-->子查詢

標籤：font   sql最佳化   哪些   sel   轉換   _id   user   基於   集合   

子查詢是指在查詢語句中嵌套另一個查詢語句。

子查詢外面的查詢部分被稱為外部查詢。

子查詢必須包含括弧。

 

any、in、some

any關鍵字必須與一個比較操作符一起使用，它的意思是“與子查詢中返回列的數值進行逐一對比，只要其中一個數值比較為true，則返回true”，我們來看一個查詢執行個體，

select id_temp from t_user_collect where commodity_id > any (select id from t_commodity); 得到如下結果，

 

我們通過查看兩張表的資料，來分析下為什麼會得到以上結果，t_user_collect表的資料如下，

 

t_commodity表的資料如下，

 

首先我們已經明確了any關鍵字的含義，t_user_collect表中commodity_id列會與t_commodity表中id列的數值進行逐一對比，只要commodity_id大於其中的一個id，則commodity_id列值所在行的id_temp列值就會被查詢出來。

 

從表中資料能夠看出，commodity_id列中為1的值是無法大於id列中任何一個值的，因此其（這裡是指commodity_id列）所在行的id_temp列值不會被查詢出來，而其它的commodity_id列值（6、3、4）在id列中均能找到大於的值，所以它們（這裡是指commodity_id列）關聯的id_temp列值會被查詢出來。

 

 

in關鍵字的含義是“只返回包含這些值的記錄”，它等價於“= any”，因此以下兩個SQL是等價的，

select id_temp from t_user_collect where commodity_id = any (select id from t_commodity);

select id_temp from t_user_collect where commodity_id in (select id from t_commodity);

查詢的結果為，

得到上述結果是因為commodity_id列中的值（1、6、3、4）在id列中均有包含。

為什麼說“in”和“= any”是等價的呢，我們來分析一下，“= any”的含義是“只要等於指定值中的任何一個值就返回true”，和明顯t_user_collect表中commodity_id列的值都能在t_commodity表的id列中匹配到相等的值。

 

 

some關鍵字和any關鍵字是等價的，之所以在有些條件下使用some，是為了表達“有部分不相等”的意思，因此以下SQL是等價的，

select id_temp from t_user_collect where commodity_id <> any (select id from t_commodity);

select id_temp from t_user_collect where commodity_id <> some (select id from t_commodity);

查詢結果為，

這裡怎麼理解，其實就是commodity_id列值與id列中值進行匹配時，只要有一個不相等，其（這裡是指commodity_id列）所在行的id_temp列值就會被查詢出來。

這裡的“<> any”含義是“只要不等於指定值中的任何一個值就返回true”。

這裡的“<> some”含義是“只要不等於指定值中的部分值就返回true”。

 

 

all

all關鍵字，也需要與比較操作符一起使用，其含義是“與子查詢返回列中的值進行逐一對比，所有比較都為true，才返回true”，來看一個執行個體，

select uc.id_temp from t_user_collect uc where uc.commodity_id > all (select c.id from t_commodity c);

查詢結果為，

 

由於t_user_collect表commodity_id列的最大值為6，而t_commodity表id列的最大值為9，因此每一個commodity_id列的值都無法大於所有的id列值，故結果為Null

 

當我們對all使用<>符號時，是想得到那些外部查詢中未包含在子查詢中的資料，來看一個執行個體，

select uc.id_temp from t_user_collect uc where uc.commodity_id <> all (select c.id from t_commodity c);

查詢結果為，

 

我們來理解下，只有當commodity_id列中的值不等於所有id列中的值時，其（這裡是指commodity_id列）所在行的id_temp列值才會被查詢出來，然而commodity_id列中的值與id列中的值均有匹配關係，因此查詢出來的結果為Null。

其實<> all還有一個別名，就是not in，後者明顯更易於理解，即不包含、不在裡面的意思，那麼對於剛剛的那個SQL就可以這麼理解，“看commodity_id列中值有哪些是不在id列中的”，因此以下兩個SQL是等價的，

select uc.id_temp from t_user_collect uc where uc.commodity_id <> all (select c.id from t_commodity c);

select uc.id_temp from t_user_collect uc where uc.commodity_id not in (select c.id from t_commodity c);

 

 

子查詢的分類

按照期望值的數量可以將子查詢分為：標量子查詢、多值子查詢

按照對外部查詢的依賴可以將子查詢分為：獨立子查詢、相互關聯的子查詢

它們之間的關係是標量子查詢和多值子查詢可以是獨立子查詢也可以是相互關聯的子查詢

 

標量子查詢返回的是單個值，若標量子查詢返回多個值，則MySQL會拋出錯誤提示“該子查詢返回多行資料”；多值子查詢返回的是多值集合。

獨立子查詢是不依賴於外部查詢而啟動並執行子查詢。怎麼理解呢，就是這個子查詢可以單獨運行，而不需要和外部查詢有任何互動。

MySQL對於in子句的最佳化，如果不是顯式的列表定義，如in (1,2,3,4,5)，那麼in子句一般都會被轉換為exists子句，這一過程就是把獨立子查詢轉換為了相互關聯的子查詢。

 

相互關聯的子查詢是指引用了外部查詢列的子查詢，例如下述這個SQL，

select id_temp from t_user_collect uc where exists (select * from t_commodity c where c.id=uc.commodity_id);

子查詢中的“c.id=uc.commodity_id”就是引用了外部查詢的commodity_id列，通過explain可以看到這個子查詢的select_type是dependent subquery（相互關聯的子查詢），

 

相互關聯的子查詢會對外部查詢的列進行多次比較，因此對於相互關聯的子查詢的最佳化我們應該從減少與外部查詢的多次比較入手。基於此，有時我們可以通過派生表來進行重寫SQL。那麼我們在確定是否需要對子查詢進行最佳化時，可以通過使用explain來查看SQL的執行計畫，不要被因資料量小導致SQL執行用時短的假象所蒙蔽，要儘可能的用大資料量去測試你的SQL。

 

 

EXISTS

用於檢查指定的查詢是否產生某些行，exists一般後接子查詢，且子查詢會關聯外部查詢。根據子查詢是否返回行，exists僅會返回true或false，而不會出現返回unknown的情況。

通常不建議在SQL中使用*，但是在exists中我們可以這麼做，這是因為exists只關心行是否存在，而不會去取各列的值。

 

額外知識點

“一個SQL最佳化建議，將語句中的in替換為exists”這句話是不是很耳熟？它是一個真理嗎？可能在一些老版本的MySQL上確實是有益的，但是現階段的MySQL對於in和exists在絕大多數情況下，兩者都具有相同的執行計畫，所以你會發現即便將in換成了exists效果是一樣的。因此我們對於別人總結的定理，最好還是保有一顆求證的心，讓事實來說服自己。

 

select id_temp from t_user_collect uc where commodity_id in (select id from t_commodity t);

 

select id_temp from t_user_collect uc where exists (select * from t_commodity t where t.id=uc.commodity_id);

 

 

派生表

派生表又被稱為表子查詢，這是什麼意思呢，就是說派生表和其它表一樣出現在from子句中。

那派生表是怎麼產生的呢？它來自於子查詢派生出的虛擬表。因此它是完全虛擬，並沒有被物理化地存於磁碟上。由於這個特性可能會導致對其執行explain時，需要消耗很長時間，這是因為最佳化器不知道這張表的資訊。

 

它在使用上會有一些限制，

①列的名稱必須是唯一的，以下這個SQL對於派生表是不支援的，

select id_temp from t_user_collect, (select id_temp as a, user_id_temp as a from t_user_collect) as t; MySQL會提示ERROR，“重複的列名”。

 

②在某些情況下派生表不支援使用limit，例如外部查詢在使用in、all、any、some時；

 

③每個派生表必須有自己的別名，以下這個SQL對於MySQL會提示這個ERROR，

select id_temp from t_user_collect, (select id_temp as a, user_id_temp as a from t_user_collect);

 

額外知識點

子查詢和派生表有什麼關係？

其實兩者從名稱上來看就有比較大的差別，前者是一個查詢語句，而後者是一張虛擬表。我們可以將子查詢重寫為派生表。當你確定你需要的是派生表時，那麼你就應該給它起一個別名，而對於子查詢，你則不需要這麼做。

MySQL學習分享-->查詢-->子查詢