Can Multiple CTEs Be Combined in a Single SQL Query?

Querying with Multiple CTEs

Conventional practice often requires combining multiple Common Table Expressions (CTEs) to achieve complex data retrieval tasks. However, doubts linger regarding the feasibility of combining multiple CTEs in a single SQL query.

Supporting Multiple CTEs

Indeed, it is entirely feasible to combine multiple CTEs within a single SQL query. The syntax requires the use of the WITH keyword only once, positioned at the beginning of the query. Even when dealing with a mix of recursive and non-recursive CTEs, the RECURSIVE keyword should still be included only once at the top.

For instance:

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

It's noteworthy that the ordering of CTE clauses becomes irrelevant when the RECURSIVE keyword is present. As per the SQL manual, it allows CTE queries to make references to other CTEs regardless of their order within the WITH clause.

Furthermore, non-recursive CTEs that are not referenced in the final SELECT statement and do not have any collateral effects will not be executed.

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