| 1 |
SQL Language Extension: common table expressions |
| 2 |
|
| 3 |
Author: |
| 4 |
Vlad Khorsun <hvlad at users.sourceforge.net> |
| 5 |
based on work by Paul Ruizendaal for Fyracle project |
| 6 |
|
| 7 |
|
| 8 |
Function: |
| 9 |
Common Table Expressions is like views, locally defined within main query. |
| 10 |
From the engine point of view CTE is a derived table so no intermediate |
| 11 |
materialization is performed. |
| 12 |
|
| 13 |
Recursive CTEs allow to create recursive queries. It works as below : |
| 14 |
engine starts execution from non-recursive members, |
| 15 |
for each evaluated row engine starts execution of each recursive member using |
| 16 |
current row values as parameters, |
| 17 |
if current instance of recursive member produced no rows engine returns one |
| 18 |
step back and get next row from previous resultset |
| 19 |
|
| 20 |
Memory and CPU overhead of recursive CTE is much less than overhead of |
| 21 |
recursive stored procedures. Currently recursion depth is limited by hardcoded |
| 22 |
value of 1024 |
| 23 |
|
| 24 |
|
| 25 |
Syntax: |
| 26 |
|
| 27 |
select : select_expr for_update_clause lock_clause |
| 28 |
|
| 29 |
select_expr : with_clause select_expr_body order_clause rows_clause |
| 30 |
| select_expr_body order_clause rows_clause |
| 31 |
|
| 32 |
with_clause : WITH RECURSIVE with_list |
| 33 |
| WITH with_list |
| 34 |
|
| 35 |
with_list : with_item |
| 36 |
| with_item ',' with_list |
| 37 |
|
| 38 |
with_item : symbol_table_alias_name derived_column_list AS '(' select_expr ')' |
| 39 |
|
| 40 |
select_expr_body : query_term |
| 41 |
| select_expr_body UNION distinct_noise query_term |
| 42 |
| select_expr_body UNION ALL query_term |
| 43 |
|
| 44 |
|
| 45 |
Or, in less formal format : |
| 46 |
|
| 47 |
WITH [RECURSIVE] |
| 48 |
CTE_A [(a1, a2, ...)] |
| 49 |
AS ( SELECT ... ), |
| 50 |
|
| 51 |
CTE_B [(b1, b2, ...)] |
| 52 |
AS ( SELECT ... ), |
| 53 |
... |
| 54 |
SELECT ... |
| 55 |
FROM CTE_A, CTE_B, TAB1, TAB2 ... |
| 56 |
WHERE ... |
| 57 |
|
| 58 |
|
| 59 |
|
| 60 |
Rules of non-recursive common table expressions : |
| 61 |
|
| 62 |
Several table expressions can be defined at one query |
| 63 |
Any SELECTs clause can be used in table expressions |
| 64 |
Table expressions can reference each other |
| 65 |
Table expressions can be used within any part of main query or another |
| 66 |
table expression |
| 67 |
The same table expression can be used several times in main query |
| 68 |
Table expressions can be used in INSERT, UPDATE and DELETE statements |
| 69 |
(as subqueries of course) |
| 70 |
Table expressions can be used in procedure language also |
| 71 |
WITH statements can not be nested |
| 72 |
References between expressions should not have loops |
| 73 |
|
| 74 |
|
| 75 |
Example of non-recursive common table expressions : |
| 76 |
|
| 77 |
WITH |
| 78 |
DEPT_YEAR_BUDGET AS ( |
| 79 |
SELECT FISCAL_YEAR, DEPT_NO, SUM(PROJECTED_BUDGET) AS BUDGET |
| 80 |
FROM PROJ_DEPT_BUDGET |
| 81 |
GROUP BY FISCAL_YEAR, DEPT_NO |
| 82 |
) |
| 83 |
SELECT D.DEPT_NO, D.DEPARTMENT, |
| 84 |
B_1993.BUDGET AS B_1993, B_1994.BUDGET AS B_1994, |
| 85 |
B_1995.BUDGET AS B_1995, B_1996.BUDGET AS B_1996 |
| 86 |
FROM DEPARTMENT D |
| 87 |
LEFT JOIN DEPT_YEAR_BUDGET B_1993 |
| 88 |
ON D.DEPT_NO = B_1993.DEPT_NO AND B_1993.FISCAL_YEAR = 1993 |
| 89 |
LEFT JOIN DEPT_YEAR_BUDGET B_1994 |
| 90 |
ON D.DEPT_NO = B_1994.DEPT_NO AND B_1994.FISCAL_YEAR = 1994 |
| 91 |
LEFT JOIN DEPT_YEAR_BUDGET B_1995 |
| 92 |
ON D.DEPT_NO = B_1995.DEPT_NO AND B_1995.FISCAL_YEAR = 1995 |
| 93 |
LEFT JOIN DEPT_YEAR_BUDGET B_1996 |
| 94 |
ON D.DEPT_NO = B_1996.DEPT_NO AND B_1996.FISCAL_YEAR = 1996 |
| 95 |
|
| 96 |
WHERE EXISTS (SELECT * FROM PROJ_DEPT_BUDGET B WHERE D.DEPT_NO = B.DEPT_NO) |
| 97 |
|
| 98 |
|
| 99 |
Rules of recursive common table expressions : |
| 100 |
|
| 101 |
Recursive CTE has reference to itself |
| 102 |
Recursive CTE is an UNION of recursive and non-recursive members |
| 103 |
At least one non-recursive member (anchor) must be present |
| 104 |
Non-recursive members are placed first in UNION |
| 105 |
Recursive members are separated from anchor members and from each |
| 106 |
other with UNION ALL clause |
| 107 |
References between CTEs should not have loops |
| 108 |
Aggregates (DISTINCT, GROUP BY, HAVING) and aggregate functions (SUM, |
| 109 |
COUNT, MAX etc) are not allowed in recursive members |
| 110 |
Recursive member can have only one reference to itself and only in FROM clause |
| 111 |
Recursive reference can not participate in outer joins |
| 112 |
|
| 113 |
|
| 114 |
Example of recursive common table expressions : |
| 115 |
|
| 116 |
WITH RECURSIVE |
| 117 |
DEPT_YEAR_BUDGET AS |
| 118 |
( |
| 119 |
SELECT FISCAL_YEAR, DEPT_NO, SUM(PROJECTED_BUDGET) AS BUDGET |
| 120 |
FROM PROJ_DEPT_BUDGET |
| 121 |
GROUP BY FISCAL_YEAR, DEPT_NO |
| 122 |
), |
| 123 |
|
| 124 |
DEPT_TREE AS |
| 125 |
( |
| 126 |
SELECT DEPT_NO, HEAD_DEPT, DEPARTMENT, CAST('' AS VARCHAR(255)) AS INDENT |
| 127 |
FROM DEPARTMENT |
| 128 |
WHERE HEAD_DEPT IS NULL |
| 129 |
|
| 130 |
UNION ALL |
| 131 |
|
| 132 |
SELECT D.DEPT_NO, D.HEAD_DEPT, D.DEPARTMENT, H.INDENT || ' ' |
| 133 |
FROM DEPARTMENT D JOIN DEPT_TREE H |
| 134 |
ON D.HEAD_DEPT = H.DEPT_NO |
| 135 |
) |
| 136 |
|
| 137 |
SELECT D.DEPT_NO, |
| 138 |
D.INDENT || D.DEPARTMENT AS DEPARTMENT, |
| 139 |
B_1993.BUDGET AS B_1993, |
| 140 |
B_1994.BUDGET AS B_1994, |
| 141 |
B_1995.BUDGET AS B_1995, |
| 142 |
B_1996.BUDGET AS B_1996 |
| 143 |
|
| 144 |
FROM DEPT_TREE D |
| 145 |
LEFT JOIN DEPT_YEAR_BUDGET B_1993 |
| 146 |
ON D.DEPT_NO = B_1993.DEPT_NO AND B_1993.FISCAL_YEAR = 1993 |
| 147 |
|
| 148 |
LEFT JOIN DEPT_YEAR_BUDGET B_1994 |
| 149 |
ON D.DEPT_NO = B_1994.DEPT_NO AND B_1994.FISCAL_YEAR = 1994 |
| 150 |
|
| 151 |
LEFT JOIN DEPT_YEAR_BUDGET B_1995 |
| 152 |
ON D.DEPT_NO = B_1995.DEPT_NO AND B_1995.FISCAL_YEAR = 1995 |
| 153 |
|
| 154 |
LEFT JOIN DEPT_YEAR_BUDGET B_1996 |
| 155 |
ON D.DEPT_NO = B_1996.DEPT_NO AND B_1996.FISCAL_YEAR = 1996 |