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

Syntax

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

Parameters

<cte_name1> , <cte_nameN>
CTE Name.

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

Examples

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     |
+--------+