[Database] Common Table Expression (CTE) 的用法

最近在工作上，同事告知了一個 Oracle Database 好用的功能，這個對於根本不熟 Database 的我來說，完全不了解的一個寫法。試過之後，真的算是蠻好用的，所以在這邊寫出來分享給大家。

以往我們在撰寫比較複雜且多層的查詢時，大多都是用暫存表 (Temp Table) 或是 sub-query 的方式。一開始都知道自己在寫什麼，但是一年過去、兩年過去，之後自己再回來看，有的時候已經不太懂自己為什麼要這樣寫，也忘了當初的邏輯是什麼。自己寫的都不一定記得，更不用說如果要後人來做維護的時候，更是難懂。

而 Common Table Expression 簡單來說，就是把原本的 sub-query 獨立出來，把每個 sub-query 的結果，暫時存放在記憶體當中，讓後續的 SQL Statement 可以直接呼叫使用。是用 WITH 這個當作查詢的關鍵字，這樣的特性，很適合使用在遞迴查詢 (recursive query) 的情況。對於把會重覆執行的 SQL 獨立出來，比起傳統的語法來說，不僅 SQL Statement 會比較簡潔易讀，而且在工程師的閱讀與維護上，會比較容易。也因為把子查詢獨立出來，所以只需要執行一次的查詢，不用執行多次，所以理論上，對於 SQL 的查詢效能也會有所改善。

不過要記住一點，CTE 產生出來的暫存資料，生命週期只存在當次的 SQL 裡，也就是說只能在同一個 SQL statement 裡呼叫，不能夠跨 SQL Statement 呼叫。CTE 產生的暫存資料，只要 WITH 的 SQL Statement 區塊執行完畢之後，就會自動消失。

在 WITH 關鍵字之後，開始宣告暫存資料表的 SQL，等宣告完要存在記憶體的暫存資料 SQL 之後，最後再宣告主要的 SQL Statement。不過在多層式堆疊的 SQL Statement 宣告，不同的 SQL DB 所採用的宣告方式卻不一定相同。在 Oracle SQL query 裡，從 WITH 關鍵字之後，先宣告底層 Level-1 SQL，再宣告 Level-2 SQL，最後再宣告主 SQL；不過在 Teradata 卻是相反，在 WITH 關鍵字之後，先宣告的是 Level-2 SQL，再宣告 Level-1 SQL，最後一樣是宣告主要的 SQL Statement。

以下是 CTE WITH 的語法

WITH cte_name (Column1, Column2, Column3)
AS
(
    SELECT Column1, Column2, Column3
    FROM SomeTable
)

SELECT * FROM cte