子查詢在哪裡？

SQL 資料操作

Mona Khalil

Data Scientist, Greenhouse Software

什麼是子查詢？

巢狀在另一個查詢中的查詢

SELECT column
FROM (SELECT column
      FROM table) AS subquery;

適合中介轉換

子查詢能做什麼？

可出現在查詢的任一部分
- SELECT、FROM、WHERE、GROUP BY
可回傳多種資訊
- 純量（3.14159、-2、0.001）
- 清單（id = (12, 25, 392, 401, 939)）
- 資料表

為什麼用子查詢？

與彙總值比較各組
- 「利物浦當年與英超平均表現相比如何？」
重塑資料
- 「德甲單月進球數的最高月平均是多少？」
合併無法直接 JOIN 的資料
- 「如何在比賽結果表中同時取得主隊與客隊名稱？」

簡單子查詢

可獨立於外層查詢評估

SELECT home_goal
FROM match
WHERE home_goal > (
    SELECT AVG(home_goal) 
    FROM match);

SELECT AVG(home_goal) FROM match;

1.56091291478423

簡單子查詢

在整個陳述式中只會處理一次

SELECT home_goal
FROM match
WHERE home_goal > (
    SELECT AVG(home_goal) 
    FROM match);

WHERE 子句中的子查詢

2012/2013 球季中，哪些比賽的主隊進球「高於整體平均」？

SELECT AVG(home_goal) FROM match;

1.56091291478423

SELECT date, hometeam_id, awayteam_id, home_goal, away_goal
FROM match
WHERE season = '2012/2013'
      AND home_goal > 1.56091291478423;

WHERE 子句中的子查詢

2012/2013 球季中，哪些比賽的主隊進球「高於整體平均」？

SELECT date, hometeam_id, awayteam_id, home_goal, away_goal
FROM match
WHERE season = '2012/2013'
      AND home_goal > (SELECT AVG(home_goal)
                     FROM match);

| date       | hometeam_id | awayteam_id | home_goal | away_goal |
|------------|-------------|-------------|-----------|-----------|
| 2012-07-28 | 9998        | 1773        | 5         | 2         |
| 2012-07-29 | 9987        | 9984        | 3         | 3         |
| 2012-10-05 | 9993        | 9991        | 2         | 2         |

用 IN 的子查詢過濾清單

哪些球隊屬於波蘭聯賽？

SELECT
  team_long_name,
  team_short_name AS abbr
FROM team
WHERE 
  team_api_id IN 
  (SELECT hometeam_id
   FROM match
   WHERE country_id = 15722);

| team_long_name | abbr |
|----------------|------|
| Ruch Chorzów   | CHO  |
| Jagiellonia    | BIA  |
| Lech Poznań    | POZ  |
| P. Warszawa    | PWA  |
| Cracovia       | CKR  |
| Górnik Łęczna  | LEC  |
| Polonia Bytom  | GOR  |
| Zagłębie Lubin | ZAG  |
| Pogoń Szczecin | POG  |
| Widzew Łódź    | WID  |
| Śląsk Wrocław  | SLA  |

一起來練習吧！

SQL 資料操作