Can Multiple CTEs, Including Recursive Ones, Be Combined in a Single SQL Query?

Connecting Multiple CTEs in a Single SQL Query

Is it feasible to combine multiple Common Table Expressions (CTEs) within a single query? The goal is to achieve a result similar to:

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

This query incorporates one recursive CTE and two non-recursive CTEs.

Solution:

To connect multiple CTEs in a single query:

• Utilize the WITH keyword once at the beginning.
• If any CTEs are recursive, add the RECURSIVE keyword once at the start, even if not all CTEs are recursive.

Updated Query:

WITH RECURSIVE
  cte1 AS (...)         -- can still be non-recursive
, cte2 AS (SELECT ...
           UNION ALL
           SELECT ...)  -- recursive term
, cte3 AS (...)
SELECT ... FROM cte3 WHERE ...

Key Points:

• The RECURSIVE keyword permits a CTE to self-reference.
• With RECURSIVE, the order of WITH clauses becomes inconsequential.
• CTEs that are not referenced in the outer SELECT might go unexecuted.

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