子查詢：關(guān)聯(lián)查詢和非關(guān)聯(lián)查詢
子查詢是「查詢」中的「查詢」喇喉，就是「嵌套查詢」孤钦。
以 NBA 的SQL數(shù)據(jù)庫文件為例择葡。
檢索問題：誰是數(shù)據(jù)庫里身高最高的球員挨务？
SELECT player_name, height
FROM player
WHERE height = (SELECT max(height) FROM player);
關(guān)聯(lián)查詢
關(guān)聯(lián)查詢：對外部的表進(jìn)行了條件關(guān)聯(lián)击你。
檢索問題：查詢每支球隊中，身高大于球隊平均身高的球員谎柄？
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);
EXISTS子查詢
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);
通過判斷某個球員是否出現(xiàn)在 player_score 表中丁侄，得知該名球員是否出場。
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);
反過來朝巫，如果主表的 player_id不在 player_score 上鸿摇，就是 NOT EXIST，可以查詢未出場的球員
子查詢中的「集合比較」
集合比較的操作符關(guān)鍵詞與其英文含義一樣劈猿，見下：
| 操作符 | 說明 |
|---|---|
| IN | 判斷拙吉，是否在集合中 |
| ANY | 與比較操作符一起使用，去子查詢返回的任何值做比較 |
| ALL | 與比較操作符一起使用揪荣，去子查詢返回的所有值做比較 |
| SOME | 其實筷黔，它是 ANY 的別名 |
同樣的查詢目的，查看出場球員名單仗颈，用 IN 子查詢實現(xiàn)：
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)
用IN 替代 EXIST佛舱，查詢結(jié)果一致。
SELECT * FROM A WHERE cc IN (SELECT cc FROM B)
SELECT * FROM A WHERE EXIST (SELECT cc FROM B WHERE B.cc=A.cc)
在這里挨决，EXIST比IN查詢效率更高名眉，為什么？
因為表 player 小于 表 play_score凰棉。
在對 cc 列建索引時, 先判斷表 A 和 B誰更大。如果A更大陌粹，B 中對列 cc 進(jìn)行索引撒犀，IN 的效率更高。
SELECT player_id, player_name, height, team_id
FROM player
WHERE height > ALL (SELECT height FROM player WHERE team_id = 1002)
子查詢：計算字段
SELECT team_name, (SELECT count(*)
FROM player
WHERE player.team_id = team.team_id) AS player_num
FROM team
這里掏秩，子查詢得到的 count(*) 結(jié)果作為計算字段或舞，起別名為 player_num
