Feature Request: Common Table Expressions (CTEs)

We would love to have Common Table Expressions (CTEs).

What is a Common Table Expression (CTE)?
A Common Table Expression (CTE) is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs help to break down complex queries into more readable parts and can be referenced multiple times within the same query.

Main use cases:

• To break down complex queries into smaller, more manageable parts
• To avoid duplicating subqueries

Examples

Non-recursive CTE

WITH cte AS (SELECT number FROM numbers LIMIT 2) SELECT * FROM cte t1, cte t2;

+--------+--------+
| number | number |
+--------+--------+
|      0 |      0 |
|      0 |      1 |
|      1 |      0 |
|      1 |      1 |
+--------+--------+

If a parenthesized list of names follows the CTE name, those names are the column names:

WITH cte (col1, col2) AS
(
  SELECT 1, 2
  UNION ALL
  SELECT 3, 4
)
SELECT col1, col2 FROM cte;

The number of names in the list must be the same as the number of columns in the result set.

+------+------+
| col1 | col2 |
+------+------+
|    1 |    2 |
|    3 |    4 |
+------+------+

Join two CTEs:

WITH
  cte1 AS (SELECT number AS a FROM NUMBERS LIMIT 2),
  cte2 AS (SELECT number AS b FROM NUMBERS LIMIT 2)
SELECT * FROM cte1 JOIN cte2
ON cte1.a = cte2.b;

+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    0 |    0 |
+------+------+

Source of example: https://docs.greptime.com/reference/sql/with/#examples