Hi All, CTE functionality is completed and here is the PR ( https://github.com/apache/iotdb/pull/16112).
CTEs are particularly useful for breaking down intricate queries into smaller, manageable parts, improving maintainability. It allows you to define a subquery block within a SQL statement, which can be referenced multiple times throughout the query. Unlike temporary tables, CTEs are defined within the scope of a single statement and do not persist beyond its execution, making them a lightweight and efficient tool for query structuring. The CTE syntax is as below: ------------- WITH * cte_name* [(*col_name* [, *col_name*] ...)] AS (*subquery*) [, *cte_name* [(*col_name* [, *col_name*] ...)] AS (*subquery*)] --------------- Note: we do not support recursive CTE at this moment!! Here is an example to use CTE: --------------- with x as (select avg(salary) as avg_salary from employee) select id, name from employee where salary > (select avg_salary from x); --------------- Query results from CTEs aren't materialized. Each outer reference to the named result set requires the defined query to be re-executed (Inline). The next work is the enhancement to support CTE materialization. On Wed, 13 Aug 2025 at 19:37, Yuan Tian <[email protected]> wrote: > Hi Colin, > > Glad to see that, we can discuss the feature definition like syntax for CTE > here. > > With CTE, I believe some of our complex SQL queries will become more > concise and more modular. > > > Best regards, > ------------------------ > Yuan Tian > > On Wed, Aug 13, 2025 at 7:12 PM Colin Shi <[email protected]> wrote: > > > Hi all, > > > > BTW, I'm working CTE feature recently and will update soon... > > >
