How to use the WITH clause in a SELECT statement - AnalyticDB

The WITH clause allows you to define a common table expression (CTE). A CTE is a temporary, named result set that exists only for the duration of a single SQL statement. It can be referenced by the main SELECT query that follows it. CTEs are useful for flattening nested queries or simplifying complex subqueries. This can improve both query performance and readability by allowing a subquery to be defined once and referenced multiple times.

Notes

A single WITH keyword is used to introduce one or more CTEs. If you define multiple CTEs, separate them with a comma (,).
Pagination features (like LIMIT and OFFSET) are not supported directly within a CTE definition.

Usage

The following two queries are equivalent. The version using a CTE is often easier to read and maintain.

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;

Multiple relations in a WITH clause can be joined

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, execute the SET ADB_CONFIG max_recursion_depth=value command to modify the limit at the global level. 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

This feature is available on AnalyticDB for MySQL clusters running version 3.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 multiple times after this feature is enabled, the subquery can be executed only once to improve the performance of specific queries.

Important

Enabling this optimization may degrade performance for some queries. If you observe a significant performance decrease after enabling it, we recommend disabling the feature.

Enable CTE 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;

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

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