How Can I Combine Multiple CTEs, Including Recursive Ones, in a Single SQL Query?

Multiple CTEs in a Single SQL Query

In SQL, it is possible to combine multiple CTEs (Common Table Expressions) within a single query. To achieve this, you need to use the keyword WITH once at the beginning of the query.

Example:

Suppose you want to combine one recursive CTE and two non-recursive CTEs. You can do so using the following syntax:

WITH RECURSIVE
  cte1 AS (...),
  cte2 AS (
    SELECT ...
    UNION ALL
    SELECT ...
  ),
  cte3 AS (...)
SELECT ... FROM cte3 WHERE ...

In this example:

• cte1 is a recursive CTE.
• cte2 is a non-recursive CTE.
• cte3 is a non-recursive CTE.

Note: The order of the CTEs is not significant when the RECURSIVE keyword is used.

Recursive CTE:

A recursive CTE can reference itself in its definition. This allows you to build hierarchical or tree-like structures. In the example above, cte2 is a recursive CTE because it references itself in the UNION ALL clause.

Non-Recursive CTEs:

Non-recursive CTEs simply define temporary tables that can be referenced later in the query. In the example above, cte1 and cte3 are non-recursive CTEs.

The above is the detailed content of How Can I Combine Multiple CTEs, Including Recursive Ones, in a Single SQL Query?. For more information, please follow other related articles on the PHP Chinese website!