子查詢 Subquery

什麼是子查詢？

比喻：子查詢就像俄羅斯娃娃 🪆

打開一個查詢，裡面還有一個查詢—— 內層的查詢先執行，結果交給外層使用。

WHERE 裡的子查詢

回傳單一值

-- 找出分數高於平均的學生
SELECT Name, Score
FROM Students
WHERE Score > (                    -- ← 子查詢用括號包起來
    SELECT AVG(Score)              -- ← 這個先執行，算出平均分
    FROM Students                  -- ← 假設平均是 75
);                                 -- ← 外層變成 WHERE Score > 75

逐行解析：

WHERE Score > (                 -- 外層條件：分數要大於某個值
    SELECT AVG(Score)           -- 子查詢：先算出全班平均分（假設 75）
    FROM Students               -- 子查詢執行完畢，回傳 75
)                               -- 整句變成 WHERE Score > 75

回傳一組值（IN）

-- 找出有選「數學」的學生
SELECT Name
FROM Students
WHERE Id IN (                      -- ← IN 搭配回傳多值的子查詢
    SELECT StudentId
    FROM Enrollments
    WHERE CourseId = (
        SELECT Id FROM Courses WHERE Name = '數學'
    )
);

逐行解析：

-- 最內層先執行：找到數學的 CourseId（假設是 10）
SELECT Id FROM Courses WHERE Name = '數學'  -- → 10

-- 中間層執行：找出選了 CourseId=10 的學生 Id
SELECT StudentId FROM Enrollments WHERE CourseId = 10  -- → [1, 2]

-- 最外層執行：找出 Id 在 [1, 2] 裡的學生
SELECT Name FROM Students WHERE Id IN (1, 2)  -- → 小明, 小華

EXISTS — 檢查是否存在

-- 找出「有選過課」的學生
SELECT s.Name
FROM Students s
WHERE EXISTS (                     -- ← 只要子查詢有結果就通過
    SELECT 1                       -- ← SELECT 什麼不重要
    FROM Enrollments e
    WHERE e.StudentId = s.Id       -- ← 注意：用到了外層的 s.Id
);

逐行解析：

WHERE EXISTS (...)              -- 子查詢有回傳任何列 → true
    SELECT 1                    -- 只需要知道「有沒有」，不需要實際資料
    FROM Enrollments e
    WHERE e.StudentId = s.Id    -- 關聯子查詢：每個學生都會執行一次
                                -- 如果這個學生有選課記錄 → EXISTS = true

-- 找出「沒選過課」的學生
SELECT s.Name
FROM Students s
WHERE NOT EXISTS (
    SELECT 1
    FROM Enrollments e
    WHERE e.StudentId = s.Id
);

SELECT 裡的子查詢

-- 每個學生的分數 vs 全班平均
SELECT
    Name,
    Score,
    (SELECT AVG(Score) FROM Students) AS 全班平均,
    Score - (SELECT AVG(Score) FROM Students) AS 差距
FROM Students;

結果：

 Name | Score | 全班平均 | 差距
------+-------+---------+------
 小明 |    85 |   75.0  | 10.0
 小華 |    78 |   75.0  |  3.0
 小美 |    62 |   75.0  | -13.0

FROM 裡的子查詢（衍生表）

-- 先算出每個科系的平均分，再找出平均 > 80 的
SELECT *
FROM (
    SELECT Department, AVG(Score) AS avg_score
    FROM Students
    GROUP BY Department
) AS dept_stats                    -- ← 子查詢結果當作一張臨時表
WHERE avg_score > 80;              -- ← 外層再過濾

💡 FROM 裡的子查詢必須給別名（AS dept_stats）。

子查詢 vs JOIN

-- 子查詢寫法
SELECT Name FROM Students
WHERE Id IN (SELECT StudentId FROM Enrollments WHERE CourseId = 10);

-- JOIN 寫法（通常效能更好）
SELECT DISTINCT s.Name
FROM Students s
INNER JOIN Enrollments e ON s.Id = e.StudentId
WHERE e.CourseId = 10;

💡 能用 JOIN 就用 JOIN，效能通常更好。EXISTS 在檢查「有沒有」時效能很棒。