> For the complete documentation index, see [llms.txt](https://docs.invariant.io/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.invariant.io/platform/apache-hadoop/hive/common-table-expression.md).

# 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:

```sql
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:

```sql
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:

```sql
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:

```sql
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.