Common Table Expression (CTE)

Description

A common table expression (CTE) defines a temporary result set that a user can reference possibly multiple times within the scope of a SQL statement. A CTE is used mainly in a SELECT statement.

Syntax

WITH common_table_expression [ , ... ]

While common_table_expression is defined as

expression_name [ ( column_name [ , ... ] ) ] [ AS ] [ [ NOT ] MATERIALIZED ] ( query )

Parameters

• expression_name

  Specifies a name for the common table expression.

• MATERIALIZED, NOT MATERIALIZED

  Optionally specifies how the common table expression is evaluated. MATERIALIZED forces it to be evaluated once and shared by all references. NOT MATERIALIZED forces it to be inlined, so that each reference is planned and evaluated independently, and non-deterministic expressions such as rand() may yield different values per reference. A MATERIALIZED common table expression cannot reference columns of an outer query. MATERIALIZED is not supported in a statement whose common table expressions are always inlined, such as a multi-insert statement, nor in a subquery whose WITH clause or query references columns of an outer query. NOT MATERIALIZED is supported in both. Omit both to let Spark decide whether to inline the common table expression into its references or to evaluate it once and share the result.

• query

  A SELECT statement.

Examples

-- CTE with multiple column aliases
WITH t(x, y) AS (SELECT 1, 2)
SELECT * FROM t WHERE x = 1 AND y = 2;
+---+---+
|  x|  y|
+---+---+
|  1|  2|
+---+---+

-- CTE in CTE definition
WITH t AS (
    WITH t2 AS (SELECT 1)
    SELECT * FROM t2
)
SELECT * FROM t;
+---+
|  1|
+---+
|  1|
+---+

-- CTE evaluated once and shared by all references
WITH t AS MATERIALIZED (SELECT 1 AS x)
SELECT * FROM t JOIN t AS t2 ON t.x = t2.x;
+---+---+
|  x|  x|
+---+---+
|  1|  1|
+---+---+

-- CTE in subquery
SELECT max(c) FROM (
    WITH t(c) AS (SELECT 1)
    SELECT * FROM t
);
+------+
|max(c)|
+------+
|     1|
+------+

-- CTE in subquery expression
SELECT (
    WITH t AS (SELECT 1)
    SELECT * FROM t
);
+----------------+
|scalarsubquery()|
+----------------+
|               1|
+----------------+

-- CTE in CREATE VIEW statement
CREATE VIEW v AS
    WITH t(a, b, c, d) AS (SELECT 1, 2, 3, 4)
    SELECT * FROM t;
SELECT * FROM v;
+---+---+---+---+
|  a|  b|  c|  d|
+---+---+---+---+
|  1|  2|  3|  4|
+---+---+---+---+

WITH
    t AS (SELECT 1),
    t2 AS (
        WITH t AS (SELECT 2)
        SELECT * FROM t
    )
SELECT * FROM t2;
+---+
|  2|
+---+
|  2|
+---+

Related Statements

• SELECT