跟我學 MS SQL Server（三）：子查詢合併查詢進階 — IN、EXISTS、彙總與窗口函數替代模式

一、子查詢四大核心模式邏輯差異

二、NOT IN 陷阱實測

-- 假設 SubQuery 回傳 (1, 2, NULL)
SELECT * FROM Main WHERE ID NOT IN (SELECT Val FROM SubQuery);
-- 結果：空集合！因為 NULL 導致所有比較為 UNKNOWN

-- 修正：加 WHERE 過濾 NULL
SELECT * FROM Main WHERE ID NOT IN (SELECT Val FROM SubQuery WHERE Val IS NOT NULL);

-- 最佳：改用 NOT EXISTS
SELECT * FROM Main m
WHERE NOT EXISTS (SELECT 1 FROM SubQuery s WHERE s.Val = m.ID);

三、實戰範例：專輯/歌詞/翻唱三表關聯

1. IN：找出含特定歌詞的專輯歌曲

SELECT SongName FROM Album
WHERE BandName = 'Metallica'
AND SongName IN (
    SELECT SongName FROM Lyric
    WHERE SongLyric LIKE '%justice%'
);

2. NOT IN：找出專輯中無歌詞包含關鍵字的歌曲 (修正版)

SELECT SongName FROM Album
WHERE AlbumName = 'And Justice for All'
AND BandName = 'Metallica'
AND SongName NOT IN (
    SELECT SongName FROM Lyric
    WHERE SongLyric LIKE '%justice%'
    AND SongName IS NOT NULL  -- 關鍵修正
);

3. EXISTS：找出被翻唱樂團翻唱過的原唱歌曲

SELECT a.SongName FROM Album a
WHERE a.BandName = 'Metallica'
AND EXISTS (
    SELECT 1 FROM Cover c
    WHERE c.BandName = 'Damage, Inc.'
    AND c.SongName = a.SongName
);

4. NOT EXISTS：找出從未被翻唱的歌曲

SELECT a.SongName FROM Album a
WHERE a.BandName = 'Metallica'
AND NOT EXISTS (
    SELECT 1 FROM Cover c
    WHERE c.SongName = a.SongName
);

四、彙總函數在子查詢中的應用

驗證專輯曲目數是否完整

SELECT ai.AlbumName
FROM AlbumInfo ai
WHERE ai.AlbumTracks <> (
    SELECT COUNT(*) FROM Album a
    WHERE a.AlbumName = ai.AlbumName
    AND a.BandName = ai.BandName
);

等效 JOIN (通常更快)：

SELECT ai.AlbumName
FROM AlbumInfo ai
LEFT JOIN (
    SELECT AlbumName, BandName, COUNT(*) AS ActualTracks
    FROM Album
    GROUP BY AlbumName, BandName
) a ON ai.AlbumName = a.AlbumName AND ai.BandName = a.BandName
WHERE ai.AlbumTracks <> a.ActualTracks OR a.ActualTracks IS NULL;

五、窗口函數替代子查詢模式 (2005+)

範例：每位歌手最高銷售專輯 (子查詢 vs 窗口函數)

-- 子查詢版 (相關，慢)
SELECT BandName, AlbumName, Sales
FROM AlbumInfo ai
WHERE Sales = (
    SELECT MAX(Sales) FROM AlbumInfo WHERE BandName = ai.BandName
);

-- 窗口函數版 (單掃描，快)
WITH Ranked AS (
    SELECT BandName, AlbumName, Sales,
           ROW_NUMBER() OVER (PARTITION BY BandName ORDER BY Sales DESC) AS RN
    FROM AlbumInfo
)
SELECT BandName, AlbumName, Sales
FROM Ranked WHERE RN = 1;

六、子查詢效能優化鐵律

1. 優先 `EXISTS`/`NOT EXISTS`：語義清晰、NULL 安全、半聯結優化
2. 避免 `NOT IN` 含 NULL 子查詢：必加 `IS NOT NULL` 或改 `NOT EXISTS`
3. 大結果集 `IN` 改 `JOIN`/`EXISTS`：`IN` 可能產生大量排序/雜湊
4. 標量子查詢移出 SELECT 列表：改 `APPLY`/`JOIN`/`CTE`，避免逐列執行
5. 彙總子查詢預先分組：衍生表/CTE 先 `GROUP BY`，再聯結
6. 2005+ 優先窗口函數：`ROW_NUMBER`/`RANK`/`SUM OVER` 替代相關子查詢
7. 檢查執行計劃：尋找 `Lazy Spool`、`Table Spool`、`Nested Loops` 高估基數

七、常見錯誤修正速查

下一篇將講解 SELECT 陳述式進階選項：`ORDER BY` 多欄位排序、`DISTINCT` 去重邏輯、彙總函數 `COUNT/SUM/AVG/MIN/MAX`、`GROUP BY` 分組、`HAVING` 分組篩選、窗口函數進階應用。