CTE Syntax Use Instructions

Last updated:2025-11-28 18:24:48
CTE Syntax Use Instructions
Last updated: 2025-11-28 18:24:48
In version 5.7, TencentDB for MySQL does not support the Common Table Expression (CTE) syntax. Only the row storage engine in version 8.0 and later supports the CTE syntax. However, the read-only analysis engine supports the CTE syntax in both version 5.7 and 8.0.
Support Status
1.2404.x
In version 1.2404.x, the read-only analysis engine only supports non-recursive CTEs. When the the CTE syntax is used, Hint /*+ MERGE() */ needs to be added. Otherwise, an incorrect execution plan may be generated. In later versions, the read-only analysis engine will gradually support recursive CTEs and optimize the CTE execution performance.
2.2410.x
In version 2.2410.x, the read-only analysis engine still only supports non-recursive CTEs. However, the CTE syntax can be executed properly without needing to add Hint /*+ MERGE() */. In addition, in version 2.2410.x, streaming execution is supported, which significantly optimizes the CTE execution performance.
4.2506.x
In version 4.2506.x, the read-only analysis engine supports Recursive CTE.
Introduction to CTEs
CTEs are parts of the SQL standard and are often referred to as "WITH clauses". CTEs were first introduced in the SQL:1999 standard and designed to provide a concise and powerful method to define temporary result sets. These result sets can be referenced multiple times within a single SQL statement, greatly improving the readability and maintainability of queries.
WITH clause usage example:
-- Start defining CTE.
WITH CustomerCTE AS (
    SELECT customer_id, first_name, last_name, email_address
    FROM customer
)
-- End defining CTE.
SELECT  *
FROM CustomerCTE; -- Reference the CTE.
CTE Advantages
CTEs have many advantages in complex SQL queries.
Simplifying queries: CTEs can organize and simplify complex SQL statements and improve maintainability. For example, in scenarios in which a subquery needs to be referenced multiple times, CTEs can avoid duplicate code, making the query clearer.
Improving code readability: Meaningful names are used to represent intermediate results, making SQL statements easier to understand.
Avoiding duplicate queries: CTEs allow definition of temporary result sets, which can be referenced multiple times in a single SQL statement, avoiding repeated execution of the same operation.
Recursive queries: CTEs support recursive queries and can handle the query requirements of hierarchical data (such as the employee organizational structure). They are very useful for processing tree-structured data.
Syntax Structure
The CTE syntax structure is as follows.
with_clause:
    WITH [RECURSIVE]
        cte_name [(col_name [, col_name] ...)] AS (subquery)
        [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...
Parameter Item
Description
WITH keyword
Indicates the start of the CTE definition.
[RECURSIVE]
An optional keyword. If RECURSIVE is included, queries can reference themselves in CTEs for creating recursive queries.
cte_name
The name specified for a CTE, which can be referenced in subsequent queries.
[(col_name [, col_name] ...)]
An optional column name list, which is used to specify column names for the result sets of a CTE. If it is omitted, the column names in the subquery are used.
AS (subquery)
A subquery in a CTE, which defines the CTE content.
Comma and additional CTEs
In a WITH clause, multiple CTEs can be defined, which are separated by commas. Each additional CTE follows the same structure: cte_name [(col_name...)] AS (subquery).
Non-Recursive CTEs
A non-recursive CTE only references other tables or previously defined CTEs and will not reference itself. It is suitable for decomposing multi-step queries and gradually constructing the final query results through intermediate-level calculations.
WITH
	cte1 AS (SELECT * FROM t1, t2),
    cte2 AS (SELECT i1, i2 FROM cte1 WHERE i3 > 10)
    cte3 AS (SELECT * FROM cte2, t3 WHERE cte2.i1 = t3.i1)
SELECT * FROM cte3;
Recursive CTEs
A recursive CTE may reference itself. Recursive CTEs are commonly used for data queries involving tree or graph structures, such as calculating factorials, generating sequences, or traversing hierarchical relationships.
A recursive CTE consists of three parts: Seed Part Subquery, Union Type, and Recursive Part Subquery. The Seed Part Subquery does not reference itself, and the Recursive Part Subquery will reference itself.
WITH RECURSIVE cte(n, fact) AS (
    SELECT 0, 1 -- Seed Part Subquery
    UNION ALL -- Union Type
    SELECT n + 1, (n + 1) * fact FROM cte WHERE n < 5 -- Recursive Part Subquery
)
SELECT n, fact FROM cte;
Example
Calculating factorials
WITH RECURSIVE cte(n, fact) AS (
    SELECT 0, 1
    UNION ALL
    SELECT n + 1, (n + 1) * fact FROM cte WHERE n < 5
)
SELECT n, fact FROM cte;
In this example, the recursive part UNION ALL SELECT n + 1, (n + 1) * fact FROM cte WHERE n < 5 calls itself repeatedly until n reaches 5. When the recursive part outputs an empty row, the recursion ends.
Traversing a tree structure
For example, we have a table named employees, which contains the hierarchical relationship of employees. id is the unique identifier of an employee, name is the employee's name, and manager_id is the ID of the superior employee of this employee.
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT
);
﻿
INSERT INTO employees (id, name, manager_id) VALUES
(1, 'CEO', NULL),
(2, 'Manager 1', 1),
(3, 'Manager 2', 1),
(4, 'Employee 1', 2),
(5, 'Employee 2', 2),
(6, 'Employee 3', 3);
Recursive CTEs are used to traverse the employee hierarchy and obtain all subordinates from top to bottom.
﻿
WITH RECURSIVE employee_hierarchy AS (
    -- Basic situation: Start from the CEO.
    SELECT 
        id, 
        name, 
        manager_id, 
        1 AS level
    FROM employees
    WHERE manager_id IS NULL
﻿
    UNION ALL
﻿
    -- Recursive situation: Find the subordinates of each employee.
    SELECT 
        e.id, 
        e.name, 
        e.manager_id, 
        eh.level + 1
    FROM employees e
    INNER JOIN employee_hierarchy eh ON eh.id = e.manager_id
)
SELECT id, name, manager_id, level
FROM employee_hierarchy
ORDER BY level, manager_id;
﻿
-- Result
┌───────┬────────────┬────────────┬───────┐
│  id   │    name    │ manager_id │ level │
│ int32 │  varchar   │   int32    │ int32 │
├───────┼────────────┼────────────┼───────┤
│     1 │ CEO        │            │     1 │
│     2 │ Manager 1  │          1 │     2 │
│     3 │ Manager 2  │          1 │     2 │
│     4 │ Employee 1 │          2 │     3 │
│     5 │ Employee 2 │          2 │     3 │
│     6 │ Employee 3 │          3 │     3 │
└───────┴────────────┴────────────┴───────┘
Basic CTE
WITH CustomerCTE AS (
    SELECT customer_id, first_name, last_name, email_address
    FROM customer
)
SELECT /*+ MERGE() */ * 
FROM CustomerCTE;
Multiple CTEs
WITH 
CTE1 AS (
    SELECT customer_id, first_name, last_name, email_address
    FROM customer
),
CTE2 AS (
    SELECT ss_item_sk, ss_customer_sk, ss_sold_date_sk, ss_sales_price
    FROM store_sales
)
SELECT /*+ MERGE() */ CTE1.first_name, CTE1.last_name, CTE2.ss_sales_price
FROM CTE1
JOIN CTE2 ON CTE1.customer_id = CTE2.ss_customer_sk;
Two CTEs: CTE1 and CTE2 are defined.
In the final query, the result sets of the two CTEs are joined.
Execution Result:
+------------+-----------+----------------+
| first_name | last_name | ss_sales_price |
+------------+-----------+----------------+
| John       | Doe       |          45.99 |
| Jane       | Smith     |          32.50 |
| Michael    | Johnson   |          78.25 |
| Emily      | Brown     |          19.99 |
| David      | Wilson    |          55.00 |
| John       | Doe       |          67.75 |
| Jane       | Smith     |          22.99 |
| Michael    | Johnson   |          41.50 |
| Emily      | Brown     |          89.99 |
| David      | Wilson    |          33.25 |
+------------+-----------+----------------+
10 rows in set (0.12 sec)
Nested CTEs
WITH SalesSummary AS (
    SELECT ss_customer_sk, SUM(ss_net_paid) AS total_spent
    FROM store_sales
    GROUP BY ss_customer_sk
),
TopCustomers AS (
    SELECT ss_customer_sk, total_spent
    FROM SalesSummary
    WHERE total_consumption > 1000 -- Assume that a threshold is set, for example, for customers whose consumption exceeds 1000.
),
CustomerDetails AS (
    SELECT c.customer_id, c.first_name, c.last_name, tc.total_spent
    FROM customer c
    JOIN TopCustomers tc ON c.customer_id = tc.ss_customer_sk
)
SELECT /*+ MERGE() */ * 
FROM CustomerDetails;
SalesSummary calculates the total consumption of each customer.
TopCustomers filters out customers who have consumed more than 1000 from the SalesSummary result set.
CustomerDetails connects the customer information in the customer table with the result set in TopCustomers.
The final SELECT query extracts all data from CustomerDetails.
Execution Result:
+-------------+------------+-----------+--------------+
| customer_id | first_name | last_name | total_spent  |
+-------------+------------+-----------+--------------+
|        1001 | John       | Doe       |      1523.75 |
|        1002 | Jane       | Smith     |      2105.50 |
|        1003 | Michael    | Johnson   |      1789.99 |
|        1004 | Emily      | Brown     |      1650.25 |
|        1005 | David      | Wilson    |      1875.00 |
|        1006 | Sarah      | Davis     |      2250.75 |
|        1007 | Robert     | Taylor    |      1955.50 |
|        1008 | Jennifer   | Anderson  |      1725.25 |
|        1009 | William    | Thomas    |      2015.00 |
|        1010 | Lisa       | Jackson   |      1890.75 |
+-------------+------------+-----------+--------------+
10 rows in set (0.15 sec)
Was this page helpful?
You can also Contact Sales or Submit a Ticket for help.
Yes
Feedback
Parameter Item	Description
WITH keyword	Indicates the start of the CTE definition.
[RECURSIVE]	An optional keyword. If RECURSIVE is included, queries can reference themselves in CTEs for creating recursive queries.
cte_name	The name specified for a CTE, which can be referenced in subsequent queries.
[(col_name [, col_name] ...)]	An optional column name list, which is used to specify column names for the result sets of a CTE. If it is omitted, the column names in the subquery are used.
AS (subquery)	A subquery in a CTE, which defines the CTE content.
Comma and additional CTEs	In a WITH clause, multiple CTEs can be defined, which are separated by commas. Each additional CTE follows the same structure: cte_name [(col_name...)] AS (subquery).
tencent cloud

Support Status

Introduction to CTEs

CTE Advantages

Syntax Structure

Non-Recursive CTEs

Recursive CTEs

Example

Basic CTE

Multiple CTEs

Nested CTEs
