Common table expressions

A common table expression is like a temporary view that is defined and used for the duration of an SQL statement.

You can define a common table expression wherever you can have a fullselect statement. For example, you can include a common table expression in a SELECT, INSERT, SELECT INTO, or CREATE VIEW statement.

Each common table expression must have a unique name and be defined only once. However, you can reference a common table expression many times in the same SQL statement. Unlike regular views or nested table expressions, which derive their result tables for each reference, all references to common table expressions in a given statement share the same result table.

You can use a common table expression in the following situations:

When you want to avoid creating a view (when general use of the view is not required, and positioned updates or deletes are not used)
When the result table is based on host variables
When the same result table needs to be shared in a fullselect
When the results need to be derived using recursion