SQL99 中取代子查詢表格的功能 - CTE (Common Table Expression)

隨著 IBM DB2 v8,v9; Oracle9i release 2; 以及 Microsoft SQL Server 2005 相關文件出現，我注意到它們都有提供一個由 SQL:1999 (簡稱 SQL-99 或 SQL3) 所規範的 CTE (Common Table Expression) 查詢語法。 CTE 查詢語法簡化了以往「子查詢表格」的複雜語法與難讀敘述。不妨試著解讀《一個較複雜的表格聯結 (join) 範例》一文的範例，想看出裡面有四個子查詢表格確實不是件容易的事。

簡潔易讀的語法

CTE 是以 WITH 為查詢關鍵字，在主查詢進行前先以 WITH 宣告暫時性表格的內容，緊接著進行主查詢。這點與傳統的子查詢表格被夾雜在主查詢中的語法相比，在語法上顯得更為簡潔易讀。

子查詢表格語法

SELECT s.*, rooms.id FROM (SELECT id AS student_id, name FROM students) AS s
INNER JOIN rooms ON rooms.student_id = s.student_id;

Common Table Expression

WITH s (student_id, name) AS (SELECT id, name FROM students)
SELECT s.*, rooms.id FROM s
INNER JOIN rooms ON rooms.student_id = s.student_id;

查詢效能的改善

從 CTE 的語義上看，理論上對查詢效能也有所助益。當主查詢中重覆使用同一個暫時性表格時， CTE 的語義將明確指示查詢引擎只需為暫時性表格進行一次 SELECT 動作。雖然就我個人的實務經驗來看，多數資料庫系統查詢引擎已經針對這種情形進行了最佳化處理，當查詢引擎發現查詢句中有重覆的子查詢時，它實際上只會對重覆的子查詢進行一次 SELECT 動作而已。所以對查詢效能的幫助可能不會很顯著。

Recursive query

我在 PostgreSQL 的 mailing-list 中，找到了一個 CTE 做得到而子查詢表格做不到的功能，即暫時性表格的遞迴查詢 (SQL-99 Recursive query) [SQL3 recursive unions，請留意並非支援 CTE 的資料庫系統就支援遞迴查詢]。遞迴查詢是指 CTE 在宣告暫時性表格的敘述中，又參照了自己的情形。範例如下，請注意第 8 行， rpl 參照了自己。

WITH rpl (part, subpart, quantity) AS
(
    SELECT root.part, root.subpart, root.quantity
    FROM partlist AS root
    WHERE root.part = '01'
    UNION ALL
    SELECT child.part, child.subpart, child.quantity
    FROM rpl AS parent
    INNER JOIN partlist AS child
    ON parent.subpart = child.part
)
SELECT * FROM rpl;

上述範例的語法，我無法測試。 OpenSource Software 中支援 SQL 標準最好的 PostgreSQL ，一直到目前釋出的 PostgreSQL v8.2 beta 2 為止，仍未支援 CTE 。

Reference

樂多舊網址: http://blog.roodo.com/rocksaying/archives/2346392.html