SQL 進階：JOIN、子查詢、CTE

學會了基本 CRUD 之後，我們要學習如何跨表查詢，這才是 SQL 真正強大的地方。

想像你有一本「學生名冊」和一本「成績單」，JOIN 就是把兩本冊子攤開來，用學號把資料對在一起看。

JOIN 的種類

INNER JOIN — 交集

只回傳兩邊都有對應資料的列。

-- 查詢有選課的學生及其課程名稱
SELECT s.Name, c.CourseName, e.Score  -- 選取學生姓名、課程名稱、分數
FROM Students s                        -- 從學生表開始
INNER JOIN Enrollments e               -- 內連接選課表
    ON s.Id = e.StudentId              -- 用學生 ID 對應
INNER JOIN Courses c                   -- 再連接課程表
    ON e.CourseId = c.Id;              -- 用課程 ID 對應

LEFT JOIN — 左邊全部保留

-- 查詢所有學生（包含沒選課的）
SELECT s.Name, c.CourseName            -- 選取姓名和課程
FROM Students s                        -- 左表：學生（全部保留）
LEFT JOIN Enrollments e                -- 左連接選課表
    ON s.Id = e.StudentId              -- 用學生 ID 對應
LEFT JOIN Courses c                    -- 再左連接課程表
    ON e.CourseId = c.Id;              -- 用課程 ID 對應
-- 沒選課的學生，CourseName 會顯示 NULL

RIGHT JOIN — 右邊全部保留

-- 查詢所有課程（包含沒人選的）
SELECT s.Name, c.CourseName            -- 選取姓名和課程
FROM Students s                        -- 左表：學生
RIGHT JOIN Enrollments e               -- 右連接選課表
    ON s.Id = e.StudentId              -- 用學生 ID 對應
RIGHT JOIN Courses c                   -- 再右連接課程表
    ON e.CourseId = c.Id;              -- 用課程 ID 對應
-- 沒人選的課程，Name 會顯示 NULL

FULL OUTER JOIN — 兩邊全部保留

-- 查詢所有學生和所有課程的配對
SELECT s.Name, c.CourseName            -- 選取姓名和課程
FROM Students s                        -- 左表：學生
FULL OUTER JOIN Enrollments e          -- 完全外連接選課表
    ON s.Id = e.StudentId              -- 用學生 ID 對應
FULL OUTER JOIN Courses c              -- 再完全外連接課程表
    ON e.CourseId = c.Id;              -- 用課程 ID 對應
-- 兩邊沒對應到的都會保留，缺少的部分顯示 NULL

子查詢（Subquery）

子查詢就是查詢裡面再包一個查詢，像是俄羅斯娃娃一樣。

-- 找出分數高於平均的學生
SELECT Name, Score                     -- 選取姓名和分數
FROM Students                          -- 從學生表查詢
WHERE Score > (                        -- 分數大於...
    SELECT AVG(Score) FROM Students    -- ...所有學生的平均分數
);

相關子查詢（Correlated Subquery）

每一列都會執行一次子查詢，效能較差但功能強大。

-- 找出每門課的最高分學生
SELECT s.Name, e.CourseId, e.Score     -- 選取姓名、課程 ID、分數
FROM Students s                        -- 從學生表
JOIN Enrollments e                     -- 連接選課表
    ON s.Id = e.StudentId              -- 用學生 ID 對應
WHERE e.Score = (                      -- 分數等於...
    SELECT MAX(e2.Score)               -- ...該課程的最高分
    FROM Enrollments e2                -- 從選課表
    WHERE e2.CourseId = e.CourseId     -- 條件：同一門課（這裡參考了外層查詢）
);

CTE（Common Table Expression）

CTE 用 WITH ... AS 語法，把複雜查詢拆成有名字的暫時結果集，讓 SQL 更好讀。

-- 用 CTE 計算每個學生的平均分數，再篩選及格的
WITH StudentAvg AS (                   -- 定義一個叫 StudentAvg 的暫時結果集
    SELECT                             -- 查詢內容
        StudentId,                     -- 學生 ID
        AVG(Score) AS AvgScore         -- 計算平均分數
    FROM Enrollments                   -- 從選課表
    GROUP BY StudentId                 -- 依學生分組
)
SELECT s.Name, sa.AvgScore             -- 從 CTE 和學生表選取資料
FROM StudentAvg sa                     -- 使用剛才定義的 CTE
JOIN Students s                        -- 連接學生表
    ON sa.StudentId = s.Id             -- 用學生 ID 對應
WHERE sa.AvgScore >= 60                -- 只要平均及格的
ORDER BY sa.AvgScore DESC;             -- 依平均分數降序排列

Window Functions（窗口函數）

窗口函數可以在不改變原始列數的情況下，計算排名、累計等聚合值。

-- 在每門課中計算學生排名
SELECT
    s.Name,                            -- 學生姓名
    c.CourseName,                      -- 課程名稱
    e.Score,                           -- 分數
    ROW_NUMBER() OVER (                -- 依每門課的分數排序，給予流水編號
        PARTITION BY e.CourseId        -- 依課程分組（每門課獨立排名）
        ORDER BY e.Score DESC          -- 依分數降序
    ) AS RowNum,
    RANK() OVER (                      -- RANK 遇到同分會跳號
        PARTITION BY e.CourseId        -- 同樣依課程分組
        ORDER BY e.Score DESC          -- 依分數降序
    ) AS ScoreRank,
    DENSE_RANK() OVER (                -- DENSE_RANK 遇到同分不跳號
        PARTITION BY e.CourseId        -- 依課程分組
        ORDER BY e.Score DESC          -- 依分數降序
    ) AS DenseRank
FROM Enrollments e                     -- 從選課表
JOIN Students s ON s.Id = e.StudentId  -- 連接學生表
JOIN Courses c ON c.Id = e.CourseId;   -- 連接課程表

ROW_NUMBER vs RANK vs DENSE_RANK：假設分數是 100, 95, 95, 90

• ROW_NUMBER: 1, 2, 3, 4（不管同分，流水號）
• RANK: 1, 2, 2, 4（同分同名次，跳號）
• DENSE_RANK: 1, 2, 2, 3（同分同名次，不跳號）

🤔 我這樣寫為什麼會錯？

❌ 錯誤 1：忘記 ON 條件，產生 Cartesian Join

-- ❌ 忘記 ON 條件 → 兩張表的每一列都會配對！
SELECT s.Name, c.CourseName            -- 選取姓名和課程
FROM Students s, Courses c;            -- 這會產生 M × N 列的結果！
-- 如果學生有 100 人，課程有 50 門 → 回傳 5000 列 😱

-- ✅ 正確寫法：用 JOIN + ON 指定關聯條件
SELECT s.Name, c.CourseName            -- 選取姓名和課程
FROM Students s                        -- 從學生表
JOIN Enrollments e ON s.Id = e.StudentId -- 透過選課表連接
JOIN Courses c ON e.CourseId = c.Id;     -- 再連接課程表

❌ 錯誤 2：NULL 在 JOIN 中的陷阱

-- ❌ NULL 不等於任何值（包括另一個 NULL）
-- 如果 DepartmentId 為 NULL 的學生不會出現在 INNER JOIN 結果中
SELECT s.Name, d.DeptName              -- 選取姓名和部門名稱
FROM Students s                        -- 從學生表
INNER JOIN Departments d               -- 內連接部門表
    ON s.DepartmentId = d.Id;          -- NULL != 任何值 → 這些列被排除

-- ✅ 如果要保留沒有部門的學生，用 LEFT JOIN
SELECT s.Name, ISNULL(d.DeptName, N'未分配') AS DeptName -- 用 ISNULL 處理 NULL
FROM Students s                        -- 從學生表
LEFT JOIN Departments d                -- 左連接保留所有學生
    ON s.DepartmentId = d.Id;          -- NULL 的學生也會保留

❌ 錯誤 3：在 WHERE 子句中對 LEFT JOIN 的右表篩選

-- ❌ 這等於把 LEFT JOIN 變成 INNER JOIN！
SELECT s.Name, c.CourseName            -- 選取姓名和課程
FROM Students s                        -- 從學生表
LEFT JOIN Courses c                    -- 左連接課程表
    ON s.CourseId = c.Id               -- 連接條件
WHERE c.IsActive = 1;                  -- 這會過濾掉 NULL（等於 INNER JOIN）

-- ✅ 條件放在 ON 裡面，才能保留左表所有列
SELECT s.Name, c.CourseName            -- 選取姓名和課程
FROM Students s                        -- 從學生表
LEFT JOIN Courses c                    -- 左連接課程表
    ON s.CourseId = c.Id               -- 連接條件
    AND c.IsActive = 1;               -- 篩選條件放在 ON 裡

💡 重點整理