Is it possible to combine multiple CTEs in single query?
I am looking for way to get result like this:
WITH cte1 AS (
...
),
WITH RECURSIVE cte2 AS (
...
),
WITH cte3 AS (
...
)
SELECT ... FROM cte3 WHERE ...
As you can see, I have one recursive CTE and two non recursive.
Use the key word WITH
once at the top. If any of your Common Table Expressions (CTE) are recursive (rCTE) you have to add the keyword RECURSIVE
at the top once also, even if not all CTEs are recursive:
WITH RECURSIVE
cte1 AS (...) -- can still be non-recursive
, cte2 AS (SELECT ...
UNION ALL
SELECT ...) -- recursive term
, cte3 AS (...)
SELECT ... FROM cte3 WHERE ...
If
RECURSIVE
is specified, it allows aSELECT
subquery to reference itself by name.
Bold emphasis mine. And, even more insightful:
Another effect of
RECURSIVE
is thatWITH
queries need not be ordered: a query can reference another one that is later in the list. (However, circular references, or mutual recursion, are not implemented.) WithoutRECURSIVE
,WITH
queries can only reference siblingWITH
queries that are earlier in theWITH
list.
Bold emphasis mine again. Meaning that the order of WITH
clauses is meaningless when the RECURSIVE
key word has been used.
BTW, since cte1
and cte2
in the example are not referenced in the outer SELECT
and are plain SELECT
commands themselves (no collateral effects), they are never executed (unless referenced in cte3
).