WITH - AnalyticDB for MySQL - Alibaba Cloud Documentation Center

When you use a SELECT statement, you can use the WITH clause to create a common table expression (CTE). A CTE is a temporary result set that you can reference within a SELECT statement. A CTE can be used to flatten nested queries or simplify subqueries. This way, a subquery is executed only once in the SELECT statement, which improves query performance. This topic describes how to use the WITH clause in a SELECT statement.

Usage notes

You can specify SQL statements or other CTEs in the wake of a CTE. You can specify only a single WITH clause in a query. You must separate multiple CTEs with commas (,). Otherwise, the CTEs are invalid.
Paging is not supported in a CTE.

How to use WITH

Use WITH in an alternative statement of SELECT

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

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

Use WITH to contain multiple subqueries

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;

Reference a subquery for other subqueries that are in the wake of the subquery 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;

CTE execution optimization

The CTE execution optimization feature is supported for AnalyticDB for MySQL clusters of V3.1.9.3 and later. By default, this feature is disabled. You can specify the cte_execution_mode parameter to enable this feature. If a CTE subquery is referenced for multiple times after this feature is enabled, the subquery can be executed only once to improve the performance of specific queries.

Important

The CTE execution optimization feature may cause degraded performance for specific queries. If you find a significant degradation in query performance, we recommend that you disable this feature.

Enable CTE execution optimization

Enable CTE execution optimization for a specific query

Add a hint before a specific query statement to enable CTE execution optimization for the query. Example with the built-in dataset:

/*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;

Enable CTE execution optimization for all queries
Execute the SET statement to enable CTE execution optimization for all queries. Example:
```
SET adb_config cte_execution_mode=shared;
```

Disable CTE execution optimization

Disable CTE execution optimization for a specific query

Add a hint before a specific query statement to disable CTE execution optimization for the query. Example with the built-in dataset:

/*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;

Disable CTE execution optimization for all queries
Execute the SET statement to disable CTE execution optimization for all queries. Example:
```
SET adb_config cte_execution_mode=inline;
```