WITH Clause

WITH clauses are individually-evaluated SELECT statements for use in a larger container query. You can use WITH clauses to simplify complicated queries and reduce statement repetition.

WITH clauses are evaluated through inline expansion or (optionally) through materialization. For details, see WITH Clauses in SELECT.

Syntax

The following syntax statement is illustrative, rather than syntactically exact, to show the possibility of numerous successive WITH queries in use with others:

WITH... with-query-1 [(col-name[,…])]AS (SELECT…),
    with-query-2 [(col-name[,…])]AS (SELECT… [with-query-1]),
.
.
.
    with-query-n [(col-name[,…])]AS (SELECT… [with-query-1, with-query-2, with-query-n[,…]])
SELECT
.
.
.

Restrictions

• Each WITH clause query must be uniquely named. Same-name aliases for WITH clause query names return with an error.

• WITH clauses do not support INSERT, UPDATE, or DELETE statements.
• WITH clauses cannot be used recursively; they can only be specified in succession.

Examples

See WITH Clauses in SELECT.