| --- | |
| { | |
| "title": "Common Table Expressions (CTE)", | |
| "language": "en", | |
| "description": "Apache Doris CTE (Common Table Expression) guide: define temporary result sets with the WITH clause, with support for nested and recursive CTEs, suitable for hierarchical traversal, graph traversal, and similar scenarios.", | |
| "keywords": [ | |
| "Doris CTE", | |
| "common table expression", | |
| "WITH clause", | |
| "recursive CTE", | |
| "RECURSIVE", | |
| "nested CTE", | |
| "hierarchical query", | |
| "tree-structured query", | |
| "graph traversal", | |
| "cte_max_recursion_depth" | |
| ] | |
| } | |
| --- | |
| <!-- Knowledge type: Capability definition + Syntax reference --> | |
| <!-- Applicable scenarios: Writing complex queries / Hierarchical and graph-structure traversal --> | |
| A Common Table Expression (CTE) is the capability in Apache Doris to define a temporary result set within a `SELECT` statement. After being declared once with a `WITH` clause, the CTE can be referenced multiple times in the same SQL. CTEs are commonly used to simplify complex queries, eliminate duplicated subqueries, and express self-referential logic such as hierarchical and graph traversal. | |
| ## Applicable Scenarios | |
| <!-- Knowledge type: Applicable scenarios --> | |
| CTEs typically make SQL clearer and easier to maintain in the following situations: | |
| - **The same subquery is referenced multiple times**: Name the subquery as a CTE to avoid writing it repeatedly in the main query. | |
| - **Deeply nested subqueries are hard to read**: Break the logic into multiple CTEs and name each step to improve readability. | |
| - **A computation builds on the result of a previous step**: With nested CTEs, a later CTE can directly reference the result of an earlier CTE. | |
| - **Hierarchical or tree-structured traversal**: For example, organizational hierarchies, category catalogs, or nested comment threads. Use a recursive CTE to expand all levels in one query. | |
| - **Graph reachability traversal**: For example, starting from a given node and following edges to find all reachable nodes. | |
| ## Basic Usage | |
| ### Syntax Overview | |
| Use the `WITH` clause to define one or more CTEs, separated by commas. Each CTE has a name and a subquery: | |
| ```sql | |
| WITH | |
| cte_name1 AS (subquery1), | |
| cte_name2 AS (subquery2) | |
| SELECT ... FROM cte_name1 JOIN cte_name2 ON ...; | |
| ``` | |
| In a statement that contains a `WITH` clause, you can reference each CTE name to access its corresponding temporary result set. | |
| ### Simple CTE Example | |
| The following example defines `cte1` and `cte2` in the `WITH` clause and references both in the outer `SELECT`: | |
| ```sql | |
| WITH | |
| cte1 AS (SELECT a, b FROM table1), | |
| cte2 AS (SELECT c, d FROM table2) | |
| SELECT b, d FROM cte1 JOIN cte2 | |
| WHERE cte1.a = cte2.c; | |
| ``` | |
| ### Nested CTE | |
| A CTE name can be referenced inside other CTEs, so you can define new CTEs based on previously defined ones: | |
| ```sql | |
| WITH | |
| cte1 AS (SELECT a, b FROM table1), | |
| cte2 AS (SELECT c, d FROM cte1) | |
| SELECT b, d FROM cte1 JOIN cte2 | |
| WHERE cte1.a = cte2.c; | |
| ``` | |
| ## Recursive CTE | |
| <!-- Knowledge type: Capability definition + Syntax reference --> | |
| <!-- Applicable scenarios: Tree/hierarchical traversal, graph traversal, hierarchical aggregation --> | |
| A recursive CTE (a CTE with the `RECURSIVE` keyword) expresses self-referential queries within a single SQL statement. It is commonly used for tree and hierarchy traversal, graph traversal, and hierarchical aggregation. | |
| ### Syntax | |
| ```sql | |
| WITH [RECURSIVE] cte_name [(col1, col2, ...)] AS ( | |
| <anchor_query> -- Non-recursive part (executed once) | |
| UNION [ALL] | |
| <recursive_query> -- Recursive part that can reference cte_name | |
| ) | |
| SELECT ... FROM cte_name; | |
| ``` | |
| Key points: | |
| - The `RECURSIVE` keyword allows the CTE definition to reference itself. | |
| - The anchor and recursive members must produce exactly the same number of columns and the same column types. | |
| - The `recursive_query` can reference `cte_name`, typically through a `JOIN`. | |
| ### Composition | |
| A recursive CTE consists of two parts, usually connected by `UNION` or `UNION ALL`: | |
| | Component | Description | | |
| |---|---| | |
| | Anchor query | The non-recursive part, executed once to produce the initial set of rows (the seed). | | |
| | Recursive query | Can reference the CTE itself and produce new rows based on the rows generated in the previous round. | | |
| The recursion continues until no new rows are produced or a system limit is reached. | |
| ### Execution Semantics (Iterative Model) | |
| <!-- Knowledge type: Execution principle --> | |
| A typical execution flow for a recursive CTE is as follows: | |
| 1. Execute `anchor_query`, write the result into the output set (Output), and use it as the working set (WorkSet) for the first round. | |
| 2. While WorkSet is not empty, repeat: | |
| - Use WorkSet as input to `recursive_query`, execute `recursive_query`, and obtain `newRows`. | |
| - With `UNION ALL`: append `newRows` directly to Output, and use `newRows` as the WorkSet for the next round. | |
| - With `UNION` (deduplicated): compute the difference between `newRows` and the existing Output (deduplication), and add only previously unseen rows to Output and to the WorkSet for the next round. | |
| 3. Repeat step 2 until `newRows` is empty or the system-defined recursion depth limit is reached. | |
| The session variable `cte_max_recursion_depth` controls the maximum recursion depth. The default value is 100, and exceeding it raises an error. | |
| ### UNION vs UNION ALL | |
| <!-- Knowledge type: Selection comparison --> | |
| | Form | Semantics | Performance | Applicable scenarios | | |
| |---|---|---|---| | |
| | `UNION ALL` | Keeps duplicate rows | Low overhead (no deduplication) | When duplicates are allowed, or when duplicates are handled by the application layer | | |
| | `UNION` | Implicit deduplication | Adds sort or hash deduplication overhead in each round or globally, with significant cost on large data | When deduplication must be performed inside the database | | |
| Recommendation: If the semantics allow it and duplicates can be handled at the application layer, prefer `UNION ALL`. | |
| ### Examples | |
| #### Simple Hierarchy Traversal | |
| Starting from the root node, recursively traverse the entire tree: | |
| ```sql | |
| CREATE TABLE tree | |
| ( | |
| id int, | |
| parent_id int, | |
| data varchar(100) | |
| ) DUPLICATE KEY (id) | |
| DISTRIBUTED BY HASH(id) BUCKETS 1 PROPERTIES ('replication_num' = '1'); | |
| INSERT INTO tree VALUES (0, NULL, 'ROOT'), (1, 0, 'Child_1'), (2, 0, 'Child_2'), (3, 1, 'Child_1_1'); | |
| WITH RECURSIVE search_tree AS ( | |
| SELECT id, parent_id, data | |
| FROM tree t | |
| WHERE t.id = 0 | |
| UNION ALL | |
| SELECT t.id, t.parent_id, t.data | |
| FROM tree t, search_tree st | |
| WHERE t.parent_id = st.id | |
| ) | |
| SELECT * FROM search_tree ORDER BY id; | |
| ``` | |
| #### Graph Traversal | |
| Following the direction of edges, traverse all reachable paths in a graph: | |
| ```sql | |
| CREATE TABLE graph | |
| ( | |
| c_from int, | |
| c_to int, | |
| label varchar(100) | |
| ) DUPLICATE KEY (c_from) DISTRIBUTED BY HASH(c_from) BUCKETS 1 PROPERTIES ('replication_num' = '1'); | |
| INSERT INTO graph VALUES (1, 2, '1 -> 2'), (1, 3, '1 -> 3'), (2, 3, '2 -> 3'), (1, 4, '1 -> 4'), (4, 5, '4 -> 5'); | |
| WITH RECURSIVE search_graph AS ( | |
| SELECT c_from, c_to, label FROM graph g | |
| UNION ALL | |
| SELECT g.c_from, g.c_to, g.label | |
| FROM graph g, search_graph sg | |
| WHERE g.c_from = sg.c_to | |
| ) | |
| SELECT DISTINCT * FROM search_graph ORDER BY c_from, c_to; | |
| ``` | |
| Note: The example above uses `SELECT DISTINCT` at the end to deduplicate. Using `UNION` for deduplication inside the recursion would deduplicate in every round, which is more expensive. | |
| ## Recursive CTE Limitations | |
| <!-- Knowledge type: Restrictions and constraints --> | |
| The following constraints apply when using a recursive CTE: | |
| - The top-level operator inside the CTE must be `UNION` or `UNION ALL`. | |
| - The non-recursive subquery cannot reference the recursive CTE itself. | |
| - The recursive subquery can reference the recursive CTE only once. | |
| - If the recursive subquery contains an inner subquery, that inner subquery cannot reference the recursive CTE. | |
| - The output column types of the recursive CTE are determined by the non-recursive side. If the recursive side and the non-recursive side have inconsistent types, an error is raised, and you must add an explicit `CAST` to align the data types on both sides. | |
| - The session variable `cte_max_recursion_depth` limits the maximum number of recursions to prevent infinite loops. The default value is 100. | |
| ## Common Errors and Troubleshooting | |
| <!-- Knowledge type: Troubleshooting --> | |
| <!-- Applicable scenarios: Debugging when writing recursive CTEs --> | |
| | Symptom | Possible cause | Resolution | | |
| |---|---|---| | |
| | The number or types of columns in the anchor and recursive members do not match | The two `SELECT` lists differ in column count or column types | Make sure both sides have the same column count, order, and types. Use `CAST` or explicit column names if needed. | | |
| | The anchor references itself (illegal) | The anchor is not allowed to reference the CTE itself | Reference the CTE only in the recursive member. Check the syntax or parse tree. | | |
| | Infinite recursion / maximum recursion depth exceeded | The recursion has no termination condition, or the termination condition is incorrect | Add a `WHERE` filter, or adjust the system-wide maximum recursion depth. If the logic really is infinite, fix the query logic. | | |
| ## References | |
| - [MySQL Recursive CTE Manual](https://dev.mysql.com/doc/refman/8.4/en/with.html#common-table-expressions-recursive): Standard definition and examples of recursive CTEs. |