1)子查詢概念:當一個查詢是另一個查詢的子部分時,稱之為子查詢(查詢語句中嵌套有查詢語句)。
子查詢出現的位置有:
a)目標列位置:子查詢如果位於目標列,則只能是標量子查詢,否則數據庫可能返回類似“錯誤: 子查詢必須只能返回一個字段”的提示。
b)FROM子句位置:相關子查詢出現在FROM子句中,數據庫可能返回類似“在FROM子句中的子查詢無法參考相同查詢級別中的關系”的提示,所以相關子查詢不能出現在FROM子句中;非相關子查詢出現在FROM子句中,可上拉子查詢到父層,在多表連接時統一考慮連接代價然後擇優。
c)WHERE子句位置:出現在WHERE子句中的子查詢,是一個條件表達式的一部分,而表達式可以分解為操作符和操作數;根據參與運算的不同的數據類型,操作符也不盡相同,如INT型有“>、<、=、<>”等操作,這對子查詢均有一定的要求(如INT型的等值操作,要求子查詢必須是標量子查詢)。另外,子查詢出現在WHERE子句中的格式,也有用謂詞指定的一些操作,如IN、BETWEEN、EXISTS等。
d)JOIN/ON子句位置:JOIN/ON子句可以拆分為兩部分,一是JOIN塊類似於FROM子句,二是ON子句塊類似於WHERE子句,這兩部分都可以出現子查詢。子查詢的處理方式同FROM子句和WHERE子句。
e)GROUPBY子句位置:目標列必須和GROUPBY關聯1。可將子查詢寫在GROUPBY位置處,但子查詢用在GROUPBY處沒有實用意義。
f)ORDERBY子句位置:可將子查詢寫在ORDERBY位置處。但ORDERBY操作是作用在整條SQL語句上的,子查詢用在ORDERBY處沒有實用意義。
2)子查詢的分類
從對象間的關系看:
a)相關子查詢。
子查詢的執行依賴於外層父查詢的一些屬性值。子查詢因依賴於父查詢的參數,當父查詢的參數改變時,子查詢需要根據新參數值重新執行(查詢優化器對相關子查詢進行優化有一定意義),如:
SELECT * FROM t1 WHERE col_1 = ANY
(SELECT col_1 FROM t2 WHERE t2.col_2 = t1.col_2);
/* 子查詢語句中存在父查詢的t1表的col_2列 */
b)非相關子查詢。
子查詢的執行,不依賴於外層父查詢的任何屬性值。這樣子查詢具有獨立性,可獨自求解,形成一個子查詢計劃先於外層的查詢求解,如:
SELECT * FROM t1 WHERE col_1 = ANY
(SELECT col_1 FROM t2 WHERE t2.col_2 = 10);
//子查詢語句中(t2)不存在父查詢(t1)的屬性
從特定謂詞看:
a)[NOT] IN/ALL/ANY/SOME子查詢。
語義相近,表示“[取反] 存在/所有/任何/任何”,左面是操作數,右面是子查詢,是最常見的子查詢類型之一。
b)[NOT] EXISTS子查詢。
半連接語義,表示“[取反] 存在”,沒有左操作數,右面是子查詢,也是最常見的子查詢類型之一。
c)其他子查詢。
除了上述兩種外的所有子查詢。
從語句的構成復雜程度看:
a)SPJ子查詢。
由選擇、連接、投影操作組成的查詢。
b)GROUPBY子查詢。
SPJ子查詢加上分組、聚集操作組成的查詢。
c)其他子查詢。
GROUPBY子查詢中加上其他子句如Top-N 、LIMIT/OFFSET、集合、排序等操作。
後兩種子查詢有時合稱非SPJ子查詢。
從結果的角度看:
a)標量子查詢。
子查詢返回的結果集類型是一個簡單值。
b)單行單列子查詢。
子查詢返回的結果集類型是零條或一條單元組。相似於標量子查詢,但可能返回零條元組。
c)多行單列子查詢。
子查詢返回的結果集類型是多條元組但只有一個簡單列。
d)表子查詢。
子查詢返回的結果集類型是一個表(多行多列)。
3)子查詢的優化方法
a)子查詢合並(Subquery Coalescing)
在某些條件下(語義等價:兩個查詢塊產生同樣的結果集),多個子查詢能夠合並成一個子查詢(合並後還是子查詢,以後可以通過其他技術消除掉子查詢)。這樣可以把多次表掃描、多次連接減少為單次表掃描和單次連接,如:
SELECT * FROM t1 WHERE a1<10 AND (
EXISTS (SELECT a2 FROM t2 WHERE t2.a2<5 AND t2.b2=1) OR
EXISTS (SELECT a2 FROM t2 WHERE t2.a2<5 AND t2.b2=2)
);
可優化為:
SELECT * FROM t1 WHERE a1<10 AND (
EXISTS (SELECT a2 FROM t2 WHERE t2.a2<5 AND (t2.b2=1 OR t2.b2=2)
/*兩個ESISTS子句合並為一個,條件也進行了合並 */
);
b)子查詢展開(Subquery Unnesting)
又稱子查詢反嵌套,又稱為子查詢上拉。把一些子查詢置於外層的父查詢中,作為連接關系與外層父查詢並列,其實質是把某些子查詢重寫為等價的多表連接操作(展開後,子查詢不存在了,外部查詢變成了多表連接)。帶來的好處是,有關的訪問路徑、連接方法和連接順序可能被有效使用,使得查詢語句的層次盡可能的減少。
常見的IN/ANY/SOME/ALL/EXISTS依據情況轉換為半連接(SEMI JOIN)、普通類型的子查詢消除等情況屬於此類,如:
SELECT * FROM t1, (SELECT * FROM t2 WHERE t2.a2 >10) v_t2
WHERE t1.a1<10 AND v_t2.a2<20;
可優化為:
SELECT * FROM t1, t2 WHERE t1.a1<10 AND t2.a2<20 AND t2.a2 >10;
/* 子查詢變為了t1、t2表的連接操作,相當於把t2表從子查詢中上拉了一層 */
子查詢展開的條件:
a)如果子查詢中出現了聚集、GROUPBY、DISTINCT子句,則子查詢只能單獨求解,不可以上拉到外層。
b)如果子查詢只是一個簡單格式的(SPJ格式)查詢語句,則可以上拉子查詢到外層,這樣往往能提高查詢效率。子查詢上拉,討論的就是這種格式,這也是子查詢展開技術處理的范圍。
把子查詢上拉到上層查詢,前提是上拉(展開)後的結果不能帶來多余的元組,所以子查詢展開需要遵循如下規則:
a)如果上層查詢的結果沒有重復(即SELECT子句中包含主碼),則可以展開其子查詢。並且展開後的查詢的SELECT子句前應加上DISTINCT標志。
b)如果上層查詢的SELECT語句中有DISTINCT標志,可以直接進行子查詢展開。
如果內層查詢結果沒有重復元組,則可以展開。
子查詢展開的具體步驟:
a)將子查詢和外層查詢的FROM子句連接為同一個FROM子句,並且修改相應的運行參數。
b)將子查詢的謂詞符號進行相應修改(如:“IN”修改為“=”)。
c)將子查詢的WHERE條件作為一個整體與外層查詢的WHERE條件合並,並用AND條件連接詞連接,從而保證新生成的謂詞與原舊謂詞的上下文意思相同,且成為一個整體。
c)聚集子查詢消除(Aggregate Subquery Elimination)
通常,一些系統支持的是標量聚集子查詢消除。如:
SELECT * FROM t1 WHERE t1.a1>(SELECT avg(t2.a2) FROM t2);