Debian Codegraph @

doc/sql.extensions/README.common_table_expressions

raw
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