上一篇聊到巢狀查詢的問題：難讀、難維護、難改。
我們也提到，如果能像變數一樣，把子查詢存起來、取個名字，之後直接引用就好了。
這篇就來介紹 SQL 裡面做這件事的語法：WITH。
單純用別名不夠
上一篇的巢狀結構裡，我們已經有用 AS 給子查詢取別名：
FROM (
...
) AS 三年級自然科班級但這個別名是寫在子查詢的「後面」。
整段子查詢還是得塞進 FROM 或 JOIN 裡面，結構還是很亂。
我們需要的是：預先把子查詢寫好、取好名字，之後在主查詢裡直接引用。
WITH 語法的結構
WITH 就是做這件事的語法。
它永遠寫在整個查詢的最前面，讓你預先定義子查詢的別名。
如果你寫過其他程式語言，WITH 就像是 let 或 var，用來宣告變數。
let 變數名稱 = 值對應到 SQL：
WITH 別名 AS (子查詢)WITH 就像 let，AS 就像 =，後面的子查詢就是要存進去的值。
基本結構長這樣：
WITH 別名 AS (
子查詢內容
)
-- 主查詢
SELECT ...
FROM 別名WITH 區塊定義子查詢的別名，後面接主查詢。
主查詢可以直接用前面定義好的別名。
如果有多個子查詢，用逗號隔開：
WITH
別名一 AS (
子查詢一的內容
),
別名二 AS (
子查詢二的內容
)
-- 主查詢
SELECT ...
FROM 別名一
LEFT JOIN 別名二 ON ...注意：最後一個子查詢的括號後面不要加逗號，直接接主查詢。
用 WITH 改寫上一篇的查詢
還記得上一篇那個很恐怖的巢狀結構嗎？
SELECT 三年級自然科班級.班級ID
FROM (
SELECT 授課班級.班級ID, 課程.學生上限
FROM 授課班級
LEFT JOIN 課程 ON 授課班級.課程ID = 課程.課程ID
WHERE 課程.年級 = 3 AND 課程.科目 = '自然'
) AS 三年級自然科班級
LEFT JOIN (
SELECT 授課班級.班級ID, COUNT(選課.選課ID) AS 學生人數
FROM 授課班級
LEFT JOIN 選課 ON 授課班級.班級ID = 選課.班級ID
GROUP BY 授課班級.班級ID
) AS 班級學生人數
USING (班級ID)
WHERE 學生人數 < 學生上限現在用 WITH 改寫。
第一步，把第一個子查詢拉出來：
WITH
三年級自然科班級 AS (
SELECT 授課班級.班級ID, 課程.學生上限
FROM 授課班級
LEFT JOIN 課程 ON 授課班級.課程ID = 課程.課程ID
WHERE 課程.年級 = 3 AND 課程.科目 = '自然'
),第二步，把第二個子查詢也拉出來：
WITH
三年級自然科班級 AS (
SELECT 授課班級.班級ID, 課程.學生上限
FROM 授課班級
LEFT JOIN 課程 ON 授課班級.課程ID = 課程.課程ID
WHERE 課程.年級 = 3 AND 課程.科目 = '自然'
),
班級學生人數 AS (
SELECT 授課班級.班級ID, COUNT(選課.選課ID) AS 學生人數
FROM 授課班級
LEFT JOIN 選課 ON 授課班級.班級ID = 選課.班級ID
GROUP BY 授課班級.班級ID
)注意：第一個子查詢後面有逗號，第二個子查詢後面沒有逗號。
第三步，加上主查詢：
WITH
三年級自然科班級 AS (
SELECT 授課班級.班級ID, 課程.學生上限
FROM 授課班級
LEFT JOIN 課程 ON 授課班級.課程ID = 課程.課程ID
WHERE 課程.年級 = 3 AND 課程.科目 = '自然'
),
班級學生人數 AS (
SELECT 授課班級.班級ID, COUNT(選課.選課ID) AS 學生人數
FROM 授課班級
LEFT JOIN 選課 ON 授課班級.班級ID = 選課.班級ID
GROUP BY 授課班級.班級ID
)
SELECT 三年級自然科班級.班級ID
FROM 三年級自然科班級
LEFT JOIN 班級學生人數 USING (班級ID)
WHERE 學生人數 < 學生上限主查詢變得非常簡單：從「三年級自然科班級」LEFT JOIN「班級學生人數」，篩選出學生人數小於學生上限的。
一眼就看懂在做什麼。
WITH 方便除錯
改成 WITH 之後，除錯也變得容易。
假設查詢結果不如預期，需要確認某個子查詢的結果是否正確。
使用巢狀結構時，必須先找到那段子查詢的範圍，確認括號的起點和終點，然後把整段複製出來，另外開一個查詢視窗執行。
確認完之後，如果需要查看另一個子查詢，又要重複一次這個過程。
用 WITH 的話，子查詢都已經獨立出來，各自有名字。
要確認某個子查詢的結果，只需要把主查詢暫時換掉：
WITH
三年級自然科班級 AS (
SELECT 授課班級.班級ID, 課程.學生上限
FROM 授課班級
LEFT JOIN 課程 ON 授課班級.課程ID = 課程.課程ID
WHERE 課程.年級 = 3 AND 課程.科目 = '自然'
),
班級學生人數 AS (
SELECT 授課班級.班級ID, COUNT(選課.選課ID) AS 學生人數
FROM 授課班級
LEFT JOIN 選課 ON 授課班級.班級ID = 選課.班級ID
GROUP BY 授課班級.班級ID
)
-- 原本的主查詢
-- SELECT 三年級自然科班級.班級ID
-- FROM 三年級自然科班級
-- LEFT JOIN 班級學生人數 USING (班級ID)
-- WHERE 學生人數 < 學生上限
-- 暫時換成這行，確認「三年級自然科班級」的結果
SELECT * FROM 三年級自然科班級執行之後就能看到「三年級自然科班級」這個子查詢的結果。
想看另一個子查詢，只需要把最後一行換成 SELECT * FROM 班級學生人數。
不需要複製貼上任何程式碼，也不需要另外開查詢視窗。
確認完之後，把主查詢換回來即可。
有些開發者會故意多定義一些子查詢的別名，即使後面的主查詢不一定用到，也先保留在 WITH 區塊裡。
這樣做的目的是方便日後除錯，隨時可以單獨查看任何一個子查詢的結果。
如果考量效能，之後再把不需要的別名註解掉即可。
縮排的建議
你可能注意到，我把別名放在行的最前面，子查詢的內容往後縮排。
這樣做的好處是：你一眼就能看到有哪些別名。
WITH
三年級自然科班級 AS (
...
),
班級學生人數 AS (
...
)別名「三年級自然科班級」和「班級學生人數」都在最左邊，非常清楚。
另外，我習慣在 WITH 區塊和主查詢之間空一行，這樣可以清楚區分「定義別名的區塊」和「主查詢」。
這些縮排和空行不是語法要求，純粹是為了好讀。
但好讀這件事非常重要，未來你自己和同事都會感謝你。
CTE 的好處
這種用 WITH 預先定義子查詢的寫法，有一個專有名詞叫 CTE（Common Table Expression）。
CTE 有幾個好處：
好讀
別名都在最前面，主查詢的結構一目了然。
不需要在層層括號中尋找子查詢的範圍。
好維護
未來要修改某個子查詢的邏輯，直接在 WITH 區塊修改即可。
不需要在巢狀結構中尋找對應的位置。
好除錯
前面提過，子查詢都獨立出來了。
要確認某個子查詢的結果是否正確，只需要把主查詢暫時換成 SELECT * FROM 別名。
可以重複引用
如果主查詢中需要用到同一個子查詢兩次，用 CTE 只要定義一次，即可重複引用。
不需要複製貼上同樣的子查詢，也不會有漏改的問題。
小結
這篇介紹了 WITH 語法和 CTE：
WITH 語法讓你預先定義子查詢：
把子查詢寫在最前面，給它取個名字，之後在主查詢裡直接引用。
CTE 讓你的 SQL 好讀、好維護、好除錯：
別名都在最前面，主查詢的結構一目了然。
要改、要除錯都很方便。
縮排和空行讓結構更清楚：
別名放最左邊，子查詢內容縮排，WITH 區塊和主查詢之間空一行。
寫 SQL 的時候，請盡量用 CTE 的方式來寫。
功能和效能不會有差別，但可讀性會好非常多。