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.
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 forWITH
clause query names return with an error. WITH
clauses do not supportINSERT
,UPDATE
, orDELETE
statements.WITH
clauses cannot be used recursively; they can only be specified in succession.
Examples
See WITH Clauses.