關聯子查詢,非關聯子查詢
子查詢雖然是一種嵌套查詢的形式,不過依然可以一句子查詢是否執行多次,進而将子查詢劃分為關聯子查詢和非關聯子查詢。
子查詢從資料表中查詢了資料結果,如果這個資料結果隻執行一次,然後這個資料結果作為主查詢條件進行執行,那麼這樣的子查詢叫做非關聯子查詢。
如果子查詢執行多次,即采用循環的方式,先從外部查詢開始,每次都傳入子查詢進行查詢,然後再将結果傳回給外部,這種嵌套的執行方式就稱為關聯子查詢。
以NBA球員資料庫為例,檔案中一共包括5張表,player表為球員,team為球隊表,team_score為球隊比賽表,player_score為球員比賽成績表,height_grades為球員身高對應的等級表。
player表,37個球員
team表,3支球隊。
team_score表,一共記錄兩場比賽
player_score表,記錄了一場比賽中球員的表現,包含19個字段
注:資料表字段比較多的時候,為了防止混淆字段,最好的方式就是做個說明文檔,用執行個體舉例。
例:shoot_attempts是總出手次數(這裡的總出手次數=二分球出手次數+三分球出手次數,不包含罰球的次數)。
示例
- 擷取球員的最高身高,最高身高是多少
SQL: SELECT player_name, height FROM player WHERE height = (SELECT max(height) FROM player)
運作結果
通過SELECT max(height) FROM player可以得到最高身高這個數值,結果為2.16,然後再通過player這個表,看誰具有這個身高,再進行輸出,這樣的子查詢就是非關聯子查詢。
如果子查詢的執行依賴于外部查詢,通常情況下都是因為子查詢中的表用到了外部的表,并進行了條件關聯,是以沒執行一次外部查詢,子查詢都要重新計算一次,這樣的子查詢就稱關聯子查詢。
示例
- 想要查找每個球隊中大于平均身高的球員有哪些,顯示他們的姓名、身高以及所在球隊ID
SELECT player_name, height, team_id FROM player AS a WHERE height > (SELECT avg(height) FROM player AS b WHERE a.team_id = b.team_id)
運作結果:(18條記錄)
這裡将player表複制成了表a和表b,每次計算的時候,需要将表a中的team_id傳入從句,作為已知值。因為每次表a中的team_id可能是不同的,是以是關聯子查詢。如果是非關聯子查詢,那麼從句計算的結果是固定的才可以。
EXISTS子查詢
關聯子查詢通常會和EXISTS一起使用,EXISTS子查詢用來判斷條件是否滿足,滿足的話為True,不滿足為False。
示例
- 檢視出場過的球員有哪些,并顯示他們的姓名、球員ID和球隊ID(是否出場通過player_score)這張表中的球員出場表現來統計,如果球員在player_score中有過出場記錄則代表他出場過)
SQL:SELECT player_id, team_id, player_name FROM player WHERE EXISTS (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id)
運作結果(19條記錄)
NOT EXISTS是不存在的意思
SQL: SELECT player_id, team_id, player_name FROM player WHERE NOT EXISTS (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id)
運作結果:(18條記錄)
集合比較子查詢
上面例子,采用IN子查詢來進行操作
SELECT player_id, team_id, player_name FROM player WHERE player_id in (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id)
運作結果和EXISTS相同
IN和EXISTS的選擇
抽象示例
SELECT * FROM A WHERE cc IN (SELECT cc FROM B)
SELECT * FROM A WHERE EXIST (SELECT cc FROM B WHERE B.cc=A.cc)
實際查詢過程中,對cc列建立索引的情況下,還需要判斷表A和表B的大小。在這個例子當中,表A指的是player表,表B指的是player_score表,如果表A表比表B大,那麼IN查詢的效率要比EXIST子查詢效率高,因為這時B表中如果對cc列進行了索引,那麼IN子查詢的效率就會比較高。
如果表A比表B小,那麼使用EXISTS子查詢效率會更高。
使用ANY和ALL需要使用比較符
示例
- 比印第安那步行者(對應的team_id為1002)中任意一個球員身高高的球員資訊,并且輸出他們的球員表ID、球員姓名和球員身高。
SQL: SELECT player_id, player_name, height FROM player WHERE height > ANY (SELECT height FROM player WHERE team_id = 1002)
運作結果
- 查詢比印第安納步行者中所有球員身高都高的球員的資訊,并且輸出球員ID、球員姓名和球員身高
SQL: SELECT player_id, player_name, height FROM player WHERE height > ALL (SELECT height FROM player WHERE team_id = 1002)
運作結果
将子查詢作為計算字段
示例
- 查詢相同team_id在player表中所有球員的數量是多少
SQL: SELECT team_name, (SELECT count(*) FROM player WHERE player.team_id = team.team_id) AS player_num FROM team
運作結果:(3條記錄)
總結