Release Notes and Announcements

Release Notes

Product Announcements

Beginner's Guide

Product Introduction

Overview

Strengths

Use Cases

Architecture

Product Specifications

Instance Types

Product Feature List

Database Versions

Regions and AZs

Common Concepts

Use Limits

Suggestions on Usage Specifications

Kernel Features

Kernel Overview

Kernel Version Release Notes

Optimized Kernel Version

Functionality Features

Performance Features

Security Features

Stability Feature

Analysis Engine Features

Inspection and Repair of Kernel Issues

Purchase Guide

Billing Overview

Product Pricing

Creating Cluster

Specification Adjustment Description

Renewal

Payment Overdue

Refund

Change from Pay-as-You-Go to Yearly/Monthly Subscription

Change from Pay-as-You-Go to Serverless Billing

Value-Added Services Billing Overview

Viewing Billing Statements

Getting Started

Database Audit

Overview

Viewing Audit Instance List

Enabling Audit Service

Viewing Audit Logs

Log Shipping

Post-Event Alarm Configuration

Modifying Audit Rule

Modifying Audit Service

Disabling Audit Service

Audit Rule Template

Viewing Audit Task

Authorizing Sub-User to Use Database Audit

Serverless Service

Serverless Introduction

Creating and Managing a Serverless Cluster

Elastic Scaling Management Tool

Serverless Resource Pack

Multi-AZ Deployment

Configuration Change

FAQs

Serverless Cost Estimator

Operation Guide

Operation Overview

Switching Cluster Page View in Console

Database Connection

Instance Management

Configuration Adjustment

Instance Mode Management

Cluster Management

Scaling Instance

Database Proxy

Account Management

Database Management

Database Management Tool

Parameter Configuration

Multi-AZ Deployment

Backup and Restoration

Operation Log

Data Migration

Parallel Query

Columnar Storage Index (CSI)

Analysis Engine

Database Security and Encryption

Monitoring and Alarms

Basic SQL Operations

Connecting to TDSQL-C for MySQL Through SCF

Tag

Practical Tutorial

Classified Protection Practice for Database Audit of TDSQL-C for MySQL

Upgrading Database Version from MySQL 5.7 to 8.0 Through DTS

Usage Instructions for TDSQL-C MySQL

New Version of Console

Implementing Multiple RO Groups with Multiple Database Proxy Connection Addresses

Strengths of Database Proxy

Selecting Billing Mode for Storage Space

Creating Remote Disaster Recovery by DTS

Creating VPC for Cluster

Data Rollback

Solution to High CPU Utilization

How to Authorize Sub-Users to View Monitoring Data

White Paper

Security White Paper

Performance White Paper

Troubleshooting

Connection Issues

Performance Issues

API Documentation

History

Introduction

API Category

Making API Requests

Instance APIs

Multi-Availability Zone APIs

Other APIs

Audit APIs

Database Proxy APIs

Backup and Recovery APIs

Parameter Management APIs

Billing APIs

serverless APIs

Resource Package APIs

Account APIs

Performance Analysis APIs

Data Types

Error Codes

FAQs

Basic Concepts

Purchase and Billing

Compatibility and Format

Connection and Network

Features

Console Operations

Database and Table

Performance and Log

Database Audit

Between TDSQL-C for MySQL and TencentDB for MySQL

Service Agreement

Service Level Agreement

TDSQL-C Policy

Data Privacy and Security Agreement

General References

Standards and Certifications

Glossary

CTE Syntax Use Instructions

PDF

Focus Mode

Font Size

Last updated: 2025-12-03 09:54:32

TDSQL-C for MySQL does not support CTE syntax in version 5.7. The row storage engine only in the versions later than 8.0 supports the CTE syntax, while the read-only analysis engine in both the versions 5.7 and 8.0 can support the CTE syntax normally.
Support Status
1.2404.x
In version 1.2404.x, the read-only analysis engine only supports non-recursive CTEs. When 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.
CTE Introduction
Common Table Expressions (CTEs) are a part of the SQL standard, commonly referred to as WITH clauses. CTE was first introduced in the SQL:1999 standard, providing a concise and powerful way to define temporary result sets. These result sets can be referenced multiple times in a single SQL statement, greatly improving the readability and maintainability of queries.
WITH clause use 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 Strengths
CTEs provide numerous strengths in complex SQL queries.
Simplifying queries: CTEs can organize and simplify complex SQL statements, improving the maintainability. For example, when the same subquery needs to be referenced multiple times, CTEs can avoid redundant code, making the query clearer.
Improving code readability: Using meaningful names for intermediate results makes SQL easier to understand.
Avoiding duplicate queries: CTEs allow the definition of temporary result sets, which can be referenced multiple times in a single SQL statement, thereby avoiding the repeated execution of the same operation.
Recursive queries: CTEs support recursive queries, which can satisfy the query needs for hierarchical data (such as staff organizational structures). It is very useful for dealing with tree structure data.
Syntax Structure
The syntax structure of CTEs is as follows.
with_clause:
    WITH [RECURSIVE]
        cte_name [(col_name [, col_name] ...)] AS (subquery)
        [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...
Parameter
Description
WITH keyword
Indicates the beginning of the CTE definition.
[RECURSIVE]
Optional keyword. If RECURSIVE is contained, it indicates that self-referencing within the CTE is allowed. It is used to create recursive queries.
cte_name
The name specified for the CTE, which can be referenced in subsequent queries.
[(col_name [,col_name] ...)]
Optional column name list, which specifies the column names for the CTE result sets. If omitted, the column names in the subquery will be used.
AS (subquery)
Internal subquery of the CTE, which defines the CTE content.
Comma and additional CTEs
In a WITH clause, you can define multiple CTEs separated by commas. Each additional CTE follows the same structure cte_name [(col_name ...)] AS (subquery).
Non-Recursive CTE
In a non-recursive CTE, the CTE only references other tables or previously defined CTEs, but does not reference itself. It is suitable for decomposing multi-step queries, to build the final query result step by step through intermediate 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 CTE
In a recursive CTE, the CTE references itself. Recursive CTEs are often used for querying the tree structure or graph structure data, such as calculating factorials, generating sequences, or traversing hierarchical relationships.
A recursive CTE consists of three parts, namely Seed Part Subquery, Union Type, and Recursive Part Subquery. The Seed Part Subquery does not reference itself, while the Recursive Part Subquery must 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;
Examples
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 will repeatedly call itself until n reaches 5. The recursion ends when the recursive part outputs an empty row.
Traversing tree structures
Assuming that we have a table named employees, it contains the employee hierarchy, where id is the unique identifier of an employee, name is the employee name, and manager_id is the superior ID of the 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);
A recursive CTE is used to traverse the employee hierarchy, retrieving all subordinates from top to bottom:
﻿
WITH RECURSIVE employee_hierarchy AS (
    -- Basic situation: starting from CEO
    SELECT 
        id, 
        name, 
        manager_id, 
        1 AS level
    FROM employees
    WHERE manager_id IS NULL
﻿
    UNION ALL
﻿
    -- Recursive situation: finding each employee's subordinates
    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 are defined, namely CTE1 and CTE2.
The result sets of these two CTEs are used to perform a Join operation in the final query.
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 CTE
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_spent > 1000  -- Assume that a threshold is set, such as customer spending more than 1,000.
),
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 spending of each customer.
TopCustomers filters out customers whose spending exceeds 1,000 from the SalesSummary result set.
CustomerDetails joins the customer information in the customer table with the TopCustomers result set.
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)

Help and Support

Was this page helpful?

You can also Contact sales or Submit a Ticket for help.

Help us improve! Rate your documentation experience in 5 mins.

Feedback

Parameter	Description
WITH keyword	Indicates the beginning of the CTE definition.
[RECURSIVE]	Optional keyword. If RECURSIVE is contained, it indicates that self-referencing within the CTE is allowed. It is used to create recursive queries.
cte_name	The name specified for the CTE, which can be referenced in subsequent queries.
[(col_name [,col_name] ...)]	Optional column name list, which specifies the column names for the CTE result sets. If omitted, the column names in the subquery will be used.
AS (subquery)	Internal subquery of the CTE, which defines the CTE content.
Comma and additional CTEs	In a WITH clause, you can define multiple CTEs separated by commas. Each additional CTE follows the same structure cte_name [(col_name ...)] AS (subquery).

tencent cloud

TDSQL-C for MySQL

CTE Syntax Use Instructions

Support Status

CTE Introduction

CTE Strengths

Syntax Structure

Non-Recursive CTE

Recursive CTE

Examples

Basic CTE

Multiple CTEs

Nested CTE

Help and Support