`WITH` clause

The WITH clause allows you to specify generic table expressions (CTE). Regular (non-recursive) common table expressions are essentially views that are limited to the scope of a particular query. CTEs can reference each other and be nested. Recursive CTEs can refer to themselves.

Syntax

Subquery

[ WITH
       <cte_name1> [ ( <cte_column_list> ) ] AS ( SELECT ...  )
   [ , <cte_name2> [ ( <cte_column_list> ) ] AS ( SELECT ...  ) ]
   [ , <cte_nameN> [ ( <cte_column_list> ) ] AS ( SELECT ...  ) ]
]
SELECT ...

Recursive CTE:

[ WITH [ RECURSIVE ]
    <cte_name1> ( <cte_column_list> ) AS ( anchorClause UNION ALL recursiveClause)
 [, <cte_name2> ( <cte_column_list> ) AS ( anchorClause UNION ALL recursiveClause)]
 [, <cte_nameN> ( <cte_column_list> ) AS ( anchorClause UNION ALL recursiveClause)]
]
SELECT ...

Where:

anchorClause ::=
    SELECT <anchor_column_list> FROM ...

recursiveClause ::=
    SELECT <recursive_column_list> FROM ... [ JOIN ... ]

Parameters

<cte_name1> , <cte_nameN>
CTE Name.

<cte_column_list>
Column names in the CTE (common table expression).

<anchor_column_list>
The columns used in the anchor expression for the recursive CTE. The columns in this list must match the columns defined in <cte_column_list>.

<recursive_column_list>
The columns used in the recursive expression for the recursive CTE. The columns in this list must match the columns defined in <cte_column_list>.

Examples of basic CTEs

Let’s create a CTE named cte and use it in a basic query:

WITH cte AS (SELECT 33 AS amount)
    SELECT * FROM cte;

+--------+
| amount |
+--------+
| 33     |
+--------+

Let’s create two CTEs cte1 and cte2, where the second CTE refers to the first CTE:

WITH
    cte1 AS (SELECT 33 AS i),
    cte2 AS (SELECT i * 2 AS amount_doubled FROM cte1)
SELECT * FROM cte2;

+----------------+
| amount_doubled |
+----------------+
| 66             |
+----------------+

You can specify names for the CTE columns:

WITH my_cte(amount) AS (SELECT 33 AS i)
SELECT * FROM my_cte;

+--------+
| amount |
+--------+
| 33     |
+--------+