Use the WITH clause to create and optimize Common Table Expressions - AnalyticDB

The WITH clause defines a common table expression (CTE) — a named, temporary result set scoped to a single SQL statement. Reference the CTE like a table in the SELECT statement that follows. CTEs simplify complex queries by replacing deeply nested subqueries with readable, reusable building blocks.

How it works

All CTEs in a WITH clause are defined before the main query runs. The subquery in the WITH clause is executed only once, which can improve query performance. With CTE execution optimization enabled, a CTE referenced multiple times executes exactly once, and all references read from that shared result.

Syntax

WITH
  cte_name AS (subquery)
  [, cte_name2 AS (subquery2) ...]
SELECT ...
FROM cte_name [, cte_name2 ...];

Separate multiple CTEs with a comma.
Each CTE must be followed by another CTE or the main SQL statement.
CTEs defined earlier in the list can be referenced by CTEs defined later.

Usage notes

Paging is not supported in CTE statements.
The WITH clause is supported in SELECT statements.

Examples

Replace a nested subquery

These two queries are equivalent. The CTE version is easier to read and maintain.

-- Nested subquery
SELECT a, b
FROM (SELECT a, MAX(b) AS b FROM t GROUP BY a) AS x;

-- Equivalent CTE
WITH x AS (SELECT a, MAX(b) AS b FROM t GROUP BY a)
SELECT a, b FROM x;

Define multiple CTEs

Use a single WITH clause to define multiple CTEs and JOIN them in the main query.

WITH
t1 AS (SELECT a, MAX(b) AS b FROM x GROUP BY a),
t2 AS (SELECT a, AVG(d) AS d FROM y GROUP BY a)
SELECT t1.*, t2.*
FROM t1 JOIN t2 ON t1.a = t2.a;

Chain CTEs

CTEs can reference earlier CTEs in the same WITH clause.

WITH
x AS (SELECT a FROM t),
y AS (SELECT a AS b FROM x),
z AS (SELECT b AS c FROM y)
SELECT c FROM z;

Recursive queries

WITH RECURSIVE cte (n) AS (
   SELECT 1
   UNION ALL
   SELECT n + 1 FROM cte WHERE n < 15 
)
SELECT * FROM cte;

By default, the recursion depth is limited to 10, which allows for 10 recursive calls. To change this limit, you can run the SET ADB_CONFIG max_recursion_depth=value command to modify the limit globally. You can also add the /*max_recursion_depth=value*/ hint to modify the limit at the query level. The `value` must be a positive integer greater than 0.

CTE execution optimization

AnalyticDB for MySQL clusters running kernel version 3.1.9.3 or later support CTE execution optimization. This feature is disabled by default. To enable this feature, you can set the CTE_EXECUTION_MODE configuration item. When enabled, a CTE subquery that is referenced multiple times is executed only once, and all references read from that shared result — avoiding redundant computation.

Important

Enabling CTE execution optimization may decrease performance for some queries. If you observe a significant performance drop, disable the optimization.

Enable CTE execution optimization

For a specific query, add the /*cte_execution_mode=shared*/ hint before the statement:

/*cte_execution_mode=shared*/
WITH shared AS (SELECT L_ORDERKEY, L_SUPPKEY FROM ADB_SampleData_TPCH_10GB.lineitem JOIN ADB_SampleData_TPCH_10GB.orders WHERE L_ORDERKEY = O_ORDERKEY)
SELECT * FROM shared s1, shared s2 WHERE s1.L_ORDERKEY = s2.L_SUPPKEY;

For all queries, run:

SET adb_config cte_execution_mode=shared;

Disable CTE execution optimization

For a specific query, add the /*cte_execution_mode=inline*/ hint before the statement:

/*cte_execution_mode=inline*/
WITH shared AS (SELECT L_ORDERKEY, L_SUPPKEY FROM ADB_SampleData_TPCH_10GB.lineitem JOIN ADB_SampleData_TPCH_10GB.orders WHERE L_ORDERKEY = O_ORDERKEY)
SELECT * FROM shared s1, shared s2 WHERE s1.L_ORDERKEY = s2.L_SUPPKEY;

For all queries, run:

SET adb_config cte_execution_mode=inline;