在 SELECT 裡面用 subquery

想再聊一下 SELECT 裡的 subquery，特別是內層查詢可以引用外層查詢的資料。看起來很簡單，其實也沒那麼直觀。我們再深入一點聊聊這個主題吧...

在 SELECT 裡用 subquery，可以加上額外的計算欄位，或是根據其他紀錄或資料表的資料來產生新欄位。舉例來說，你可以查出學生清單，顯示他們的平均分數、修了幾門課，或是目前小組的最高分。這很方便，因為你可以「即時」分析資料，直接產生彙總欄位，不用先處理好資料。

SELECT 裡 subquery 的基本用法

在看範例之前，先來看一下基本語法。SELECT 裡的 subquery 長這樣：

SELECT column1,
       column2,
       (SELECT 聚合_或_條件 FROM 另一個_資料表 WHERE 條件) AS 新欄位名稱
FROM 主要_資料表;

注意，subquery 只會回傳一個值，這個值會變成結果集裡的新欄位。而且 條件 可以引用 主要_資料表 的欄位。

範例 1：加上學生的平均分數

先來個簡單又實用的查詢：我們有一個 students 資料表，還有一個 grades 資料表，裡面存學生的分數。

students 資料表：

grades 資料表：

現在我們想查出學生名單，還有他們的平均分數。這時就可以在 SELECT 裡用 subquery：

SELECT
    s.id,
    s.name,
    (SELECT AVG(g.grade) 
     FROM grades g 
     WHERE g.student_id = s.id) AS average_grade
FROM students s;

結果：

這裡的 subquery (SELECT AVG(g.grade) FROM grades g WHERE g.student_id = s.id) 會算出每個學生的平均分數。每一筆 students 的資料都會對應一個值，這樣就不用 JOIN 或先做 VIEW 了，超方便。

範例 2：算每個學生修幾門課

現在來加點資料：我們想知道每個學生修了幾門課。這時有另一個資料表：

enrollments 資料表：

查出學生清單，還有他們修的課數：

SELECT
    s.id,
    s.name,
    (SELECT COUNT(*)
     FROM enrollments e
     WHERE e.student_id = s.id) AS course_count -- 指到外層 students 資料表
FROM students s;

結果：

這個 subquery (SELECT COUNT(*) FROM enrollments e WHERE e.student_id = s.id) 會算出每個學生在 enrollments 裡的紀錄數。

subquery 裡的資料聚合

很常在 SELECT 裡用 subquery 來算聚合資料。像 AVG、SUM、COUNT、MAX、MIN 這些函數，都可以直接在其他查詢裡用。

範例 3：學生的總分

來加個總分給每個學生。這時 subquery 會把 grades 裡所有分數加起來：

SELECT
    s.id,
    s.name,
    (SELECT SUM(g.grade)
     FROM grades g
     WHERE g.student_id = s.id) AS total_grade
FROM students s;

結果：

這個 subquery (SELECT SUM(g.grade) FROM grades g WHERE g.student_id = s.id) 會把每個學生的分數加總。如果學生沒分數，結果會是 NULL，因為 SUM 沒資料時會回傳 NULL。

限制與建議

1. 效能問題。 SELECT 裡的 subquery 會對主要資料表的每一筆資料都執行一次。資料量大時會很慢。有機會的話，建議用 JOIN 或先算好聚合資料。像這樣：

SELECT
    s.id,
    s.name,
    g.total_grade
FROM students s
LEFT JOIN (
    SELECT student_id, SUM(grade) AS total_grade
    FROM grades
    GROUP BY student_id
) g ON s.id = g.student_id;

這種 JOIN 的寫法比較有效率，因為聚合和計算只做一次。

2. NULL 的問題。

如果 subquery 沒資料，結果會是 NULL。這有時會讓人意外。舉例：

SELECT
    s.id,
    s.name,
    (SELECT SUM(g.grade)
     FROM grades g
     WHERE g.student_id = s.id) AS total_grade
FROM students s;

如果某個學生在 grades 沒紀錄，total_grade 就會是 NULL。想把 NULL 換成 0，可以用 COALESCE：

SELECT
    s.id,
    s.name,
    COALESCE((SELECT SUM(g.grade)
              FROM grades g
              WHERE g.student_id = s.id), 0) AS total_grade
FROM students s;

沒錯，這裡 COALESCE 的第一個參數就是

(
    SELECT SUM(g.grade)
    FROM grades g
    WHERE g.student_id = s.id
)

優化 SELECT 裡的 subquery

想避免重複計算、提升效能，可以這樣做：

1. 在 subquery 用到的欄位加 index。像 grades 的 student_id 加 index，查詢會快很多。
2. 能用 JOIN 或先算好的聚合資料，就不要用 subquery。
3. 用 WHERE 過濾，減少 subquery 處理的資料量。

最後一個範例：subquery 大集合

來把剛剛學的都用上，寫個查詢：查出學生名字、平均分、課程數、總分：

SELECT
    s.id,
    s.name,
    (SELECT AVG(g.grade)
     FROM grades g
     WHERE g.student_id = s.id) AS average_grade,
    (SELECT COUNT(*) 
     FROM enrollments e 
     WHERE e.student_id = s.id) AS course_count,
    (SELECT SUM(g.grade)
     FROM grades g
     WHERE g.student_id = s.id) AS total_grade
FROM students s;

這個查詢會回傳學生的完整 profile，全部靠 subquery 算出來。我們可以看到平均分、總分，還有修幾門課。這種寫法超適合快速拿到聚合資訊，不用另外建 VIEW 或 JOIN。