| --- |
| { |
| "title": "Common Table Expressions", |
| "language": "en" |
| } |
| --- |
| |
| ## Description |
| |
| Common Table Expression (CTE) define a temporary result set that can be referenced multiple times within the scope of an SQL statement. CTE are primarily used in SELECT statements. |
| |
| To specify a CTE, use the `WITH` clause with one or more comma-separated clauses. Each clause provides a subquery that generates a result set and associates a name with the subquery. |
| |
| Within the statement that contains the `WITH` clause, you can reference each CTE name to access the corresponding CTE result set. CTE names can be referenced in other CTE, allowing you to define CTE based on other CTE. |
| |
| Doris **DO NOT** support recursive CTE. |
| |
| ## Example |
| |
| The following example defines CTE named cte1 and cte2 within the WITH clause and refers to them in the top-level SELECT below the WITH clause: |
| |
| ```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; |
| ``` |
