A common table expression permits defining a result table with a table-name that can be specified as a table name in any FROM clause of the fullselect that follows.
>>-table-name--+---------------------------+--------------------> | .-,-----------. | | V | (1) | '-(----column-name-+--)-----' >--AS--(--fullselect--)----------------------------------------><
Multiple common table expressions can be specified following the single WITH keyword. Each common table expression specified can also be referenced by name in the FROM clause of subsequent common table expressions.
If a list of columns is specified, it must consist of as many names as there are columns in the result table of the fullselect. Each column-name must be unique and unqualified. If these column names are not specified, the names are derived from the select list of the fullselect used to define the common table expression.
The table-name of a common table expression must be different from any other common table expression table-name in the same statement (SQLSTATE 42726). If the common table expression is specified in an INSERT statement the table-name cannot be the same as the table or view name that is the object of the insert (SQLSTATE 42726). A common table expression table-name can be specified as a table name in any FROM clause throughout the fullselect. A table-name of a common table expression overrides any existing table, view or alias (in the catalog) with the same qualified name.
If more than one common table expression is defined in the same statement, cyclic references between the common table expressions are not permitted (SQLSTATE 42835). A cyclic reference occurs when two common table expressions dt1 and dt2 are created such that dt1 refers to dt2 and dt2 refers to dt1.
If the fullselect of a common table expression contains a data-change-table-reference in the FROM clause, the common table expression is said to modify data. A common table expression that modifies data is always evaluated when the statement is processed, regardless of whether the common table expression is used anywhere else in the statement. If there is at least one common table expression that reads or modifies data, all common table expressions are processed in the order in which they occur, and each common table expression that reads or modifies data is completely executed, including all constraints and triggers, before any subsequent common table expressions are executed.
The common table expression is also optional before to the fullselect in the CREATE VIEW and INSERT statements.
If the fullselect of a common table expression contains a reference to itself in a FROM clause, the common table expression is a recursive common table expression. Queries using recursion are useful in supporting applications such as bill of materials (BOM), reservation systems, and network planning.
The following must be true of a recursive common table expression:
The FROM clauses of these fullselects can include at most one reference to a common table expression that is part of a recursion cycle (SQLSTATE 42836).
A warning is issued if this syntax is not found in the recursive common table expression (SQLSTATE 01605).