Use the WITH clause in a SELECT statement - AnalyticDB - Alibaba Cloud Documentation Center

You can use the WITH clause to create a common table expression (CTE). A CTE is a temporary result set that is valid for the duration of a single SQL statement. A SELECT query can then reference this result set. CTEs can be used to flatten nested queries or simplify subqueries. The subquery in the WITH clause is executed only once, which can improve query performance. This topic describes how to use the WITH clause in a SELECT statement.

Notes

You can use a single WITH clause to define one or more CTEs. Separate multiple CTEs with a comma (,). A CTE must be followed by an SQL statement or another CTE.
Paging is not supported in CTE statements.

Usage

The following two queries are equivalent

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 a WITH clause for 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;

Relations defined in a WITH clause can reference each other

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

AnalyticDB for MySQL clusters that run 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 this feature is enabled, a CTE subquery that is referenced multiple times is executed only once. This can improve the performance of some queries.

Important

Enabling CTE execution optimization may decrease the performance of some queries. If you notice a significant performance decrease, you should disable the optimization feature.

Enable CTE execution optimization

Enable CTE execution optimization for a specific query

To enable CTE execution optimization for a specific query, you can add a hint before the query statement. The following example shows how to optimize a query on a 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 optimized CTE execution for all queries
You can run the SET statement to enable CTE execution optimization for all queries. The following example shows how to enable the optimization:
```
SET adb_config cte_execution_mode=shared;
```

Disable CTE execution optimization

Disable CTE optimization for a specific query

To disable CTE execution optimization for a specific query, you can add a hint before the query statement. The following example shows how to disable the optimization for a query on 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
You can run the SET statement to disable CTE execution optimization for all queries. The following example shows how to disable the optimization:
```
SET adb_config cte_execution_mode=inline;
```