Common Table Expression

A common table expression, or CTE, is a set of query results obtained from a simple query specified within a WITH clause and which immediately precedes a SELECT or INSERT keyword. A CTE exists only within the scope of a single SQL statement. One or more CTEs can be used with the following SQL statements:

• SELECT

• INSERT

• CREATE TABLE AS SELECT

• CREATE VIEW AS SELECT

The following example demonstrates the use CTE in a SELECT statement:

WITH q1 AS (SELECT key from src where key = '5') SELECT * from q1;

The following example demonstrates the use of q1 as a CTE in an INSERT statement:

CREATE TABLE s1 LIKE src WITH q1 AS (SELECT key, value FROM src WHERE key = '5') 
FROM q1 INSERT OVERWRITE TABLE s1 SELECT *;

The following example demonstrates the use of ql as a CTE in a CREATE TABLE AS SELECT clause:

CREATE TABLE s2 AS WITH q1 AS (SELECT key FROM src WHERE key = '4') SELECT * FROM q1;

The following example demonstrates the use of q1 as a CTE in a CREATE TABLE AS VIEW clause:

CREATE VIEW v1 AS WITH q1 AS (SELECT key FROM src WHERE key='5') SELECT * from q1;

Limitations

Common Table Expressions have the following limitations:

• Recursive queries are not supported.

• The WITH clause is not supported within subquery blocks.