WITH

A WITH clause may precede any SQL operator and has the form

WITH <alias> AS (
  <sql-op>
)
[ , <alias> AS ( <sql-op> ) ... ]

where

• <alias> is a table alias with optional columns as defined in a FROM clause, and
• <sql-op> is any SQL operator.

WITH defines one or more common-table expressions (CTE) each of which binds a name to the query body defined in the CTE.

A CTE is similar to a query declaration but the CTE body must be a SQL operator and the CTE name can be used only with a FROM clause and is not accessible in an expression.

The table aliases form a lexical scope which is available in any FROM clause defined within the SQL operator that follows the WITH clause and any FROM clauses recursively defined within that operator. Additionally, a CTE alias is available to the other CTEs that follow in the same WITH clause.

Note

SuperSQL will support recursive CTEs in a future version.

Examples

Hello world

# spq
WITH hello(message) AS (
    VALUES ('hello, world')
)
SELECT * FROM hello
# input

# expected output
{message:"hello, world"}

A first CTE referenced in a second CTE

# spq
WITH T(x) AS (
    VALUES (1), (2), (3)
),
U(y) AS (
    SELECT x+1 FROM T
)
SELECT * FROM U
# input

# expected output
{y:2}
{y:3}
{y:4}

A nested CTE reaching into its parent scope

# spq
WITH T(x) AS (
    VALUES (1), (2), (3)
)
SELECT (
    WITH U(y) AS (
        SELECT x+1 FROM T
    )
    SELECT max(y) FROM U
 ) as max
# input

# expected output
{max:4}