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.