Using common table expressions

Using common table expression (CTE), you can create a temporary view that repeatedly references a subquery.

A CTE is a set of query results obtained from a simple query specified within a WITH clause that immediately precedes a SELECT or INSERT keyword. A CTE exists only within the scope of a single SQL statement and not stored in the metastore. You can include one or more CTEs in the following SQL statements:

  • SELECT

  • INSERT

  • CREATE TABLE AS SELECT

  • CREATE VIEW AS SELECT

Recursive queries are not supported and the WITH clause is not supported within subquery blocks.