tencent cloud

TDSQL Boundless

DocumentaçãoTDSQL BoundlessUser GuideRead-Only Analysis InstanceExecuting QueriesCTE Syntax Usage Instructions

CTE Syntax Usage Instructions

Download
Modo Foco
Tamanho da Fonte
Última atualização: 2026-07-08 17:25:19
Common Table Expressions (CTEs) are an important part of the SQL standard, used to define temporary result sets that can be referenced multiple times within a single SQL statement. TDSQL Boundless read-only analysis instances have gradually supported CTE syntax since early versions and now provide full recursive CTE and stream execution capabilities in the latest version. This document describes the support for CTEs, syntax structure, usage examples, and limitations in TDSQL Boundless read-only analysis instances.

Introduction to CTEs

CTEs were first introduced in the SQL:1999 standard and are typically defined using the WITH clause, hence they are also known as WITH clauses. A CTE defines a temporary result set within a single SQL statement and allows it to be referenced multiple times in subsequent parts of the same statement, thereby improving the readability and maintainability of complex queries.
CTEs are categorized into the following two types:
Non-Recursive CTE: A CTE that references only other tables or previously defined CTEs, not itself. It is suitable for breaking down multi-step queries into multiple intermediate results.
Recursive CTE: A CTE that references itself within its definition. It is suitable for querying hierarchical data such as tree or graph structures.
The following code provides a simple CTE example.
-- Define the CTE.
WITH CustomerCTE AS (
    SELECT customer_id, first_name, last_name, email_address
    FROM customer
)
-- Reference the CTE.
SELECT *
FROM CustomerCTE;

Support Status

The support for CTEs in TDSQL Boundless read-only analysis instances is shown in the following table.
Non-Recursive CTE
Recursive CTE
Whether Hint /*+ MERGE() */ Is Required
Streaming Execution
Supported
Supported
Not required
Supported

CTE Advantages

Using CTEs in complex SQL queries offers the following advantages.
Simplify Queries: CTEs improve the organization and maintainability of SQL by breaking down complex queries into named intermediate result sets, avoiding duplicate code when the same subquery is referenced multiple times.
Improve Readability: CTEs are used to represent intermediate results with business-meaningful names, making complex SQL easier to understand.
Avoid Redundant Computation: In materialized mode, the intermediate results of a CTE can be referenced multiple times within the same SQL statement, preventing the repeated execution of identical operations.
Support Recursive Queries: Recursive CTEs can process hierarchical data (such as organizational charts and directory trees), simplifying the logic for querying tree-structured data.

Syntax Structure

The basic syntax structure of a CTE is as follows.
with_clause:
    WITH [RECURSIVE]
        cte_name [(col_name [, col_name] ...)] AS (subquery)
        [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...
Parameters are described in the following table.
Parameter Item
Description
WITH
The keyword that begins a CTE definition.
RECURSIVE
An optional keyword. Including RECURSIVE allows the CTE to reference itself, which is used to construct recursive queries.
cte_name
The name of the CTE, which can be referenced in subsequent queries.
col_name
An optional list of column names used to specify the column names for the CTE result set. If the optional list of column names is omitted, the column names from the subquery are used.
subquery
A subquery within a CTE that defines the CTE content.
Multiple CTEs
Multiple CTEs can be defined in a single WITH clause, separated by commas.

Non-Recursive CTEs

In a non-recursive CTE, the CTE references only other tables or previously defined CTEs, not itself. It is suitable for breaking down multi-step queries into multiple intermediate results and building the final query result through layer-by-layer computation.
The basic structure of a non-recursive CTE is as follows.
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;

Single CTE

The following example uses a single non-recursive CTE to perform a simple query.
WITH CustomerCTE AS (
    SELECT customer_id, first_name, last_name, email_address
    FROM customer
)
SELECT *
FROM CustomerCTE;

Multiple CTEs

The following example defines two CTEs within the same WITH clause and uses a JOIN in the final query to associate them.
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 CTE1.first_name, CTE1.last_name, CTE2.ss_sales_price
FROM CTE1
JOIN CTE2 ON CTE1.customer_id = CTE2.ss_customer_sk;
The execution logic of this example is as follows:
Define two CTEs, CTE1 and CTE2, which read data from the customer table and the store_sales table, respectively.
In the final query, the two CTEs are joined using the customer_id field.
The execution result is as follows.
+------------+-----------+----------------+

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

+------------+-----------+----------------+
5 rows in set (0.12 sec)

Nested CTEs

The following example uses three CTEs to perform a nested query: it first aggregates sales by customer, then filters for high-spending customers, and finally joins the customer information table.
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
    ),
    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 *
FROM CustomerDetails;
The execution logic of this example is as follows:
SalesSummary calculates the total spending amount for each customer.
TopCustomers filters customers with a spending amount greater than 1000 from SalesSummary.
CustomerDetails joins the customer information from the customer table with TopCustomers.
The final query extracts all data from CustomerDetails.
The execution result is as follows.
+-------------+------------+-----------+-------------+

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

+-------------+------------+-----------+-------------+
5 rows in set (0.15 sec)

Recursive CTEs

A Recursive CTE references itself within its definition, making it suitable for querying hierarchical data, such as calculating factorials, generating sequences, or traversing tree structures.
A Recursive CTE consists of the following three parts:
Seed Part Subquery: the starting point of recursion, which does not reference itself.
Union Type: Use UNION ALL or UNION DISTINCT to connect the seed part with the recursive part.
Recursive Part Subquery: It must reference itself to generate new rows.
The basic syntax of a Recursive CTE is as follows.
WITH RECURSIVE cte(n, fact) AS (
    SELECT 0, 1                                              -- Seed part
    UNION ALL                                                -- Union type
    SELECT n + 1, (n + 1) * fact FROM cte WHERE n < 5        -- Recursive part
)
SELECT n, fact FROM cte;
Attention:
The recursion depth of a Recursive CTE is controlled by the system variable cte_max_recursion_depth, which has a default value of 1000.
When the recursion quantity exceeds cte_max_recursion_depth, the query returns an error: Recursive query aborted after N iterations. Try increasing @@cte_max_recursion_depth to a larger value.
You can adjust the maximum recursion depth by using SET cte_max_recursion_depth = N;.

Calculating Factorials

The following example uses a Recursive CTE to calculate the factorial of numbers from 0 to 5.
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;
The execution process is as follows:
The seed part outputs the initial row (0, 1).
The recursive part calculates n + 1 and (n + 1) * fact based on the result from the previous iteration, until n reaches 5.
Recursion terminates when the recursive part outputs an empty result set.

Traversing Tree Structures

The following example uses a Recursive CTE to traverse the employee hierarchy, querying all subordinates starting from the CEO.
First, create and initialize the employee employees table, where manager_id indicates the direct manager 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);
Use a Recursive CTE to traverse the employee hierarchy.
WITH RECURSIVE employee_hierarchy AS (
    -- Seed part: Starting from the CEO
    SELECT
        id,
        name,
        manager_id,
        1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive part: Finding 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;
The execution result is as follows.
+----+------------+------------+-------+

| id | name       | manager_id | level |

+----+------------+------------+-------+

|  1 | CEO        |       NULL |     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 |

+----+------------+------------+-------+
6 rows in set (0.05 sec)

Use Limits

When using CTEs, note the following limitations.
The recursive part of a Recursive CTE must reference the CTE itself and can do so only once. It cannot be referenced within a subquery.
The recursive part of a Recursive CTE does not support ORDER BY, aggregate functions, window functions, DISTINCT, or LIMIT operations.
The recursion depth of a Recursive CTE is limited by the system variable cte_max_recursion_depth, which has a default value of 1000. If the limit is exceeded, the query returns an error. You can increase the upper limit by adjusting this variable.
When a CTE is referenced multiple times in an outer query, the engine automatically chooses to execute it using the Materialization method. When it is referenced only once, the engine defaults to expanding it using the Merge method.
Anterior: SELECT Statement DescriptionPróximo: Window Function Use Instructions

Ajuda e Suporte

Esta página foi útil?

comentários