WITH CLAUSE#

OpenMLDB WITH CLAUSE support starts in 0.7.2 as an experimental feature, and may stabilized in feature release.

It is highly inspired by WITH CLAUSE in BigQuery, but only supports non recursive with clause.

A WITH clause contains one or more common table expressions (CTEs). A CTE acts like a temporary table that you can reference within a single query expression. Each CTE binds the results of a subquery to a table name, which can be used elsewhere in the same query expression, but rules apply.

Syntax#

'WITH' non_recursive_cte [, ... ]

non_recursive_cte
         ::= cte_name 'AS' '(' SelectStmt ')'

Description#

WITH clause can be treated as another form of subquery, same online request mode restriction applied for subquery and WITH clause.

SELECT Statement Elements

Offline Mode

Online Preview Mode

Online Request Mode

Note

WITH Clause

✓

✓

✓

CTE rules#

  1. CTEs can be referenced inside the query expression that contains the  WITH  clause. This means the CTEs can be referenced by the outermost query contains the WITH clause, as well as subqueries inside the outermost query.

  2. Each CTE in the same WITH clause must have a unique name.

  3. A local CTE can overrides an outer CTE or table with the same name.

Example#

  1. CTE overrides table name

    WITH t1 as (select col1 + 1 as id, std_ts from t1)
select * from t1;

  2. nested WITH clause

    WITH q1 AS (my_query)
SELECT *
FROM
  (WITH q2 AS (SELECT * FROM q1),  # q1 resolves to my_query
        q3 AS (SELECT * FROM q1),  # q1 resolves to my_query
        q1 AS (SELECT * FROM q1),  # q1 (in the query) resolves to my_query
        q4 AS (SELECT * FROM q1)   # q1 resolves to the WITH subquery on the previous line.
    SELECT * FROM q1)              # q1 resolves to the third inner WITH subquery.