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.