Skip to content

Feature Request: Common Table Expressions (CTEs) #30645

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Open
mesquita opened this issue Apr 3, 2025 · 2 comments
Open

Feature Request: Common Table Expressions (CTEs) #30645

mesquita opened this issue Apr 3, 2025 · 2 comments
Labels
enhancement New feature or request

Comments

@mesquita
Copy link

mesquita commented Apr 3, 2025

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

@mesquita mesquita added the enhancement New feature or request label Apr 3, 2025
@jbrass
Copy link
Contributor

jbrass commented May 15, 2025

@mesquita Thanks for the feedback. We will log this in our internal backlog and let you know if it progresses.

@andymans
Copy link

+1 for this feature.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
enhancement New feature or request
Projects
None yet
Development

No branches or pull requests

3 participants