Question { Thomson Reuters, 7764 }
CTE(common table expression)
Answer
A common table expression (CTE) is a temporary result set
that is defined within the execution scope of a single
SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A
CTE is similar to a derived table in that it is not stored
as an object and lasts only for the duration of the query.
Unlike a derived table, a CTE can be self-referencing and
can be referenced multiple times in the same query.
A CTE can be used to:
1. Create a recursive query.
2. Substitute for a view when the general use of a view is
not required; that is, you do not have to store the
definition in metadata.
3. Reference the resulting table multiple times in the same
statement.
4. Enable grouping by a column that is derived from a scalar
subselect, or a function that is either not deterministic or
has external access.